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.

tablw

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

max min

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.


max value

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.

min value

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.

maxa

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).

maxa value

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.

Advertisement
  • Pingback: Excel 2010: Address Function

  • Pingback: Excel 2010 Basic Arithmetic Operation On DATE Values

  • Pingback: FLOOR.PRECISE Function In Excel 2010

  • Pingback: Find Correlation Between Datasets In Excel 2010

  • Aaron

    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

      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

      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

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

  • nick

    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, 

  • Mike

    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