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;
We will be writing it as;
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;
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.