Identifying Maximums and Minimums With Excel Functions

admin | Business | July 28th, 2008

Need to find the largest or the smallest value in an Excel worksheet? Microsoft Excel supplies several easy-to-use functions for just such a purpose says bestselling computer book author Stephen L. Nelson.

If you select a range of cells, you can have Excel find the largest, or maximum, value in the range. Similarly, you can have Excel find the smallest, or minimum, value in the range. Excel can identify such maximum or minimum values using its MAX and MIN functions.

Often, these functions are extremely useful (especially when you’re analyzing large data sets). Accordingly, you’ll probably find it profitable to see how the four maximum and minimum functions work in action.

MAX

The MAX function returns the largest value in a set of data. It ignores blank cells and cells containing text or logical values such as TRUE and FALSE. The MAX function uses the following syntax:

=MAX (data set range)

MAXA

The MAXA function returns the largest value in a set of data, but it includes logical values and text. It counts TRUE as 1 and FALSE and all other text as 0. The MAXA function uses the following syntax:

=MAXA (data set range)

MIN

The MIN function returns the smallest value in a set of data. It ignores blank cells and cells containing text or logical values such as TRUE and FALSE. The MIN function uses the following syntax:

=MIN (data set range)

MINA

The MINA function returns the smallest value in a set of data, but it includes logical values and text. It counts TRUE as 1 and FALSE and all other text as 0. The MINA function uses the following syntax:

=MINA (data set range)

A Bit More Info on the Data Set Range Input

Excel’s maximum and minimum accept worksheet ranges as inputs. That’s what the “data set range” input shown in the above function descriptions refers to.

These worksheet ranges, or ranges, are simply any rectangular area of the worksheet, such as a two-cell by two-cell square, a five-cell by nine-cell rectangle, or even an entire worksheet.

You can also use opposite corner cell references and a colon to define ranges. For example, the range of cells from C1 and up to and including C5 is written as C1:C5. And the range of cells from C1 to D2 is written as C1:D2.

Finally, note that you can include several ranges as arguments. For example, you could write a formula to calculate a minimum that looks like this:

=MIN(A1:B30,F44 ,G4:G50)

About the author

Seattle accountant and bestselling computer book author Stephen L. Nelson wrote the MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself kits that businesses and investors can use for setting up a S Corporation, Corporation or a Limited Liability Company.