98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Finding multiple maximum/minimum values
Finding multiple maximum/minimum values
Resolved · High Priority · Version 2010
Tue 9 Dec 2014, 10:21Delegate 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:40Trainer 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:58Trainer 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 information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
