Find Multiple Maximums

[Guest]

My Problem:

I need to write a formula that will return the 'highest' number in a group
of ranked numbers.


Month Count Rank Required Unique Max # Sought
------- ---------------- -----------------
--------------------------
12 20 2
3
12 20 3
12 40 1
11 15 2
4
11 15 3
11 10 4
11 20 1
10 30 1
4
10 30 2
10 25 3
10 25 4

As per the example above, I need a formula that will return only one '3',
when referencing the group of 12's, and return only one '4' when referencing
the group of 11's, etc.

Many thanks for your assistance.

 

[olasa]

This will return the highest number in each group:
=LARGE(IF($A$2:$A$20=12,$C$2:$C$20),1)
12-->3, 11-->4, 10-->4

HTH
Ola Sandström


Note: This is an Array formula, so it Must be confirmed by holding down
Ctrl and Shift, and then hit Enter. Otherwise it will return #VALUE!