Find the maximum, minimum value (MAX, MIN function)

To find the maximum (max.), minimum (min.) value from a specific data in Excel, we use the following function.

=MAX(number 1,[number 2],…)

[number 1] = Select cell range or the first number in finding the max. value.
[number 2] = Select cell range or the second number in finding the max. value.

Definition of function A function to extract the max. value in a selected [number] (numeric value).
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MIN(number 1,[number 2],…)

[number 1] = Select cell range or the first number in finding the min. value.
[number 2] = Select cell range or the second number in finding the min. value.

Definition of function A function to extract the min. value in a selected [number] (numeric value).
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) MAX・MIN functions

The following list shows the results of  the end of year exams in a school. The highest and the lowest total score is shown in cell G9 and G10.

① Extracting the highest total score

[number 1] : Select cell range [G4:G7] in finding the highest total score.
=MAX(G4:G7)

② Extracting the lowest total score

[number 1] : Select cell range [G4:G7] in finding the lowest total score.
=MIN(G4:G7)

 

Find the max., min. value with a condition within a specific region

To find the value with a condition, for example, of “the region where ‘apples’ are most shipped in a given list of shipments of ‘apples’ and ‘oranges’ in a country…” we use the following function called a database function.

=DMAX(database, field, criteria)

[database] = Select data range of each column including headings.
[field] = Select column subject for calculation. Either input the heading name of the column using “, or the column number counting from the left.
[criteria] = Select cell range where the condition is input.

Definition of function A function to extract the max. value in a selected [field] with a condition.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DMIN(database, field, criteria)

[database] = Select data range of each column including headings.
[field] = Select column subject for calculation. Either input the heading name of the column using “, or the column number counting from the left.
[criteria] = Select cell range where the condition is input.

Definition of function A function to extract the min. value in a selected [field] with a condition.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

What is a database?

Data organized and aggregated in a neat form is called a database in Excel.
In a database, the “row” is called record and the “column” is called field, used regularly in functions.

 

Example of use) DMAX・DMIN functions

The column ‘Retake’ (of exams) has been added to the previous list. A new condition is also added marking ‘○’ for every total score below 300. Results show that everyone has to retake their exams except for one student, but we decided to see the highest and the lowest total score among those who have to retake their exams.

① Find the highest score among the students who have to retake their exam

(1) [Database] : Select cell range [G3:H7] which includes the header ‘Total’ and ‘Retake’.
(2) [Field] : Input [1] as we are trying to find the highest score of the first row (field) in the selected [Database].
(3) [Criteria] : Select cell range [H9:H10] in which the condition is input. →※ By including the header (H9), it specifies when the condition (H10) occurs in the header (H9).
=DMAX(G3:H7,1,H9:H10)

② Find the lowest score among the students who have to retake their exam

(1) [Database] : Select cell range [G3:H7] which includes the header ‘Total’ and ‘Retake’.
(2) [Field] : Input [1] as we are trying to find the lowest score of the first row (field) in the selected [Database].
(3) [Criteria] : Select cell range [H9:H10] in which the condition is input. →※ By including the header (H9), it specifies when the condition (H10) occurs in the header (H9).
=DMIN(G3:H7,1,H9:H10)

 

Find and color the cell of the max., min. value

To find the maximum and minimum value and add color on the cell, we use “conditional formatting”.

 

Example of use) Color the cell of the max., min. value with conditional formatting

We will use the previous list to show how to automatically color in the cell of the highest and lowest scores.

(1) Follow these steps to use conditional formatting.

① Select cell range subjected to changing color.
② Click [Conditional Formatting (※Click the word)] from the Ribbon and then click [New Rule…]

 

(2) Select the following when the dialog box [New Formatting Rule] appears.

① From the section [Select a Rule Type], select [Use a formula to determine which cells to format].
② In the [Format values where…], input the formula that states ‘(when) G4 is the largest number within G4:G7’.

=$G4=MAX($G$4:$G$7)

③ Click [Format] and select color from ‘Fill’.
④ Click [OK] to complete.

 

(3) The results of the Conditional Formatting

The cell with the highest total score, G5 has been colored in.

 

(4) Using Conditional Formatting with the lowest score

We can do the same with the ‘lowest score’ as well.
In this case, change the formula using the MIN function instead of the MAX function.

 

Find the second and third largest/smallest value

To find the ○○th largest/smallest number in a data, we use the following function.

=LARGE(array, k)

[array] = Select data range, or input the numbers separating them with a “, (comma)”
[k] = The spot of number counting from the largest.

Definition of function A function to find the spot [k] of a number within a specified [array] in a data.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=SMALL(array, k)

[array] = Select data range, or input the numbers separating them with a “, (comma)”
[k] = The spot of number counting from the smallest.

Definition of function A function to find the spot [k] of a number within a specified [array] in a data.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) LARGE・SMALL functions

The following list shows the results of  the end of year exams in a school. We found the highest and lowest total scores and changed their cell color but have decided to find the “2nd” highest total score and the “2nd” lowest total score.

① Find the 2nd highest total score

(1) [array] : Select cell range [G4:G11] subject to comparison.
(2)[k] : Input [2], as it is the 2nd number we are looking for.
=LARGE(G4:G11,2)

② Find the 2nd lowest total score

(1)[array] : Select cell range [G4:G11] subject to comparison.
(2)[k] : Input [2], as it is the 2nd number we are looking for.
=SMALL(G4:G11,2)

 

Find the min. value excluding ‘0’ (zero)

To find the min. value excluding ‘0’, we combine the COUNTIF function.
→For more information on the COUNTIF function, click here 

 

Example of use) Find the min. value excluding ‘0’

In the list below, as Daniel Scott has been appointed to retake the biology exam, we input ‘0’ as his temporary score and calculate his total for the other 4 subjects. We will find the lowest score in Biology excluding ‘0’.

① Find the lowest score in Biology excluding ‘0’.

(1) In the COUNTIF function, we see how many ‘0’ there are the Biology cell range [E4:E11].

COUNTIF(E4:E11,0)

The results are the total number of ‘0’ . In other words, we know the rank of number ‘0’. For example, if “there are two of them, the 1st and 2nd smallest would be ‘0’”. We would like to find the next rank after ‘0’ (+1), therefore the following formula can be applied.

COUNTIF(E4:E11,0)+1

(2) The SMALL function then helps to find the number of the rank calculated in (1).

=SMALL(E4:E11, COUNTIF(E4:E11,0)+1 )

The above is the complete equation to find the lowest Biology score excluding ‘0’.