# Find Max & Min Values In Excel 2010

Through simple Excel 2010 functions you can easily find out the maximum and minimum value in the spreadsheet. The MAX and MIN functions immediately returns the maximum and minimum values for the specified range. But sometimes there is a need of including logical values along with numeric values to find out max and min value, then MAXA and MINA functions are very helpful. The syntax of theses functions are same and takes only one argument which either be number or location of data range. In this post we will using a simple example to demonstrate the core usage of these functions.

Launch Excel 2010 spreadsheet on which you want to find out Maximum and Minimum values. For instance we have included a student record spreadsheet containing fields; Names, Course, and Marks, as shown in screenshot below.

First we will be creating Max Marks & Min Marks labels beneath the table.

Now we will be finding out Maximum value out of Marks field, for this we will be writing MAX function in the adjacent cell of Max Marks.

The syntax of  Max Marks is;

=Max(number1, number2,…)

We will be writing it as;

=MAX(C2:C11)

The argument C2:C11 is the location of cell where data is residing. It will yield the maximum value from the field Marks, as shown in the screenshot below.

For finding out minimum values from the table we will be writing MIN function as;

=MIN(C2:C11)

It will yield the minimum value from the Marks field.

The MAX and MIN function only evaluate if there is only numeric values present in the datasheet, if you want to include logical values and get them evaluated, then you will need to use MAXA and MINA functions.

Now we will be applying MAXA and MINA functions in cells adjacent to respective labels. The syntax of these functions are absolutely same as MAX and MIN function, the primary difference is that it is able to consider logical values as number (TRUE as 1 and FALSE as 0).

It looked up for the maximum value in location B2:B11 and showed 1 as maximum, it also considers logical value ‘TRUE’ as 1. For minimum value it showed 0 as minimum value as it consider FALSE as 0.

You can also check out previously reviewed Excel function; Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, and LEN.

1. Mike says:

Is it possible to use MIN with a row of data that also has < symbol and give the < symbol the priority to pull the min number

2. nick says:

I have a question. Let’s say I have data like:

column 1          column 2
a                      1-1-2011
a                      3-1-2011
a                      4-1-2011
a                      1-1-2012
a                      2-1-2012
b                      1-1-2011
c                      2-1-2011
c                      5-1-2011
c                      6-1-2012
d                      2-1-2011
d                      4-1-2012

for all values in column 1 that is “a” what is the min in column 2.

so for this example, 1-1-2011 should return in column 3

if I am looking for the max, I would get 2-1-2012

please let me know if this question makes sense or if you need more explaining… I probably didn’t explain it well…

thanks,

3. Aaron says:

What formula would I use if I wanted to associate a NAME (from the student list) with the value discovered via the MIN or MAX formula? Rather than looking through the list and trying to figure it out I want Excel to look at those scores and tell me who got those two scores. Thanks!

• Peter Welch says:

Aaron,

Did you get a response to your query? I have the same need – to also collect the data in an adjacent column……

Regards,

Pete

• Peter Welch says:

Aaron,

Did you get a response to your query? I have the same need – to also collect the data in an adjacent column……

Regards,

Pete

• Turnitos says:

Use a vlookup formula – using the min as your lookup value

This site uses Akismet to reduce spam. Learn how your comment data is processed.