Go Back

Excel MAX Function

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.

Formula

=MAX (number1,[number2], ...)

Explanation

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

Example

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: 

  1. Open the spreadsheet you want to use the MAX function on. Below is a picture of the spreadsheet we will be working with.

Figure1. click_on_the_cell

  1. Click on the cell where the result would be displayed (F4).

Figure2.click_on_the_cell

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

Figure3. insert_formula

  1. Press Enter on your keyboard. The highest score was 97.

 

Figure4. Press_Enter_for_final_results

Notes

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

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need a formula to tell me if a product is standard, medium or large if i input length, width and height data worked with the following restrictions Standard is: Max dimension <= 52cm, next Max <= 33cm and Min <= 30cm Medium is: Max dimension <= 120cm, next Max <= 80cm and Min <= 80cm Large is: Max dimension > 120cm, or next Max > 80cm
Solved by B. L. in 18 mins
I did a formula ( =MAX(D9,D14,D20,D26,D29)), the max number appears perfectly, but I want the information that appears next to the max number to appear as well. How do I do that? For example , this max formula is also equal to "Instructor Led Training"
Solved by E. H. in 15 mins
Out: Max 522.63 kbps, Avg 28.45 kbps, Last 3.33 kbps Out: Max 94.02 bps, Avg 80.61 bps, Last 94.02 bps Out: Max 2.26 Mbps, Avg 260.99 kbps, Last 1.67 Mbps Out: Max 0.00 bps, Avg 0.00 bps, Last 0.00 bps Out: Max 223.12 kbps, Avg 6.21 kbps, Last 2.83 kbps Out: Max 21.49 kbps, Avg 6.37 kbps, Last 4.24 kbps Out: Max 3.16 kbps, Avg 2.32 kbps, Last 1.85 kbps can i quick select the words and numbers between Avg (numbers) kbps?
Solved by D. L. in 21 mins

Leave a Comment

avatar