Finding multiple maximum/minimum values

Resolved · High Priority · Version 2010

Tue 9 Dec 2014, 10:21 Delegate Sif said...

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

Tue 9 Dec 2014, 16:40 Trainer Doug said...

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

Tue 23 Dec 2014, 15:58 Trainer Richard said...

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.

Tue 30 Dec 2014: Automatically marked as resolved.

Training courses