Using Excel's MAX Formula
Oftentimes, you'll want to use Excel to find the maximum value in a range or series of numbers. For example, given a list of salespeople and corresponding dollar sales in any given month, you might want to find the maximum dollar sales to identify the highest-performing salesperson.
One way to accomplish this is by looking through your list of values and manually identifying the largest one. But that seems particularly time consuming and error-prone — especially if you're working with a big list. That's why Excel includes the
A simple use of the MAX function
MAX( number_or_range_1, number_or_range_2 (optional)...)
Note a couple of things here: first of all, the
Let's take a look at a simple example to demonstrate this formula in action:
MAX( 4, 5, 6)
The above formula outputs the value
Here's another example using a larger series of numbers:
MAX( -7, 15, -100, 0, 3)
This formula outputs the value
Using MAX with a range
Per the above, the
In the above example, we use
This is certainly useful, but what we really care about here is the name of the best-performing category — not necessarily the dollar value of sales within that category. Is there a way to extract just the name of the best-performing category using the
The answer is yes — but to do so, we'll need to use a much more complicated function called
INDEX( B3:B6, MATCH( F2, C3:C6, 0))
This may seem complicated, but if you'd like to learn more, head over to our
Other uses of MAX
- Find the most recent day in a range of dates;
- Identify top-performing students by assignment;
- Find salespeople with the most sales in a given time range;
- Find the highest-recorded temperature in a given location; and
- Benchmark stock prices to maximum historical performance.
Questions on how to use
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
- How to create beautiful table formatting instantly...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...