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. Continue reading
by Svetlana Cheusheva, updated on
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. Continue reading
Comments page 3. Total comments: 135
im trying to find the best way to write a function that will take the max of a range of cells and display the row header associated with that cell... for example take the Max of cells A1:a17 and display the row header of whichever value is produced from the max function. any input?
Hi!
Your question is not entirely clear. What row title do you want to show? What cells is the row header in?
Is it possible to do an index match lookup between 2 tables and also find the highest value? The index match + max formulas I am finding all seem to be for within a single table.
For example, if you have a list of cities and hotel prices at different times of the year in one table.
Then in another table, you have a list of cities, with columns for different values and you want to pull in the highest hotel price from the other table. Is that possible with any kind of lookup?
Hello!
I recommend reading this guide: VLOOKUP across multiple sheets in Excel with examples.
I hope my advice will help you solve your task.
Hello,
I have tried my best on my own forever. I am trying to get the largest sum of 5 numbers, however, based on a second column, the corresponding total cannot exceed a number. Example, for the below, I would like to have the left column total the largest of the 5 added up, but cannot exceed $135 based on the column to it's right. I have gotten close, but it will give me a number but don't actually know which 5 it has chosen! Ha. So best case scenario, it will highlight or say A, D, H or whatever are the five that total the most without going over $135 summed on the right column. Thanks for any help and I hope that makes sense.
A 24.91 $37
B 19.47 $23
C 18.26 $38
So On 17.27 $34
13.48 $22
12.37 $18
11.45 $20
10.68 $18
9.49 $17
9.34 $18
8.99 $16
6.25 $10
6.15 $21
5.38 $10
4.28 $10
I have a customer list with three columns representing stage1, stage2 and stage3. some customers have balance only in one stage while others have balance in two stages. I want to find out the stage with maximum balance for each customer. how to find the same.
Customer STAGE1 STAGE2 STAGE3 Result I want
ABC 100 400 STAGE3
DEF 3000 2000 STAGE1
MNO 5000 50 STAGE2
KPL 250 STAGE1
GHI -250 STAGE2
How can I find the result with a formula for larger amount of data
Hello!
The formula below will do the trick for you:
=INDEX($B$1:$D$1,MATCH(MAX(B2:D2),B2:D2,0))
You can learn more about searching with INDEX + MATCH in Excel in this article on our blog.
I have 2 columns of values, i have conditionally formatted each to show top 20%. I want ones that are in the top 20% of each category to be highlighted in another colour. e.g top 20% in column A Green, top 20% in column B Green. In top 20% in A and B Blue.
Any assistance is appreciated
Thanks,
Luke
Hello!
Here is the article that may be helpful to you: How to change background color in Excel based on cell value.
After I find the MAX value, do I use a CONCATENATE formula to show which is the highest value? Any suggestions are welcome. Thank you.
Hello!
You can insert the maximum value returned by the MAX function into a text string using the CONCATENATE function:
=CONCATENATE("Maximum value ",MAX(A2:A20))
i have in
column A: 34,34,34,33,33,33,35,35.
column B: 3,2,1,5,4,3,2,2,1
what function i can use to gate max value of B compare to A: 34,33,35 for each
like in column C: 3,nil,nil,5,nil,nil,2,nil.
Hello!
Use the MAXIFS function:
=IF(B2<>MAXIFS($B$2:$B$9,$A$2:$A$9,A2),0,MAXIFS($B$2:$B$9,$A$2:$A$9,A2))
This should solve your task.
I have been trying the formula to get headers to find maximum value in rows which goes as =INDEX($P1:$R1,0,MATCH(MAX($P2:$R2),$P2:$R2,0))
I drag the formula through the column. It takes the 2nd row values as headers. The formula seems as =INDEX($P2:$R2,0,MATCH(MAX($P3:$R3),$P3:$R3,0)). It returns the 2nd row maximum values in the consecutive cells but not headers for the maximum value.
How can I fix this issue that the header reference formulas do not change in the formula?
Hello im trying to find the best formula for this data. I feel it should be FILTER but not sure which one to use.
I need to filter the result in column D with the condition as
amount = highest value in Column C
data limited to column B of Company XYZ
expected result should be: A9100 amount$3,428.52
please help.. I'm not expert in excel's formula. thanks in advance..!!
A B C D
Company XYZ AC1000000454 $3.59 A1019
Company XYZ AC1000000454 $67.91 AR400
Company XYZ AC1000000454 $3,428.52 A9100
Company XYZ AC1000000454 ($353.20) AF400
Company XYZ AC1000000454 ($33.00) AHF00
Company XYZ AC1000000454 ($4,256.00) A1044
Hi,
Could you please help me out on how to calculate max time from an array of 3 time stamps of 3 systems placed horizontal in range? How can I get the system names too after I try the formula or it can directly show system names that has highest time? How can I use maxifs with criteria range?
System 1, 00:30:23
System 2, 00:57:20
System 3, 00:11:05
Hello!
If I understand your task correctly, the following formula should work for you:
=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))
Hi,
The 3 time stamps are placed horizontal on a row.
A. B. C
00:30:23. 00:57:20 00:11:05
How can I get max value from the columns A,B,C with names system 1, system2 and systems 3 respectively in formula? Please help me out on this.
Hi!
Have you tried the ways described in this blog post? If they don’t work for you, write what formula you used and what were the problems.
How can I get any of the three systems names whichever with maximum value on a single column with respect to 50 rows for 3 systems?
Hi,
I have tried with index, match, max functions.
If I have headers : System1, System2, System3 in Column O, P, Q with 00:01:57, 00:04:00, 00:00:35 timestamps respectively.
And there is a list of 50 timestamps under each.
I want to find out which system has maximum timestamp value.
I tried the formula, on 2nd row:
INDEX($O1:$Q1,0,MATCH(MAX($O2:$Q2),$O2:$Q2,0))
Problem1 : I don’t get the true system header name with true maximum value.
Problem 2: the formula doesn’t work on 3rd row when I try to drag the formula for next 50 rows.
Please suggest.
Hello!
If I got you right, the formula below will help you with your task:
=INDIRECT(ADDRESS(1,MIN(IF(O2:Q51=MAX(O2:Q51),COLUMN(O1:Q1)))))
This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
I hope it’ll be helpful.
Hi
Am working with data where I have to get the date of Highest Quantity with matching Product No.
Maxif formula is not working with.
Kindly suggest the way out.
Product Qty Date
GHI 102 30-Aug
DEF 77 30-Aug
ABC 21 30-Aug
ABC 10 30-Aug
GHI 119 20-May
GHI 106 20-May
GHI 94 20-May
DEF 81 20-May
DEF 69 20-May
DEF 56 20-May
ABC 44 20-May
ABC 36 20-May
ABC 10 20-May
GHI 114 10-May
DEF 90 10-May
DEF 65 10-May
ABC 40 10-May
ABC 20 10-May
GHI 110 01-May
DEF 85 01-May
DEF 61 01-May
ABC 35 01-May
GHI 123 30-Apr
GHI 98 10-Apr
DEF 73 10-Apr
ABC 48 10-Apr
ABC 30 10-Apr
Result should be - ABC - 10-Apr (ABC highest Qty is 48 so 10-Apr)
Hello!
Specify which formula you are using. Also, check if your date is written as text.
Hi
Date format is correct.
Have tried {=MAX(IF($B$3:$B$29=$G6,$D$3:$D$29))}
This gives me latest date against ABC which is 30-Aug.
I would like to have result 10-Apr because ABC has 48 (highest Value)
Hello!
I believe the following formula will help you solve your task:
=FILTER($D$3:$D$29,($B$3:$B$29=$G$6)*($C$3:$C$29=MAXIFS($C$3:$C$29,$B$3:$B$29,$G$6)))
You can learn more about FILTER function in Excel in this article on our blog.
Hello,
Does anyone know how to apply this function with data that is not in a consecutive range?
Instead of a range like B5:G5, my data is B5,D5,F5,
Can anyone help?
Thanks!
Hi!
You can use this formula:
=MAX(B5,D5,F5)
Hi,
I have a data sheet that I need to extract information from, in one column I have the NAMES of persons, in another I have either "yes" or "no" answers for each person.
I'd like to extract everyone's name who answered "yes" into either one cell, separated by commas, or each into a vertical list.
Hello!
I recommend reading this guide: Excel FILTER function - dynamic filtering with formulas.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi, I am involved in stock trading and i have my trading records in Excel; ie bunch of profits and losses.
My question is, how do i find the lowest and highest profit and loss that i make in my records?
=MAX(IFERROR(P23:P79, "")) + CTRL + Shift + Enter only return highest profit, while
=MIN(IFERROR(P23:P79, "")) + CTRL + Shift + Enter returns highest loss.
How do i find lowest profit and lowest loss? Where can i fit the criteria "0" ?
Do note that i have multiple blanks (intentionally left) and #DIV/0! (formula i keyed in in advance for future trading but i havent filled in respective fields)
Thank you in advance. God bless and have a nice day.
Hello!
To find the lowest loss, use the formula
=MAXIFS(A1:A10,A1:A10," < 0")
Minimum profit -
=MINIFS(A1:A10,A1:A10," > 0")
Please have a look at this article: MINIFS function in Microsoft Excel and MAXIFS function in Excel.
Hello, I'm trying to get the maximum error to show up but some times the maximum error is a negative (so -5 is a greater error than +3 etc) Any ideas?
Also the 2 cells I'm comparing arent next to each other (just to add to the fun).
hello
Can I get help to write a formula that will highlight the lowest price for an article that repeats in several rows. If that article only shows once then that price would be highlighted too. for example only the first line would be highlighted.
article 1 300
article 1 548
article 1 2500
Thank you for your help
Hello!
I recommend using the FILTER function to find the article you are looking for. Find the minimum price using the MAXIFS function.
=FILTER(A1:B8,(A1:A8="article 1")*(B1:B8=MINIFS(B1:B8,A1:A8,A1:A8)))
I hope my advice will help you solve your task.
I'm struggling with this a bit.
My challenge is that I've got a list of results in a column, and want to identify the largest value in that column and then add a bonus prize in the next column for the largest row value.
I would describe it BADLY as:
in the column next to the data: +IF("the data in this cell is greater than the cells in rh column, give me A point" otherwise 0)
Does that make sense?
thanks
Hello!
In the next column, you can write the formula
=IF(MAX($A$1:$A$30)=A1,100,"")
After that you can copy this formula down along the column.
Hope this is what you need.
I am trying to find a formula that returns for example to find which State had the highest sales for a specific month. Column A is the State and Column B is the sales for the specific month. Does anyone know a formula for this? Thanks.
Hello!
The formula below will do the trick for you:
=INDEX(A1:A50,MATCH(MAX(B1:B50),B1:B50,0))
Here is the article that may be helpful to you: INDEX function in Excel
Well if my range is - A2:D10 and I need to find the highest value with the name the person
Thanks it worked.
Hi, is it possible to create a formula to find the max value if some of the cells contain ranges please?
i.e. working out that the max value is 505 from the following cells:
Cell 1. 490
Cell 2. 500,
Cell 3. 490-505
Dear Sir
can you help me to do a formula to find highest value of 1st numbers letters
This is excellent, thanks for saving me a ton of time!
which one of the following functions is designed to display the maximum value in a range of cells with question answer
THANK YOU!!! Do you know how complicated I have been making this for years?! And it was as simple as =Max!!!
You have made my day!
How to find max, 2nd, 3rd & 4th latest dates by name in another sheet?
Results e;
Names in column A
Dates in column B
Distance in column C
& so on
Regards
Tony
Well nd easy