Retrieving the Top 10 of the Average of Two columns, but displaying a third

  • Thread starter Thread starter Gil Doron
  • Start date Start date
G

Gil Doron

I'm new to Excel programing, so please bear with me.

I have an excel spreadsheet with 3 columns.

Column1 Column2 Column3
--------------------------------------
John Doe 5 3
Jane Smith 2 9
Bill Smith 4 2


What I need to do is take the average of Column 2 and 3, find the
highest average of the entire list and display Column 1 as the final
result.

In other words: I need to create a Top 5 List of the people with the
highest grade average. I don't care much for displaying the grade, I
just need the top 5 names.

Can this be done with an excel formula or do I need to write a macro?
 
You could do an advanced filter.

1. Create another column (say D) that averages each row of
columns B and C (assuming col 2 & 3) using:

=AVERAGE(B2:C2)

2. In an open cell (say G100) outside the data put:

=SUM(--(D2=LARGE(OFFSET($D$1,1,,COUNT(D:D)),{1,2,3,4,5})))
0
Click to expand...

3. Click somewhere inside your data and go to Data >
Filter > Advanced Filter.

4. In criteria range select G99:G100, ensuring G99 is
empty.

HTH
Jason
Atlanta, GA
 
Thought I had it with the following, but it bombs if two people or more in the top 5 have the same
average and gives the same name twice or more. If they are all different though it's fine (But I
hate the fact it breaks, so I'll keep playing):-

With your data in A1:C100, select a vertical range of 5 cells anywhere outside the A1:C100 range
and array enter the following (CTRL+SHIFT+ENTER):-

=INDEX($A$1:$A$100,MATCH(LARGE(($B$1:$B$100+$C$1:$C$100)/2,{1;2;3;4;5}),($B$1:$B$100+$C$1:$C$100)/
2,0))
 
OK, my dimwitted brain has given up tonight - Just can't get past the duplicates issue and still
contain it all in a few cells.

Kludgy I'm afraid, but I need 3 helper columns that can be hidden or fonts changed to white, or
dragged off to the right of the spreadsheet somewhere.

Your data in A1:C100

In cell D1 put the following formula and copy down:-

=AVERAGE(B1:C1)

In cell E1 put the following formula and copy down:-

=IF(RANK(D1,$D$1:$D$100)<6,RANK(D1,$D$1:$D$100),"")

In cell F1 put the following formula and copy down:-

=IF(ISERROR((COUNTIF($E$1:$E1,E1)-1+E1)),"",(COUNTIF($E$1:$E1,E1)-1+E1))


Now select cells H1:H100 and enter the following formula by array entering it (CTRL+SHIFT+ENTER):-

=IF(ISERROR(INDEX($A$1:$A$100,MATCH(ROW(INDIRECT("1:"&COUNT(E1:E100))),$F$1:$F$100,0))),"",INDEX($
A$1:$A$100,MATCH(ROW(INDIRECT("1:"&COUNT(E1:E100))),$F$1:$F$100,0)))

That will display all the people that were ranked in the top 5 in the average listing. They will
appear in Rank order, but for any duplicate rankings, they will appear in the list in the order
they appear on the sheet in your range A1:A100. If the rankings are as follows:-

1
2
3
4
5

then you will get the top 5 names listed, but if the ramkings are as follows, which is what will
happen with any duplicate averages:-

1
2
2
4
4
4
4
4

then you will get each of their names in a list. The length of the list will adjust automatically
depending on duplicates.

If you would like a copy of the workbook then just holler.
 
Back
Top