by The tutorial explains the MAX function with many formula examples that show how to find highest value in Excel and highlight largest number in your worksheet.
MAX is one of the most straightforward and easy-to-use Excel functions. However, it does have a couple of tricks knowing which will give you a big advantage. Say, how do you use the MAX function with conditions? Or how would you extract the absolute largest value? This tutorial provides more than one solution for these and other related tasks.
Excel MAX function
The MAX function in Excel returns the highest value in a set of data that you specify.
The syntax is as follows:
Where number can be represented by a numeric value, array, named range, a reference to a cell or range containing numbers.
Number1 is required, number2 and subsequent arguments are optional.
The MAX function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.
How to make a MAX formula in Excel
To create a MAX formula in its simplest from, you can type numbers directly in the list of arguments, like this:
=MAX(1, 2, 3)
In practice, it's quite a rare case when numbers are "hardcoded". For the most part, you will deal with ranges and cells.
The fastest way to build a Max formula that finds the highest value in a range is this:
- In a cell, type =MAX(
- Select a range of numbers using the mouse.
- Type the closing parenthesis.
- Press the Enter key to complete your formula.
For example, to work out the largest value in the range A1:A6, the formula would go as follows:
=MAX(A1:A6)
If your numbers are in a contiguous row or column (like in this example), you can get Excel to make a Max formula for you automatically. Here's how:
- Select the cells with your numbers.
- On the Home tab, in the Formats group, click AutoSum and pick Max from the drop-down list. (Or click AutoSum > Max on the Formulas tab in the Function Library group.)
This will insert a ready-to-use formula in a cell below the selected range, so please make sure there is at least one blank cell underneath the list of numbers that you've selected:
5 things to know about MAX function
To successfully use Max formulas your worksheets, please remember these simple facts:
- In the current versions of Excel, a MAX formula can accept up to 255 arguments.
- If the arguments do not contain a single number, the MAX function returns zero.
- If the arguments contain one or more error values, an error is returned.
- Empty cells are ignored.
- Logical values and text representations of numbers supplied directly in the list of arguments are processed (TRUE evaluates as 1, FALSE evaluates as 0). In references, logical and text values are ignored.
How to use MAX function in Excel – formula examples
Below you will find a few typical uses of the Excel MAX function. In many cases, there are a few different solutions for the same task, so I encourage you to test all the formulas to choose the one best suited for your data type.
How to find max value in a group
To extract the largest number in a group of numbers, supply that group to the MAX function as a range reference. A range can contain as many rows and columns as you desire. For example, to get the highest value in the range C2:E7, use this simple formula:
=MAX(C2:E7)
Find highest value in non-adjacent cells or ranges
To make a MAX formula for non-contiguous cells and ranges, you need to include a reference to each individual cell and/or range. The following steps will help you to do that quickly and flawlessly:
- Start typing a Max formula in a cell.
- After you've typed the opening parenthesis, hold down the Ctrl key and select the cells and ranges in the sheet.
- After selecting the last item, release Ctrl and type the closing parenthesis.
- Press Enter.
Excel will use an appropriate syntax automatically, and you will get a formula similar to this:
=MAX(C5:E5, C9:E9)
As shown in the screenshot below, the formula returns the maximum sub-total value from rows 5 and 9:
How to get max (latest) date in Excel
In the internal Excel system, dates are nothing else but serial numbers, so the MAX function handles them without a hitch.
For instance, to find the latest delivery date in C2:C7, make a usual Max formula that you'd use for numbers:
=MAX(C2:C7)
MAX function in Excel with conditions
When you wish to get the maximum value based on conditions, there are several formulas for you to choose from. To make sure that all the formulas return the identical result, we will test them on the same set of data.
The task: With the items listed in B2:B15 and sales figures in C2:C15, we aim to find the highest sale for a specific item input in F1 (please see the screenshot at the end of this section).
Excel MAX IF formula
If you a looking for a formula that works in all versions of Excel 2000 through Excel 2019, use the IF function to test the condition, and then pass the resulting array to the MAX function:
=MAX(IF(B2:B15=F1, C2:C15))
For the formula to work, it must press Ctrl + Shift + Enter simultaneously to enter it as an array formula. If all done correctly, Excel will enclose your formula in {curly braces}, which is a visual indication of an array formula.
It is also possible to evaluate several conditions in a single formula, and the following tutorial shows how: MAX IF with multiple conditions.
Non-array MAX IF formula
If you don't like using array formulas in your worksheets, then combine MAX with the SUMPRODUCT function that processes arrays natively:
=SUMPRODUCT(MAX((B2:B15=F1)*(C2:C15)))
For more information, please see MAX IF without array.
MAXIFS function
In Excel 2019 and Excel for Office 365, there is a special function named MAXIFS, which is designed to find the highest value with up to 126 criteria.
In our case, there is just one condition, so the formula is as simple as:
=MAXIFS(C2:C15, B2:B15, F1)
For the detailed explanation of the syntax, please see Excel MAXIFS with formula examples.
The below screenshot shows all 3 formulas in action:
Get max value ignoring zeros
This is, in fact, a variation of conditional MAX discussed in the previous example. To exclude zeros, use the "not equal to" logical operator and put the expression "<>0" in either the criteria of MAXIFS or the logical test of MAX IF.
As you understand, testing this condition only makes sense in case of negative numbers. With positive numbers, this check is superfluous because any positive number is greater than zero.
To give it a try, let's find the lowest discount in the range C2:C7. As all the discounts are represented by negative numbers, the smallest discount is actually the largest value.
MAX IF
Be sure to press Ctrl + Shift + Enter to correctly complete this array formula:
=MAX(IF(C2:C7<>0, C2:C7))
MAXIFS
It's a regular formula, and a usual Enter keystroke will suffice.
=MAXIFS(C2:C7,C2:C7,"<>0")
Find highest value ignoring errors
When you work with a large amount of data driven by various formulas, chances are that some of your formulas will result in errors, which will cause a MAX formula to return an error too.
As a workaround, you can use MAX IF together with ISERROR. Given that you are searching in the range A1:B5, the formula takes this shape:
=MAX(IF(ISERROR(A1:B5)), "", A1:B5))
To simplify the formula, use the IFERROR function instead of the IF ISERROR combination. This will also make the logic a bit more obvious – if there's an error in A1:B5, replace it with an empty string (''), and then get the maximum value in the range:
=MAX(IFERROR(A1:B5, ""))
A fly in the ointment is that you need to remember to press Ctrl + Shift + Enter because this only works as an array formula.
In Excel 2019 and Excel for Office 356, the MAXIFS function can be a solution, provided that your data set contains at least one positive number or zero value:
=MAXIFS(A1:B5,A1:B5,">=0")
Since the formula searches for the highest value with the condition "greater than or equal to 0", it won't work for a data set consisting of solely negative numbers.
All these limitations are not good, and we are evidently in need of a better solution. The AGGREGATE function, which can perform a number of operations and ignore error values, fits perfectly:
=AGGREGATE(4, 6, A1:B5)
The number 4 in the 1st argument indicates the MAX function, the number 6 in the 2nd argument is the "ignore errors" option, and A1:B5 is your target range.
Under perfect circumstances, all three formulas will return the same result:
How to find absolute max value in Excel
When working with a range of positive and negative numbers, sometimes you may wish to find the largest absolute value regardless of the sign.
The first idea that comes to mind is to get the absolutes values of all numbers in the range by using the ABS function and feed those to MAX:
This is an array formula, so don't forget to confirm it with the Ctrl + Shift + Enter shortcut. Another caveat is that it only works with numbers and results in an error in case of non-numeric data.
Not happy with this formula? Then let us build something more viable :)
What if we find the minimum value, reverse or ignore its sign, and then evaluate along with all other numbers? Yep, that will work perfectly as a normal formula. As an extra bonus, it handles text entries and errors just fine:
With the source numbers in A1:B5, the formulas go as follows.
Array formula (completed with Ctrl + Shift + Enter):
=MAX(ABS(A1:B5))
Regular formula (completed with Enter):
=MAX(MAX(A1:B5), -MIN(A1:B5))
or
=MAX(MAX(A1:B5), ABS(MIN(A1:B5)))
The below screenshot shows the results:
Return the maximum absolute value preserving the sign
In some situations, you may have a need to find the largest absolute value but return the number with its original sign, not the absolute value.
Assuming the numbers are in cells A1:B5, here's the formula to use:
=IF(ABS(MAX(A1:B5))>ABS(MIN(A1:B5)), MAX(A1:B5), MIN(A1:B5))
Complex at first sight, the logic is quite easy to follow. First, you find the largest and smallest numbers in the range and compare their absolute values. If the absolute max value is greater than the absolute min value, the maximum number is returned, otherwise – the minimum number. Because the formula returns the original and not absolute value, it keeps the sign information:
How to highlight max value in Excel
In situation when you want to identify the largest number in the original data set, the fastest way is to highlight it with Excel conditional formatting. The below examples will walk you through two different scenarios.
Highlight highest number in a range
Microsoft Excel has a predefined rule to format top ranked values, which suits our needs perfectly. Here are the steps to apply it:
- Select your range of numbers (C2:C7 in our case).
- On the Home tab, in the Styles group, click Conditional formatting > New Rule.
- In the New Formatting Rule dialog box, choose Format only top or bottom ranked values.
- In the lower pane, pick Top from the drop-down list and type 1 in the box next to it (meaning you want to highlight just one cell containing the largest value).
- Click the Format button and select the desired format.
- Click OK twice to close both windows.
Done! The highest value in the selected range is automatically highlighted. If there is more than one max value (duplicates), Excel will highlight them all:
Highlight max value in each row
Since there is no built-in rule to make the highest value stand out from each row, you will have to configure your own one based on a MAX formula. Here's how:
- Select all the rows in which you want to highlight max values (C2:C7 in this example).
- On the Home tab, in the Styles group, click New Rule > Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter this formula:
=C2=MAX($C2:$E2)Where C2 is the leftmost cell and $C2:$E2 is the first row range. For the rule to work, be sure to lock the column coordinates in the range with the $ sign.
- Click the Format button and choose the format you want.
- Click OK twice.
Tip. In a similar manner, you can highlight the highest value in each column. The steps are exactly the same, except that you write a formula for the first column range and lock the row coordinates: =C2=MAX(C$2:C$7)
For more information, please see How to create a formula-based conditional formatting rule.
Excel MAX function not working
MAX is one of the most straightforward Excel functions to use. If against all expectations it does not work right, it's most likely to be one of the following issues.
MAX formula returns zero
If a normal MAX formula returns 0 even though there are higher numbers in the specified range, chances are those numbers are formatted as text. It's especially the case when you run the MAX function on data driven by other formulas. You can check this by using the ISNUMBER function, for example:
=ISNUMBER(A1)
If the above formula returns FALSE, the value in A1 is not numeric. Meaning, you should troubleshoot the original data, not a MAX formula.
MAX formula returns #N/A, #VALUE or other error
Please check the referenced cells carefully. If any of the referenced cells contains an error, a MAX formula will result in the same error. To bypass this, see how to get the max value ignoring all errors.
That's how to find max value in Excel. I thank you for reading and hope to see you on our blog soon!
134 comments
Hi. I'm adapting a calendar I found in a video. Mostly successful but there's one additional thing I'd like to do.
The calendar input is just the 1st day of a month (enter 7/1/2024, for instance). This sets up a 7x46 grid (each week has 9 lines for handwritten schedule entries, and the days of the week (Sunday-Saturday) appear along the top of the grid). Each day (block of 9 lines) displays the day of the month in the upper left corner of the first lines. All days display this number, even if they're not within the month of interest.
My "inherited" function for displaying the first day after the end of each week is:
=IF($A13=0,MAX($C$4:$I12)+1,"")
(column A is an index of the 9 lines in each day, from 0 to 8. If it's 0, that's the first line and a day will be displayed on it. All other lines are blank). So this compares all the dates from the 0,0 day of the calendar to find the latest displayed date and increments it by one to display the first day of the next week. This works great as long as each day is displayed with only the day (custom cell format "d").
The problem is that I would like to display both the month and the day for the first day only of the month of interest. When I do that, if the first day of the month falls on a Saturday (last day of the week), it's no longer recognized as the latest displayed date and the 1st of the month is repeated on that second week. If I test the VALUE() of each day in that first week, they are returned correctly as ascending serial numbers. But I cannot figure out how to work that VALUE() function into the above formula. Do you know how I use VALUE() to get the serial value of each cell in that range to find the MAX?
Thanks, sorry if confusing but it appears I can't insert a screenshot.
Hello Kate!
You do not need to use VALUE function if cells $C$4:$I12 contain dates rather than text. Excel stores dates as numbers. Without seeing your data, it is difficult to understand your question and give you more precise advice. If you can give me a more detailed description of the data that this formula uses, I will have a better chance of understanding and helping you.
Thank you. I'll try to make it easier to follow with some code examples. I still can't make screenshots appear here though.
***Stop here unless you're interested! I think I ran into the date-text formatting unpredictability I've read about. It doesn't seem like I'll be able to do what I wanted here after all***
The first row of the calendar for June 2024 shows (displays), arranged Sunday through Saturday:
[26 27 28 29 30 31 1] These cells use the custom format "d" to only show the day, but they are dates because if I test =VALUE(each of those cells) I get the serial dates 45438 - 45444. The formula to get this first row is:
If Sunday (1) is less than June 1st's position in the week (7), subtract the interval from 6/1/24, otherwise if Sunday IS the first day of the month insert the 1, otherwise increment the previous cell by one day. The code below is for Monday 5/27. It places 27 in cell D4.
=IFS(VALUE(D3)StartDay,C4+1)
The first day of the next week is the last day of the previous week incremented by 1, using that MAX formula. (A13 being zero just signals that it is the row into which I want to insert date numbers.):
=IF($A13=0,MAX($C$4:$I12)+1,"").
The rest of the days of the 2nd week just increment by one, and then the whole thing repeats for the 3rd and subsequent rows.
But I want the 1st day of the month to read "June-1" and leave all the other days as just the custom "d" formatted numbers. I can do that with:
=IFS(VALUE(D3)StartDay,C4+1)
and the cell that gets the month-day treatment still returns a VALUE() of the correct serial date. But if it falls on a Saturday (as June 1 does), the MAX function doesn't return it as the highest value, so I end up with 2 June 1st entries (on Saturday and Sunday). Every month that starts on Sunday-Friday works fine with this, just not Saturday. And here I stop because I found other problems using this in other years besides 2024.....
Hello Kate!
If I understand you correctly, you can change the date format of the first day of the month not by using a formula, but by using conditional formatting.
I recommend reading this guide: How to conditionally format dates and time in Excel with formulas and inbuilt rules.
To define the day of the month, use the DAY function.
=DAY(D3)=1
Hi
I have a large datasheet with approximately 40000 rows. Each row represent a date and time, over the course of multiple years, and some values (like altitude).
I would like to 'extract' from that datasheet a new subsheet, that only gives the highest value for each day.
Like
01/12/2022 - 10:00 - 10m
01/12/2022 - 11:00 - 20m
01/12/2022 - 12:00 - 30m
01/12/2022 - 13:00 - 60m
01/12/2022 - 14:00 - 100m
01/12/2022 - 15:00 - 05m
02/12/2022 - 06:00 - 0m
02/12/2022 - 07:00 - 10m
02/12/2022 - 09:00 - 20m
02/12/2022 - 11:00 - 300m
Should result in
01/12/2022 - 14:00 - 100m
02/12/2022 - 11:00 - 300m
Can anyone help me how I should do this?
Hi Diederik!
You can find maximum value if it is a number, not text. "100m is text. You can remove the letter "m" from all values in a column using the Find and Replace tool.
You can get maximum value for a specific date if you use the guidelines in this section of the article above: MAX function in Excel with conditions.
=SUMPRODUCT(MAX((A1:A10=F1)*(C1:C10)))
You can also find useful information in this article: Excel MAXIFS function - get largest value based on multiple criteria.
=MAXIFS($C$1:$C$10,$A$1:$A$10,F1)
You can also get a list of dates, for example in column G, by using UNIQUE function.
=UNIQUE(A1:A10)
Then in cell H1, write FILTER formula to extract the maximum values that match the date in column G.
=FILTER($B$1:$C$10,($A$1:$A$10=G1)*($C$1:$C$10=MAXIFS($C$1:$C$10,$A$1:$A$10,G1)))
After that you can copy this formula down along the column.
You can also use a pivot table to automatically get a list of dates and the maximum value for each date. Read more: How to make and use Pivot Table in Excel.
students result 1 to 30 sheets. one column is highest marks. when i put =max '1':'30'!$K$9 but show #REF!. how I solve it.
Hi! We have a special tutorial on this. Please see: 3-D reference in Excel: reference the same cell or range in multiple worksheets. Based on this information, the formula could be as follows:
=MAX('1:30'!K9)
Hi i need to highlight only the dates in excel which has the highest number
eg :
01.01.2024 - 34
02.01.2024 - 22
03.01.2024 - 14
04.01.2024 - 20
05.01.2024 - 25
i need to highlight the top 2 not the numbers the dates only in this case top 2 numbers are 34 and 25 i need 01.01.2024 and 05.01.2024 to be highlighted
Hi! Try to use the recommendations described in this article: Excel conditional formatting formulas based on another cell. If dates are written in column A and numbers are written in column B, create a conditional formatting rule for column A with the formula
=LARGE($B$1:$B$5,2)<=B1
For more information, please visit: Excel LARGE function to get n-th highest value.
Can I use maxifs or max(if.. to find the max value based on font color in a column where each cell text font is one of three different colors?
I retreived the font color numbers from get.cell(24.. The colors are based on value ranges.
10.0000 <-- font green (50)
0.7557 <-- font red (53)
0.0435 <-- font blue (42)
215.8094 <-- font green (50)
0.6836 <-- font red (53)
0.0524 <-- font blue (42)
0.5349 <-- font red (53)
Hi! If you add one more column of font color numbers to your table, you can use this column to find the maximum value based on the condition.
Instead of =MAX(range)
you can use =LARGE(range,1)
What if the header is number example:1990 which indicates year, and the column has more than 670 rows of values. While executing max formula how to turn off the header so that the formula does not count header as value?
N.B: I have formatted the header as text and the result is same. The result for max value turns out to be 1990 value. I have multiple dataset with variation of rows for a column so I dont want to go "=max(A2:A671)", I want to go "=max(A:A)" for simplicity, and I do not want header to counted while executing the formula.
Choose either simplicity or precision. max(A:A) uses the entire column.
How to change in X Simple to number
Example : (98XXXX86 to 98652786)
To replace characters in the text, use the SUBSTITUTE function.
Good Morning,
If there are several columns, with several values, then i have to highlight highest values in each column at a time by selecting the entire data rather than selecting single column and formatting the top most value in each column, Similarly, for many rows also, Can u pls suggest me an easy method.
Thanks in Advance,
Kotha Jyothi.
Hi! Use the MAX function and copy the formula MAX(A1:A20) to the right as far as necessary.
Dear Alexander,
If my question is irrelevant here, please direct me to where I can find the answer.
I am looking for a way to highlight a group of three adjacent cells that have the highest total (cumulative) value in a row. I can identify this value using an array formula, but it seems not to be working in the Conditional formatting tool (or I am doing it in the wrong way). Here is an example of my data:
_____A____B___C___D___E___F__
1___126__91__80__66__99__65
2___101__47__78__88__66__70
In the first row, cells A1, B1 and C1 produce the highest total value (126+91+80=297). I get it by using a formula =MAX(A1:D1+B1:E1+C1:F1), then pressing Ctrl-Shift-Enter. In the second row, the respective cells are C2, D2 and E2 (=232). Now I just need to highlight these tree cells with a color in each row, and then spread this rule onto next rows downwards (the whole data set takes several thousands of rows).
When I insert the above formula in the respective field of Conditional formatting tool, it produces no result. It either highlights all cells which are selected (even if the selected cells are empty or outside the range of data) when I use "$" to lock colums/rows, or neither of cells is highlighted when I do not use "$" in the formula.
I would greatly appreciate your help to find the answer.
PS: I need to color-mark these cells to use them as a condition for making calculations in a different (but associated) range of data. As I haven't found a direct formula for this, I am trying to do it through cell formatting. The color format of cells in the first data set will be copied to the second data set and the new colored cells (containing new values) will be summed.
Hi! You cannot do conditional formatting with your formula.
To do conditional formatting on cell A1, you have to compare its value to another value. If you tell me what cell A1 can be compared to, I will try to help.
I can help you make a formula, but I won't solve a logical task for you.
Thanks for your reply! I will try to figure out how to do it in a different way.
How to select the maximum value <100 from the command MAX(AO68;AQ68;AS68;AU68) if the max values change within the command? Thank you.
You can find the maximum value from non-adjacent cells by condition using this formula:
=MAX((A1*(A1<100)),(B1*(B1<100)),(C1*(C1<100)))
Thx..could you give the formula to return the region after finding the Max from the three months?
Regards,
Larry
Hi! If I understand correctly, use INDEX MATCH functions to find the row with the maximum value.
For example,
=INDEX(A1:A5,MIN(IF(C2:F5=MAX(C2:F5),ROW(A2:A5))))
This is very great. Please I want to find the MAX but to replace the value with the name how do I do that?
Hi! You cannot replace a number in a cell with a name using an Excel formula. If I have misunderstood, please explain your question in more detail.
This blog is the most comprehensive but I just need one more step.
My set of data is a measure of both positive and negative deviation from 0 with so called 'error' cells too. I'm looking for formula to give me maximum value from zero including its sign.
It looks like I could do with a combination of Index / match / aggregate. Data example is
Column A : #N/A, #N/A, 6, 5, -2, 0, 0, -5, -3, -4, #N/A, #N/A, -10, -15, -12, 0, 11
The formula result would then be -15
Thanks fro your help
Hi! Replace the errors with zero using the IFERROR function. Use the ABS function to take absolute values into account.
Try this formula:
=MAX(ABS(IFERROR(A1:A15,0)))
Team Runs for the day Team Runs for the day Leading Team
South Vizag 7 Coimbatore 1 12 Coimbatore 1
Tirupathi 2 Erode 11 Erode
Hosur 10 Guntur 23 Guntur
Nellore 10 Madurai 1 12 Madurai 1
Salem 8 Strategic Br-Hyderabad Main 3 Salem
In this above i have to compare two teams and should reflect highest team name?? how please suggest ???
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
Hi,
Product SKU Current Location Age
FNSF51LGRU30003 47
FNSF51LGRU30003 46
FNSF51LGRU30003 46
If i use the max if formula for the above data. The data is identifying the high value number in it for all the same SKU.
I need to freeze it if once the answer used.
For Example : 47 should occur only once. Next answer for the FNSF51LGRU30003 should be 46. Not the same 47.
Plz help me out.
Hi!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you MAXIFS formula
=MAXIFS(B1:B3,A1:A3,"FNSF51LGRU30003")
if three different numbers , make 2 sets of pair, find the minimum difference in that 2 sets of pair that should less than 15 and how to finalize the highest values minimum difference pair.
Hi!
To find the minimum difference, use the MIN function.
Article Material Desc BUn CC Code Base Rate
590000002 APPLE RED DELICIOUS (KG) KG AV53 145
590000003 APPLE GRANNY SMITH (KG) KG AM43 169.8
590000005 APPLE ROYAL GALA (KG) KG AV53 150
590000008 APPLE KINNOR (KG) KG AM42 126.4
590000008 APPLE KINNOR (KG) KG AM43 134
590000017 AVACADO (KG) KG AV53 240
590000024 PASSION FRUIT KG AV53 110
590000034 GRAPES IMPORTED (KG) KG AM42 366.3
590000036 GRAPES BANGALORE BLUE KG AK75 45
590000039 GRAPES FLAME SEEDLESS KG S577 77
590000042 GRAPES SONAKA SEEDLESS KG AK51 50.5
590000042 GRAPES SONAKA SEEDLESS KG AK74 51
590000042 GRAPES SONAKA SEEDLESS KG VX53 54
590000047 MANGO TOTAPURI KG AK74 125
590000047 MANGO TOTAPURI KG AM43 158
590000047 MANGO TOTAPURI KG SKAC 110
590000051 MUSK MELON (KG) KG AK74 20
590000051 MUSK MELON (KG) KG AM43 24.7
590000051 MUSK MELON (KG) KG SB81 22
How to highlight, each max value in E column, against data in A or B column
eg. For musk melon max value is 24.7, so only it has to highlighted..like that to do for each products
It would be of great help if I get solution for this
Hello!
To find the maximum value by condition, use the MAXIFS function. I recommend reading this article: MAXIFS function in Excel – find max value with multiple criteria.
For example,
=MAXIFS(E2:E20,B2:B20, "MUSK MELON")
I need to find the top 5 highest values from this set of data:
64.47
59.2
58.77
55.94
75.54
63.01
57.65
54.91
54.01
58.94
51.33
55.86
71.09
53.69
72.33
59.05
66.53
53.95
what formula should I use?
Hello!
The answer to your question can be found in this article: Excel LARGE function with formula examples to get n-th highest value.
Thanks, So helpful!!!