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?

It could be done with a large and complicated formula, but if you are willing to use a helper
column to calculate the average for each person, the final formula will be simpler. With
=AVERAGE(B2:C2) in column D,

For the highest: =INDEX(A2:A30,MATCH(MAX(D2:D30),D2:D30,0))
For 2nd, assuming no ties =INDEX(A2:A30,MATCH(LARGE(D2:D30,2),D2:D30,0))

Change the 2 in the LARGE formula to 3, 4, then 5 for the rest of the values.

You can hide column D if you wish.



On 11 Aug 2003 14:58:33 -0700, (Gil Doron) wrote:

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?

Hi Gil,

You can use the AVERAGE formula in column4. For example,
cell D1 would be "=AVERAGE(B1:C1)". Then just use the
autofilter feature to display the top 5.

Hope this helps...

Chong Moua

-----Original Message-----
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?
.

See response in .misc

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Gil Doron" wrote in message
om...
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?