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:
Please Login or Register to view this content.
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:
Please Login or Register to view this content.
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)
@Bo_Ry so elegant..thank you very much
Use this matrix formula![]()
Please Login or Register to view this content.
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:
Please Login or Register to view this content.
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)
Bookmarks