Formula for Finding Maximum or Minimum Values in Excel

Two of the most fundamental indicators in any statistical data set are the maximum and minimum values, commonly referred to as maxima and minima on a graph. Maximum and minimum values help determine the range of all original values.



How to Sample Maximums and Minimums in Excel

Example: Below is a representation of the average air temperature for specific months. In this example, we want to find out in which months of the year the temperature was the highest and lowest. The first formula searches for the highest temperature:

sampling maximums and minimums.

Excel provides two functions for finding the highest and lowest values in a data set: MAX and MIN. Both functions can have up to 255 arguments. The source data is in the cell range B2:B13, which serves as the argument for both the MAX and MIN functions. The MAX function returns a value of 19 degrees—this is the highest temperature in the specified data set, while the MIN function returns the lowest temperature: -3.

The next formula returns the name of the month corresponding to the temperature found using the first formula:

=INDEX(A2:A13,MATCH(D2,B2:B13,0),1)
INDEX and MATCH.

Download the Example of How to Search by Minimum Negative Value in Excel download file

To determine the name of the month with the maximum or minimum temperature, you first need to use the INDEX function. The cell range A2:A13 is specified as the first argument for this function, containing the list of months. In the second argument of the INDEX function, the MATCH function is specified, which returns the position of the sought value in the original data list. If the sought value is 19, the MATCH function will return the number 7, as the number 19 is in the seventh position of the original data list. The INDEX function uses this value to return the seventh row from the "Month" column, which is "July." The same principle applies to the MIN function, which ultimately returns the text value "January," representing the month with the lowest temperature of the year.

Thanks to the MAX and MIN functions, text values in the original data range are ignored. However, if there are errors in the data, these functions will also return errors. If all values are text, both functions will return 0 in their results.