Excel - MAXIFS & MINIFS Tutorial

In this video, you will learn about the intelligent search feature in Microsoft 365. The video covers how to find documents, images, videos, people, and conversations using the search bar located at the top of your Microsoft Office apps.
It also demonstrates how to access your most recently used documents and run an online search in the Microsoft Bing search engine.
This powerful tool helps you search your documents stored across your online storage services and gain time and efficiency in your searches.
This will help you improve your productivity and find everything you need in your Microsoft 365 account.

1:06
3167 views

Available in Microsoft365

Objectifs :

Understand how to use the MAXIFS and MINIFS functions in Excel to find maximum and minimum values based on specific criteria.

Chapitres :

Introduction to MAXIFS and MINIFS Functions
The MAXIFS and MINIFS functions in Excel are powerful tools that allow users to find maximum and minimum values from a range of data based on specified conditions. This is particularly useful in scenarios where data needs to be filtered according to certain criteria, such as qualifications and divisions in a workplace.
Using the MAXIFS Function
The MAXIFS function returns the highest value from a specified range of cells that meet certain criteria. In this context, we want to determine the highest years of service based on two criteria: qualification and division. **Key Components of the MAXIFS Function:** - **Range:** This is the range of cells that contains the years of experience. - **Criteria 1:** This is the qualification, which is defined in the function. - **Criteria 2:** This is the division, also defined in the function. **Example:** To find the maximum years of service for a specific qualification and division, the formula would look like this: `=MAXIFS(years_of_experience_range, qualification_range, qualification_criteria, division_range, division_criteria)`
Using the MINIFS Function
Similar to MAXIFS, the MINIFS function is used to find the minimum value from a range of cells that meet specified criteria. This function can be applied in the same way as MAXIFS to find the lowest years of service based on the same criteria of qualification and division. **Key Components of the MINIFS Function:** - **Range:** This is the range of cells that contains the years of experience. - **Criteria 1:** This is the qualification, defined in the function. - **Criteria 2:** This is the division, also defined in the function. **Example:** To find the minimum years of service for a specific qualification and division, the formula would look like this: `=MINIFS(years_of_experience_range, qualification_range, qualification_criteria, division_range, division_criteria)`
Conclusion
In summary, the MAXIFS and MINIFS functions are essential for analyzing data in Excel, allowing users to extract meaningful insights based on specific criteria. By understanding how to implement these functions, users can efficiently determine the highest and lowest years of service according to qualifications and divisions, enhancing their data analysis capabilities.

FAQ :

What is the MAXIFS function used for in Excel?

The MAXIFS function is used to find the maximum value in a range of cells that meet one or more specified criteria.

How does the MINIFS function differ from MAXIFS?

The MINIFS function returns the minimum value from a range of cells based on specified criteria, while MAXIFS returns the maximum value.

Can I use multiple criteria with MAXIFS and MINIFS?

Yes, both MAXIFS and MINIFS allow you to specify multiple criteria to refine your search for maximum or minimum values.

What types of data can I use with MAXIFS and MINIFS?

You can use numerical data, such as years of service or sales figures, with both MAXIFS and MINIFS functions.

What are some common applications of the MAXIFS and MINIFS functions?

These functions are commonly used in data analysis to determine the highest or lowest values based on specific conditions, such as employee performance or sales data.

Quelques cas d'usages :

Evaluating Employee Performance

A human resources manager can use the MAXIFS function to find the employee with the highest years of service in a specific division, helping to identify potential candidates for promotion.

Sales Analysis

A sales manager can apply the MINIFS function to determine the lowest sales figures in a particular region, allowing for targeted strategies to improve performance.

Project Management

In project management, the MAXIFS function can be used to identify the team member with the most experience in a specific qualification area, ensuring the right expertise is applied to critical tasks.

Budgeting and Forecasting

Financial analysts can utilize the MINIFS function to find the lowest expenses in a department over a set period, aiding in budget adjustments and forecasting.

Glossaire :

MAXIFS

A function in Excel that returns the maximum value from a range of cells based on one or more specified criteria.

MINIFS

A function in Excel that returns the minimum value from a range of cells based on one or more specified criteria.

criteria

Conditions or rules that define which cells to include in the calculation of the MAXIFS or MINIFS functions.

range

The group of cells that the MAXIFS or MINIFS function evaluates to find the maximum or minimum value.

years of service

The total number of years an employee has worked in a particular role or organization.

qualification

The education, skills, or experience that make someone suitable for a particular job or task.

division

A distinct part of an organization, often responsible for a specific area of work or function.

00:00:13

MINIFS returns to the minimum value.

00:00:16

Here we would like to know the highest years of service according to qualification and division.

00:00:23

With the MAXIFS function…

00:00:30

The range being the years of experience.

00:00:36

The first criterion: Qualification,

00:00:43

Defined here.

00:00:47

Second criterion: Division,

00:00:53

Defined here.

00:01:00

Same procedure for the MINIFS function which returns the minimum value.

00:00:05

A função MAXICS devolve o valor máximo entre as células especificadas por um determinado conjunto de condições ou critérios.

00:00:13

MINIFS regressa ao valor mínimo.

00:00:16

Aqui gostaríamos de conhecer os anos mais elevados de serviço de acordo com a qualificação e divisão.

00:00:23

Com a função MAXICS...

00:00:30

A gama sendo os anos de experiência.

00:00:36

O primeiro critério: Qualificação,

00:00:43

Definido aqui.

00:00:47

Segundo critério: Divisão,

00:00:53

Definido aqui.

00:01:00

O mesmo procedimento para a função MINIFS que retorna o valor mínimo.

00:00:05

La funzione MAXIFS restituisce il valore massimo tra le celle specificate da un determinato insieme di condizioni o criteri.

00:00:13

MINIFS ritorna al valore minimo.

00:00:16

Qui vorremmo conoscere gli anni di servizio più elevati in base alla qualifica e alla divisione.

00:00:23

Con la funzione MAXIFS...

00:00:30

La gamma è costituita da anni di esperienza.

00:00:36

Il primo criterio: qualificazione,

00:00:43

Definito qui.

00:00:47

Secondo criterio: divisione,

00:00:53

Definito qui.

00:01:00

Stessa procedura per la funzione MINIFS che restituisce il valore minimo.

00:00:05

Функция MAXIFS возвращает максимальное значение среди ячеек, заданное заданным набором условий или критериев.

00:00:13

MINIFS возвращается к минимальному значению.

00:00:16

Здесь мы хотели бы узнать наивысший стаж работы в соответствии с квалификацией и подразделением.

00:00:23

С функцией MAXIFS...

00:00:30

Ассортимент — это многолетний опыт.

00:00:36

Первый критерий: квалификация,

00:00:43

Определено здесь.

00:00:47

Второй критерий: разделение,

00:00:53

Определено здесь.

00:01:00

Та же процедура для функции MINIFS, которая возвращает минимальное значение.

00:00:05

La función MAXIFS devuelve el valor máximo entre las celdas especificadas por un conjunto determinado de condiciones o criterios.

00:00:13

MINIFS vuelve al valor mínimo.

00:00:16

Aquí nos gustaría conocer los años de servicio más altos según la calificación y la división.

00:00:23

Con la función MAXIFS...

00:00:30

La gama son los años de experiencia.

00:00:36

El primer criterio: calificación,

00:00:43

Definido aquí.

00:00:47

Segundo criterio: división,

00:00:53

Definido aquí.

00:01:00

El mismo procedimiento para la función MINIFS que devuelve el valor mínimo.

00:00:05

De MAXIFS-functie retourneert de maximale waarde tussen cellen die is gespecificeerd op basis van een bepaalde set voorwaarden of criteria.

00:00:13

MINIFS keert terug naar de minimumwaarde.

00:00:16

Hier willen we graag weten wat de hoogste dienstjaren zijn volgens kwalificatie en indeling.

00:00:23

Met de MAXIFS-functie...

00:00:30

Het assortiment is de jarenlange ervaring.

00:00:36

Het eerste criterium: kwalificatie,

00:00:43

Hier gedefinieerd.

00:00:47

Tweede criterium: indeling,

00:00:53

Hier gedefinieerd.

00:01:00

Dezelfde procedure voor de MINIFS-functie die de minimumwaarde retourneert.

00:00:05

Funkcja MAXIFS zwraca maksymalną wartość między komórkami określoną przez dany zestaw warunków lub kryteriów.

00:00:13

MINIFS powraca do wartości minimalnej.

00:00:16

Tutaj chcielibyśmy poznać najwyższe lata służby według kwalifikacji i podziału.

00:00:23

Dzięki funkcji MAXIFS...

00:00:30

Zakres to lata doświadczenia.

00:00:36

Pierwsze kryterium: Kwalifikacja,

00:00:43

Zdefiniowane tutaj.

00:00:47

Drugie kryterium: Podział,

00:00:53

Zdefiniowane tutaj.

00:01:00

Ta sama procedura dla funkcji MINIFS, która zwraca wartość minimalną.

00:00:05

A MAXIFS függvény egy adott feltétel vagy kritériumkészlet által megadott cellák közötti maximális értéket adja vissza.

00:00:13

A MINIFS visszatér a minimális értékre.

00:00:16

Itt szeretnénk megismerni a legmagasabb szolgálati éveket a képesítés és a felosztás szerint.

00:00:23

A MAXIFS funkcióval...

00:00:30

A tartomány az évek tapasztalata.

00:00:36

Az első kritérium: Minősítés,

00:00:43

Itt definiálva.

00:00:47

Második kritérium: osztás,

00:00:53

Itt definiálva.

00:01:00

Ugyanez az eljárás a MINIFS függvényre, amely a minimális értéket adja vissza.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2187 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES