The MAX function in Microsoft Excel is used to return the largest numeric value among a range of values. The Excel MAX function doesn’t count any empty cell, any values in text or the logical values of TRUE and FALSE.
The purpose of the Excel MAX function is to help get the highest value from a range, and it helps reduce the time taken to search for these values manually. This function helps to return the largest value from a set of independent data. For instance, the highest temperature in a city, the highest number of views on an advert, or the highest scores students have gotten from a test.
The arguments for this function can be provided as either constant or as cell references or ranges.
=MAX (number1,[number2], ...)
- number1 – this could be a number, reference to a numeric value, or even a range that contains a set numeric value (as we would be using in our examples).
- number2 – [optional] a number, a reference to a numeric value, or a range that contains a set of numeric values.
How to use the MAX function?
The spreadsheet in this example contains the scores of 5 students on an English test. We are going to find the highest score in this set of scores.
Please follow the step-by-step guideline below:
- Open the spreadsheet you want to use the MAX function on. Below is a picture of the spreadsheet we will be working with.
- Click on the cell where the result would be displayed (F4).
- Insert the formula;
=MAX(C5:C9)into the cell.
We are making use of a range C5:C9 because that is the column where we have stored our students’ scores (values to be assessed).
- Press Enter on your keyboard. The highest score was 97.
- The MAX function can also be used as many values as possible; values that you manually input into the formula. Use this format;=MAX(45,2,30,573,34). The result should be 573.
- However, if the arguments fed into the function don’t contain any numbers, then the function will return a zero.
- The Excel MAX function doesn’t have any activity on empty cells since they don’t contain any data that can be acted upon.
- In the current versions of Microsoft Excel, the MAX function is able to accept up to 255 arguments.
- The arguments for which the MAX function can act no can be provided as numbers, names, arrays, or references. As long as they contain some numbers, the MAX function can act on them.
- TRUE and FALSE values, as well as numbers which are entered as text (for instance, “zero”, “five”, etc.), are also ignored by the Excel MAX function.