Sif has attended:
Excel Advanced course
Finding multiple maximum/minimum values
Hi
I am working with a dataset where I have to find the maximum and minimum values for many different variables. Excel's max/min function only returns one value, but I have multiple max/min's.
I have tried doing this with the following array formula, but do not quite understand how it works:
={INDEX(_indicators!$A$2:$B$96,SMALL(IF(_indicators!$A$2:$A$96=_indicators!$A$97,ROW(_indicators!$A$2:$A$96)),ROW(1:1))-1,2)}
Is there a way to return multiple max/min values?
Thanks
RE: Finding multiple maximum/minimum values
Hi Sif
I've tested the formula by putting =Max(A2:A96) in cell A97. Entering the formula you give say in D2 returns the value in the B column next to the first Max value it finds.
Change A97 to Min(A2:A96) and it finds the value in B next for the first Min value.
If instead you use LARGE in the formula it finds the last value that matched with A97.
As it's an array formula you will need to press Ctrl+Shift+Enter after entering the formula in D2. That adds the curly brackets.
If you want to highlight all the Max or Min values you could consider using Conditional Formatting.
Hope it helps!
Doug Dunn
Best STL
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
