# MAX function in Excel

Part 1: Introduce

💡 Definition: The MAX function in Microsoft Excel is used to find the maximum value in a range or a set of values.

💡 Purpose: The MAX function aims to identify the highest value within a specified range or a list of values. It is commonly used in data analysis, financial calculations, and other scenarios where finding the maximum value is essential.

💡 Syntax & Arguments:

syntax
```MAX(number1, [number2], [...]) ```

💡 Arguments in the Function:

• `number1`, `number2`, and so on. These are the values or cell references from which you want to find the maximum value.

💡 Return Value: The MAX function returns the maximum value from the specified range or set of values.

💡 Remarks: The MAX function ignores empty cells, cells with text, and cells with logical or error values within the range.

Part 2: Examples

Example 1: Maximum Sales Revenue Consider a sales report with the revenue generated for different products.

AB
1ProductRevenue
2Product1\$5000
3Product2\$6500
4Product3\$4800
5Product4\$7200
6Product5\$5800

💡 Formula (B8): `=MAX(B2:B6)` 💡 Result: The maximum sales revenue will be calculated as \$7200.

Example 2: Maximum Temperature Determine the highest temperature recorded weekly.

AB
1DayTemp
2Mon32
3Tue29
4Wed35
5Thu31
6Fri34
7Sat30

💡 Formula (B8): `=MAX(B2:B7)` 💡 Result: The maximum temperature recorded during the week will be 35.

Example 3: Maximum Stock Price Find the highest stock price among companies.

AB
1CompanyPrice
2ABC\$50.5
3XYZ\$65.2
4DEF\$55.8
5PQR\$60.1
6MNO\$58.7

💡 Formula (B8): `=MAX(B2:B6)`
💡 Result: The maximum stock price among companies will be \$65.2.

Example 4: Maximum Number of Sales Calculate the highest sales made by different salespersons.

AB
1NameSales
2John12
3Maria15
4Alex10
5Jane18
6Eric14

💡 Formula (B8): `=MAX(B2:B6)` 💡 Result: The maximum sales a salesperson makes will be 18.

Example 5: Maximum Production Output Determine the highest production output for different manufacturing units.

AB
1ManufacturingOutput
2Unit1500
3Unit2720
4Unit3600
5Unit4480
6Unit5550

💡 Formula (B8): `=MAX(B2:B6)` 💡 Result: The maximum production output among manufacturing units will be 720.

How to nest the MAX function with other functions in Microsoft Excel:

Example 1: Maximum Sales Increase Consider a sales report with the monthly sales figures for a product.

AB
1MonthSales
2Jan\$5000
3Feb\$6500
4Mar\$4800
5Apr\$7200
6May\$5800
• 💡 Formula (C2): `=MAX(B2:B6)-MIN(B2:B6)`
• 💡 Result: The maximum increase in sales for the product will be calculated as \$2400.

Example 2: Maximum Age Difference Calculate the maximum age difference between two data sets.

ABC
1PersonAge1Age2
2John4035
3Maria5550
4Alex3040
5Jane4530
• 💡 Formula (D2): `=MAX(ABS(B2:B5-C2:C5))`
• 💡 Result: The maximum age difference between the two data sets will be 20.

Example 3: Maximum Profit Percentage Determine the maximum profit percentage for a set of investments.

ABC
1ItemCost PriceSale Price
2A\$100\$150
3B\$80\$120
4C\$90\$110
• 💡 Formula (D2): `=MAX(((C2:C4-B2:B4)/B2:B4)*100)`
• 💡 Result: The maximum profit percentage for the investments will be calculated as 87.5%.

Example 4: Maximum Frequency Find the maximum frequency of a value in a range.

A
1Value
25
33
45
57
65
• 💡 Formula (B2): `=MAX(COUNTIF(A2:A6,A2:A6))`
• 💡 Result: The maximum frequency of a value in the range will be calculated as 3.

Example 5: Maximum Length of Text Determine the maximum length of text in a range of cells.

A
1Text
2Apple
3Banana
4Orange
5Mango
• 💡 Formula (B2): `=MAX(LEN(A2:A5))`
• 💡 Result: The maximum length of text in the range will be 6.

Example 6: Maximum Unique Values Calculate the maximum number of unique values in a range.

A
1Value
25
33
45
57
63
75
• 💡 Formula (B2): `=MAX(SUM(1/COUNTIF(A2:A7,A2:A7)))`
• 💡 Result: The maximum number of unique values in the range will be calculated as 3.

Example 7: Maximum Conditional Count the maximum number of occurrences that meet specific criteria.

AB
1NameCategory
2JohnCategory1
3MariaCategory2
4AlexCategory1
5JaneCategory3
6EricCategory2
• 💡 Formula (C2): `=MAX(COUNTIFS(B2:B7,"Category1",B2:B7,B2:B7))`