Return Variable Name if Max Value

[haleyac2008]

Hi I have data for 10 regions over several different populations. I want to return the name of the region for the 3 highest data points. I'm already using Large to find my 1st, 2nd, and 3rd highest values but now I need the region names. Below is an example of my data.


All Hispanic
Region 1 70 70
Region 2 78 77
Region 3 78 74
Region 4 73 76
Region 5 78 75
Region 6 78 77
Region 7 72 75
Region 8 78 76
Region 9 78 75
Region 10 85 74


High Performer 1:
High Performer 2:
High Performer 3:

Let me know if you need more explanation.

[Tony Valko]

How about showing us the results you expect.

Which column are you looking at for the 3 highest numbers? What about ties? A top 3 may have more than 3 elements to it depending on ties.

[haleyac2008]

Region 1 75
Region 2 74
Region 3 73
Region 4 70
Region 5 74
Region 6 71
Region 7 77
Region 8 70
Region 9 65
Region 10 61

So for this population, the high performers were 1st - Region 7; 2nd - Region 1; and 3rd - Regions 2 & 5. I'm thinking what I want to do is too complicated now since the 3rd had a tie.

If Region 2 and Region 10 both have the max -- I want to have them both listed -- and the large function takes care of that. I'm wondering if there is a way I can combine an if and large statement so that both can be listed but instead of listing values, I have the name of the Region. I've tried combining but I'm not having any luck.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F	G
1	Region	Value	------	------	------	------	------
2	Region 1	75		Top	3	Region	Value
3	Region 2	74		Count	4	Region 7	77
4	Region 3	73				Region 1	75
5	Region 4	70				Region 2	74
6	Region 5	74				Region 5	74
7	Region 6	71
8	Region 7	77
9	Region 8	70
10	Region 9	65
11	Region 10	61
12

Enter the top N number in E2.

Enter this formula in E3. This will return the count of how many records fall within the top N.

=COUNTIF(B2:B11,">="&LARGE(B2:B11,E2))

Enter this formula in G3:

=IF(ROWS(G$3:G3)>E$3,"",LARGE(B$2:B$11,ROWS(G$3:G3)))

Enter this array formula** in F3:

=IF(G3="","",INDEX(A:A,SMALL(IF(B$2:B$11=G3,ROW(B$2:B$11)),COUNTIF(G$3:G3,G3))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select F3:G3 and copy down until you get blanks.

[haleyac2008]

I'm getting stuck on the 2nd step: formula in G3. I decided to do conditional formatting to highlight the cell when it equals the 1st & 2nd highest values. My model has a whole lot more data than what I showed so I can't add any more columns... Thanks so much for helping!!!! This was my first post, and I'll definitely be coming back for help!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.