LinkBack URL
About LinkBacks
Hi,
I am using maxifs with multiple criteria. It's really convenient in order to find the highest value.
I wonder if there is any simple way to use maxifs to find the second highest value?
Thanks
Hi,
I am using maxifs with multiple criteria. It's really convenient in order to find the highest value.
I wonder if there is any simple way to use maxifs to find the second highest value?
Thanks
Please * if you like the answer
You need to use LARGE function
Formula:
=LARGE(A2:A11,2)
v A B 1 2 65 125 3 118 4 9 5 123 6 4 7 95 8 127 9 68 10 125 11 82
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It would be better to use AGGREGATE with LARGE
Formula:=AGGREGATE(14,6,A1:A10/(B1:B10="apple")/(C1:C10="banana"),2)
the above illustrates how to return 2nd highest value from A1:A10 where corresponding value in Col B is "apple", and Col C is "banana"
If you need more detailed assistance please provide your current MAXIFS calculation.
I really hope the sample file would make my question more clear.
Thank you
Please try at J6
=MAXIFS($F$2:$F$44,$B$2:$B$44,L6,$F$2:$F$44,"<"&K6)
Fantastic! Thanks for this great little formulaOriginally Posted by Bo_Ry
@Bo_Ry so elegant..thank you very much
Use this matrix formula
=IF(COUNTIF(B$2:B$44;L6)>0;LARGE(IF(B$2:B$44=L6;F$2:F$44);2);"")
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Or
For the lack of MAXIFS function you may use this regular formula:
Enter in J6 and copy down
Formula:=MAX(INDEX((B$2:B$44=L6)*(F$2:F$44<K6)*(F$2:F$44),0))
Thans guys, it's really good to know more than one way to solve a problem.. and here we got three.
@Alkey, I was surprised to see that the formula is not an array forumula.. so it's really simple for those who are not familiar with the CTRL+ALT+ENTER..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks