How to use MAX Function (WS)

Description

The Microsoft Excel MAX function returns the largest value from the numbers provided.

Syntax

The syntax for the MAX function in Microsoft Excel is:

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

Parameters or Arguments

number1

It can be a number, named range, array, or reference to a number.

number2, ... number_n

Optional. These are numeric values that can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MAX function examples and explore how to use the MAX function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following MAX examples would return:

=MAX(A2, A3)
Result: 10.5

=MAX(A3, A5, 45)
Result: 45

=MAX(A2:A6)
Result: 200

=MAX(A2:A6, 325)
Result: 325

Frequently Asked Questions

Question: In Microsoft Excel, I want to calculate the largest yearly difference in total between any adjacent year for each company. Below is my spreadsheet with a small sample of 4 years of data.

Right now, I'm using the following formula to calculate the yearly difference:

=IF(AND((MAX(B2:C2)-MIN(B2:C2))>(MAX(C2:D2)-MIN(C2:D2)), (MAX(B2:C2)-MIN(B2:C2))>(MAX(D2:E2)-MIN(D2:E2))), MAX(B2:C2)-MIN(B2:C2), IF((MAX(C2:D2)-MIN(C2:D2))>(MAX(D2:E2)-MIN(D2:E2)), MAX(C2:D2)-MIN(C2:D2), MAX(D2:E2)-MIN(D2:E2)))

I am wondering if there is a way to streamline this and not have such a long and complicated formula.

Answer: Yes, it should be possible to simplify your formula and achieve the same results. By using the ABS function, you could remove some of your IF conditions since you are really looking for the yearly differences as "absolute values". Try replacing your formula with the following:

=MAX(ABS(B2-C2),ABS(C2-D2),ABS(D2-E2))

As you can see in the spreadsheet below, this formula is much simpler and returns the same results.

---

Question:I'm having a problem finding a specific formula in Excel.

Is there a formula that will tell me the Cell with the highest number rather than tell me the highest number? I.E. if K2 has 15 and is the highest, how do I make a formula that says K2 instead of 15?

Answer: You can use the CELL, INDEX, and MATCH functions in combination with the MAX function to return the cell with the highest value.

For example, if you wanted to find the cell with the highest value in the range K1 to K10, you could use the following formula:

=CELL("address",INDEX(K1:K10,MATCH(MAX(K1:K10),K1:K10,0)))

This would return the result as an absolute reference such as $K$2, as you stated in your example where the cell K2 contained 15 which was the highest value.