MAX Function in Microsoft Excel
Part 1: Introduction
Definition
The MAX function in Microsoft Excel is a valuable tool that returns the most significant value in a set of values.
Purpose
The primary purpose of the MAX function is to find the maximum value among a set of numbers. This can be particularly useful in various scenarios, such as finding the highest sales figure, the most significant expense, the highest temperature, etc.
Syntax & Arguments
The syntax for the MAX function is as follows:
MAX(number1, [number2], ...) Here’s a breakdown of the arguments:
number1: This is the required first argument representing the first number you want to find the maximum value of.number2, ...: These are optional additional numbers you want to consider when finding the maximum value up to 255.
Return Value
The MAX function returns the maximum value among the input numbers.
Remarks
Arguments can be numbers or names, arrays, or references containing numbers. Logical values and text representations of numbers you type directly into the list of arguments are counted. If an argument is an array or reference, only numbers in that array or reference are used. The collection or connection ignores empty cells, logical values, or text. If the arguments contain no numbers, MAX returns 0 (zero). Arguments that are error values or text that cannot be translated into numbers cause errors. If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.
Part 2: Examples
Let’s look at examples of using the MAX function in business.
Example 1
Purpose of Example: To find the highest sales figure among a set of sales figures.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales Figure | MAX |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | MAX | =MAX(A2:A4) |
Explanation: In this example, we have a list of sales figures. The formula =MAX(A2:A4) finds the maximum value among these sales figures. The result is $7000.
Example 2
Purpose of Example: To find the highest expense among a set of costs.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Expense | MAX |
| 2 | $200 | |
| 3 | $300 | |
| 4 | $250 | |
| 5 | MAX | =MAX(A2:A4) |
Explanation: In this example, we have a list of expenses. The formula =MAX(A2:A4) finds the maximum value among these expenses. The result is $300.
Example 3
Purpose of Example: To find the highest temperature recorded in a week.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Temperature | MAX |
| 2 | 72 | |
| 3 | 75 | |
| 4 | 73 | |
| 5 | MAX | =MAX(A2:A4) |
Explanation: In this example, we have a list of temperatures. The formula =MAX(A2:A4) finds the maximum value among these temperatures. The result is 75.
Example 4
Purpose of Example: To find the highest score among test scores.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Score | MAX |
| 2 | 85 | |
| 3 | 90 | |
| 4 | 88 | |
| 5 | MAX | =MAX(A2:A4) |
Explanation: In this example, we have a list of test scores. The formula =MAX(A2:A4) finds the maximum value among these test scores. The result is 90.
Example 5
Purpose of Example: To find the highest value among a set of stock prices.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Stock Price | MAX |
| 2 | $50 | |
| 3 | $55 | |
| 4 | $52 | |
| 5 | MAX | =MAX(A2:A4) |
Explanation: In this example, we have a list of stock prices. The formula =MAX(A2:A4) finds the maximum value among these stock prices. The result is $55.
Example 6
Purpose of Example: To find the highest sales figure among sales figures over $5000.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales Figure | MAX |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | MAX | =MAX(IF(A2:A4>5000, A2:A4)) |
Explanation: In this example, we have a list of sales figures. The formula =MAX(IF(A2:A4>5000, A2:A4)) finds the maximum value among these sales figures over $5000. The result is $7000.
Example 7
Purpose of Example: To find the highest expense among costs less than $300.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Expense | MAX |
| 2 | $200 | |
| 3 | $300 | |
| 4 | $250 | |
| 5 | MAX | =MAX(IF(A2:A4<300, A2:A4)) |
Explanation: In this example, we have a list of expenses. The formula =MAX(IF(A2:A4<300, A2:A4)) finds the maximum value among these expenses less than $300. The result is $250.
Example 8
Purpose of Example: To find the highest temperature recorded in a week that is over 70 degrees.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Temperature | MAX |
| 2 | 72 | |
| 3 | 75 | |
| 4 | 73 | |
| 5 | MAX | =MAX(IF(A2:A4>70, A2:A4)) |
Explanation: In this example, we have a list of temperatures. The formula =MAX(IF(A2:A4>70, A2:A4)) finds the maximum value among temperatures over 70 degrees. The result is 75.
Example 9
Purpose of Example: To find the highest score among a set of test scores over the average.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Score | MAX |
| 2 | 85 | |
| 3 | 90 | |
| 4 | 88 | |
| 5 | MAX | =MAX(IF(A2:A4>AVERAGE(A2:A4), A2:A4)) |
Explanation: In this example, we have a list of test scores. The formula =MAX(IF(A2:A4>AVERAGE(A2:A4), A2:A4)) finds the maximum value among these test scores over the average. The result is 90.
Example 10
Purpose of Example: To find the highest value among stock prices over the median price.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Stock Price | MAX |
| 2 | $50 | |
| 3 | $55 | |
| 4 | $52 | |
| 5 | MAX | =MAX(IF(A2:A4>MEDIAN(A2:A4), A2:A4)) |
Explanation: In this example, we have a list of stock prices. The formula =MAX(IF(A2:A4>MEDIAN(A2:A4), A2:A4)) finds the maximum value among these stock prices over the median price. The result is $55.
Example 11
Purpose of Example: To find the highest sales figure among a set of sales figures over $5000 using the SUM function.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales Figure | MAX |
| 2 | $5000 | |
| 3 | $7000 | |
| 4 | $6000 | |
| 5 | MAX | =MAX(IF(A2:A4>SUM(2500,2500), A2:A4)) |
Explanation: In this example, we have a list of sales figures. The formula =MAX(IF(A2:A4>SUM(2500,2500), A2:A4)) finds the maximum value among these sales figures that are over the sum of 2500 and 2500 (5000). The result is $7000.
Example 12
Purpose of Example: To find the highest expense among a set of costs less than $150 and $150.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Expense | MAX |
| 2 | $200 | |
| 3 | $300 | |
| 4 | $250 | |
| 5 | MAX | =MAX(IF(A2:A4<SUM(150,150), A2:A4)) |
Explanation: In this example, we have a list of expenses. The formula =MAX(IF(A2:A4<SUM(150,150), A2:A4)) finds the maximum value among these expenses that are less than the sum of $150 and $150 (300). The result is $250.
Example 13
Purpose of Example: To find the highest temperature recorded in a week that is over the sum of 35 and 35 degrees.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Temperature | MAX |
| 2 | 72 | |
| 3 | 75 | |
| 4 | 73 | |
| 5 | MAX | =MAX(IF(A2:A4>SUM(35,35), A2:A4)) |
Explanation: In this example, we have a list of temperatures. The formula =MAX(IF(A2:A4>SUM(35,35), A2:A4)) finds the maximum value among these temperatures over 35 and 35 degrees (70). The result is 75.
Part 3: Tips and Tricks
- MAX can handle up to 255 arguments, allowing you to find the maximum value among large numbers.
- MAX ignores empty cells, logical values, or text in the array or reference. If you want to include these in your calculation, use the MAXA function.
- You can use MAX with other functions for more complex calculations. For example, you could use MAX inside an IF function to calculate only if a certain number is a maximum value.
- MAX can be used with arrays or references, not just individual numbers. This makes it a powerful tool for analyzing large sets of data.
- If the arguments contain no numbers, MAX returns 0. Be mindful of this when analyzing your results.