Max formula in excel is used to calculate the maximum value among a group of cells or data or even in an array, it is an inbuilt formula in excel and to use this formula write =MAX( in a cell and provide the cells with values which we need to compare and find out the maximum value, this formula ignores the text or the space in the input cell.
Max Formula in Excel
MAX stands for Maximum. MAX formula is one of the built-in statistical function in MS Excel, which outputs the maximum value from the numbers provided. This function can be used as a worksheet function. As maximum is related to numbers only, this function ignores empty cells, the logical values TRUE and FALSE, and text values.
Syntax of the MAX Formula
The formula of the Max function is:
- Number1: We need to provide a number, a cell reference to a numerical value, or a range consisting of numeric values.
- Number2: This is an optional argument. As number1, we can provide a number literally, a single cell reference to a numerical value, or a range consisting of numeric values.
In this way, the MAX Exel formula takes arguments up to number 255.
How to Use Max Formula in Excel?
Below is the use of MAX Formula using some examples.
Suppose, we have the following information about marks scored by students in various subjects and the total score. We want to find out the maximum marks scored by students.
To find out the maximum marks in every subject scored by students and aggregately as well, we will use MAX Formula.
- Start typing the function MAX in cell B9 with an equal sign (=). Write MAX and open the brackets.
- Give the reference of range B2:B8 using mouse or keyboard (We need to keep pressing ‘Shift’ key while pressing down arrow to come to B8 cell from B2 cell).
- As we have given a reference for all the cells consisting of marks for English and press ‘Enter’ to get the result.
We came to know that the maximum marks in the ‘English’ subject are 94. In this way, we can get to know about maximum marks for all subjects using the same Max Excel formula. As we have used ‘Relative Reference’ while specifying the cell range, that is why we can copy and paste the function using Ctrl+C and Ctrl+V respectively and the range reference will change automatically.
After doing the same, the result would look like as follows:
- Click on ‘Conditional Formatting’ and choose ‘New Rule’ from the drop-down list.
- Choose the last option as shown in the below image and specify the formula as written below. Click on ‘Format’ button.
Select the green as the fill color for formatting.
Click on ‘Ok’ for both dialog boxes.
Now we can see that the maximum scores are highlighted by green color. If any marks are edited then the formatting will change automatically for the table highlighting only maximum value.
Suppose, we have the following data for attendance of an organization’s employees. We want to find out the maximum time value and the name of the employee who comes at last for any specified date.
There are 217 rows for our data.
To find out the latest employee for a specified date. Steps are:
- We will create a format first to enter the date so that based on that date, the maximum time value will be fetched using the MAX the formats created is:
- We have set data validation for entering date in F3 cell by using the Data Validation feature available in ‘Data Tab’ under ‘Data Tools’ group. To do the same, the steps are:
- Select cell F3 then, click on Data Validation.
- Choose ‘Date’ for ‘Allow’ Choose ‘between’ for ‘Data’ field and specify the MIN and MAX function for a Start date and End date respectively so that user would not be able to enter the date which is not available in the range B2: B17. Click on OK.
Now we can select any date between the dates in the data.
- Now to get maximum value from a time where the date is the same as in cell F3, we will use a combination of IF and MAX function as an array excel function.
We need to use IF function as we only need to compare those values where the date is the same as in cell F3. The syntax would be:
Logical_test for IF would be to compare the dates written in B column with F3 cell value and if the date value gets matched, then we need to pick up the time values written in C column so that we can compare those values to find out the maximum time value.
As there are more than one results for IF function which will be evaluated by the MAX function that is why this is an array function. To submit an array function in MS Excel, we need to press Ctrl+Shift+Enter.
We can do one more thing. That is to highlight the row in data where time value is maximum for that particular date so that we can get to know about employee name.
To do the same, the steps are:
- Click on the ‘Conditional Formatting’ command available in the ‘Styles’ group in ‘Home’ then, choose ‘New Rule’ from the list.
- Choose the last option as shown in the below image and specify the formula as written below.
- Click on ‘Format’ button as shown above to select the orange as the fill color.
- Choose White color as the font color for formatting.
Now whenever we type any date in cell F3, the maximum time is displayed in cell F4 and the row is highlighted too in the data table having maximum time value for that particular date.
Things to Remember
- As maximum is related to numbers only, MAX formula in excel ignores empty cells, the logical values TRUE and FALSE, and text values. But, text representations of numbers and logical values that are provided as an argument directly to the MAX function will be considered for the calculation.
- If we do not provide any argument to the MAX formula, then it returns the 0 as output.
- If anyone of the arguments of MAX function is non-numeric, MAX Excel Formula gives #VALUE! Error.
You can Download this Max Excel Formula template here – Max Excel Formula Template
This has been a guide to Max Excel Formula. Here we discuss how to use Max Excel Formula to find Maximum Value from given data with examples and downloadable excel template. You may learn more about excel from the following articles –