How to Find the Highest Value Within a Range in Excel
In this Excel tutorial you will teach yourself how to find the highest value within a range.
The easiest way to find the highest value in a range in Excel is by using the MAX function.
Basic MAX Function
The MAX function finds the largest number in a range of cells. It ignores text and only works with numbers.
Syntax:
=MAX(range)Steps to use it:
- Click on the cell where you want the result to appear.
- Type
=MAX( - Select or type the range you want to check (for example, B2:B10).
- Close the parenthesis and press Enter.
Example:
=MAX(B4:B8)This formula returns the highest value found in cells B4 through B8.
You can also access MAX through the ribbon. Go to the Home tab, click the AutoSum dropdown, and select Max. Then select your range and press Enter.
Finding the Position of the Highest Value
If you also want to know where the highest value is located (not just the value itself), combine MAX with MATCH.
Formula:
=MATCH(MAX(range), range, 0)Example:
=MATCH(MAX(C3:C11), C3:C11, 0)This returns the position (row number within the range) of the maximum value. For instance, if the result is 4, the highest value is the 4th item in that range.
Finding the Maximum with a Condition
Sometimes you want the highest value that matches a specific criterion. For example, the highest quantity sold for a specific product. There are a few ways to do this.
Using MAXIFS (simplest method)
=MAXIFS(D5:D14, B5:B14, "Orange")This returns the maximum value in D5:D14 where column B equals “Orange”.
Using MAX as an array formula
=MAX((B5:B14="Apple")*(C5:C14))This multiplies matching rows by their values and returns the highest one.
Quick Reference
| Goal | Formula |
|---|---|
| Highest value in a range | =MAX(range) |
| Position of highest value | =MATCH(MAX(range), range, 0) |
| Highest and lowest value together | =MAX(range) and =MIN(range) |
| Highest value matching a condition | =MAXIFS(value_range, criteria_range, criteria) |
For most everyday needs, the plain =MAX(range) formula is all you need. When you need more advanced results, such as the position of the maximum value or a conditional maximum, combining MAX with MATCH or using MAXIFS gives you the flexibility to handle more complex data analysis tasks.
Leave a Reply