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

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?
0
gildoron (1)
8/11/2003 5:23:07 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
572 Views

Similar Articles

[PageSpeed] 31

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

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

>-----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?
>.
>
0
jason.morin (561)
8/11/2003 7:18:28 PM
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))

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

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



"Gil Doron" <gildoron@yahoo.com> wrote in message
news:a997536f.0308110923.288be6a7@posting.google.com...
> 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?


0
ken.wright (2489)
8/11/2003 7:22:12 PM
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.

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

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



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:u6YEf3DYDHA.1056@TK2MSFTNGP10.phx.gbl...
> 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))
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
>
> ----------------------------------------------------------------------------
>   Attitude - A little thing that makes a BIG difference
> ----------------------------------------------------------------------------
>
>
>
> "Gil Doron" <gildoron@yahoo.com> wrote in message
> news:a997536f.0308110923.288be6a7@posting.google.com...
> > 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?
>
>


0
ken.wright (2489)
8/11/2003 8:22:27 PM
Reply:

Similar Artilces:

Automatically copy formula in column when adding new row....
Hello all. Sorry if I am posting this question in the wrong category, but this is my first post here. I am using Excel 2007, and I am trying to automatically copy a formula that's in a column when I add a new row. I'll explain a little more in detail what I am hoping to achieve. My spreadsheet is intended to show profit and loss for my sales. Columns A:W all have details specific to the products that I am selling, including original purchase price, sale price, shipping, fees, etc... In Column X, I have this formula " =SUM(Q4:W4)*L4 ", and then in Column ...

display a notification when new mail arrives #2
Hi Not sure if this is an Outlook or a Windows question! But here goes. Until a short while ago, I would get an 'unopened envelope' icon in the systray when new mail arrived, but now its gone.... Don't think I've done anything to change this - but it was really useful, so can anyone tell me how to put it back, thanks Outlook 2003: 1) Select Options from the Tools menu (in Outlook) 2) Click [E-mail Options] 3) Click [Advanced E-mail Options] 4) Check "Show an envelope icon in the notification area. -- Let me know if this helps! Bay Area DebG "Jeff" ...

1920x1080 how some programs (non-menu area) fonts are displayed
Moving from the 1280x1024 for standard LCD 19" etc screens resolution, to the 1920x1080 which comes with 23" etc WideScreen monitors these days, we know the same settings of font size displayed we were use to, will now show slightly smaller - and that's fine. First, yes we understand about font size, clear type, and dpi adjustments. Yes we know for higher resolutions being sure to turn on and adjust ClearType which solves readability problems just fine across the board in every nook and cranny, Except, inside many non-microsoft programs where the OS's control over t...

Can no longer display payment address/info for epay transactions
Ya gotta love how things inexplicably stop working in Money, Money 2007 in my case. Suddenly, I can no longer display the payment address for any of the epay transactions in my checking account register. When I select a transaction and either: 1. Press the Options button and then select "Edit Address: <Payee>" 2. Press the "Pay to:" hyperlink nothing happens. This is having a nasty side-effect: If I enter a new epay check (i.e., a withdrawal) to a new payee, I can't submit the transaction. I'm no longer prompted to enter the address and account number.....

error messages 03-03-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: pop Was syncing entourage with mailme when i got an error message. I clicked it off and they kept coming...at one time I had 20 on the screen. I had to close the program and then turn off my Macbook Pro, then turn it on. I cannot use Entourage without this happening...opened it and one popped right up. On 2010-03-02 22:09:33 -0500, padreruf@officeformac.com said: > Was syncing entourage with mailme when i got an error message. I > clicked it off and they kept coming...at one time I had 20 on the &...

What is the Excel formula for adding 1 column with several rows?
I need to the sum of one column - several rows. Example: Column J rows 3 thru 71 without entering each column and row number. =SUM(J3:J71) Gord Dibben MS Excel MVP On Mon, 21 Aug 2006 18:50:51 -0700, ljsbjs <ljsbjs@discussions.microsoft.com> wrote: >I need to the sum of one column - several rows. Example: Column J rows 3 thru >71 without entering each column and row number. And a variation that allows you to insert rows immediately before the formula. J72: =SUM(J$3:OFFSET(J72,-1,0)) see http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Mic...

moving every other row up 1 row to the next column
I need to move every other row to match with the one above it but place it the info in the next column. Column A Column B Description F8430566 PREMARK 24'' WH LN + 15' F8431064 PREMARK 12'' WH LINE PLUS 30' What I need is: F8432060SP4 PREMARK 4''X4' STRIPS WH 3000' Try something like the "Vertical to horizontal..." here: http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "Rose Davis" <rdavis@lkfinc.com> wrote in messag...

how does one plot a line-column on two axes in excel?
How does one use Excel 2007 to plot a line and a bar graph with two axes - the same chhart offered in Excel 2003 under Custom Types? Hi, You have to build the combination chart yourself. Create a column chart based on both data sets. Select the column you want as a line and use Change Chart Type to set it to line chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "gbrunnhoeffer" <gbrunnhoeffer@discussions.microsoft.com> wrote in message news:12DE504E-6948-4925-893E-0E737751B46F@microsoft.com... > How does one use Excel 2007 to plot a lin...

Outlook Contacts Displaying Incorrectly
Hi, I synchronise contacts with Outlook and my mobile phone (Nokia 6230). When a contact comes from the phone it displays as Smith, John in the Outlook list, however when I open the contact and try to edit it says display as John Smith. There must be another setting that is overriding this somewhere! Any ideas? Hi Shaun, By default, Outlook 2003 displays contact names that are in your Contacts list in the FirstName, LastName format, as opposed to the LastName, FirstName format. In this situation, the first name of a contact is used to sort your Contact lists because the Contacts lis...

Need to calculate time between 2 dates and display Conditional formatting
I have some Excel cells with dates formatted like this Thursday, June 2004 8:53 AM. I need the difference in time (Days Hours Minutes between the 2 cells containing the dates. What would the formula be? I was trying this =DATEDIF('Level 2 Support Team Member'!B7,'Level 2 Support Tea Member'!B35,"md") but I kept getting a #Value error B35 and b7 are merged with cells t the right for display, but those are the cells that contain the actua values. I wanted to Conditionally format the result so that if the result wa greater than 1 day or 24 hours that the font wou...

Outlook Reminders #10
Hi Everyone, Hope this question has not been answered before. I have transfered all my business data from SBS 2000 to SBS 2003. I created all the user accounts from scratch on the new server using the same usernames as the old server. I then restored all their email to Exchange 2003 using Veritas Backup Exec 10.1 Ever since then no reminders have came up in Outlook on any PC when the user logs in and runs Outlook. We run various version of Outlook (2000 / 2002 / 2003) all with the same problem. When I log in via Outlook web access the reminders appear so I suspect it is a Outlook t...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Form will not display records
In Access 2007, one of our users has asked me to research the following, She built one form, using fields from 5 tables, each table has a field, Item_ID, it is the Primary key in all 5 tables, and a 1-1 inner join relates the 5 tables. She then created the form, using the fields from the tables. Data entry works perfectly, and the tables get populated, but when closing the application and then restarting it, the data does not display in the form, although it is still in the tables. What could cause this behavior? What is the SQL feeding the form? -- Build a little, test a ...

Macro with "relative" range for Average function, or... 'is there a real keystroke recording macro generator?
I often have columns of numbers, with a label, that I need to average (or get the standard deviation, etc.). The column length varies, and I would like to not worry about having a constant number of rows between the cell I am in when I run the macro and the cell with the label. Manually, I use =average( end, down arrow ' gets me to the label down arrow ' first data cell control shift down ' last data cell Enter ' completes entry, calculates average This works fine for other columns of the same length, but wit...

Adding Industry Average Line
So I have data; X Y Plant 1 Y1 Plant 2 Y2 .. .. Plant N YN Company Avg YAvg Industry Avg YIA (YAvg is a calc I do, YIA is plugged) Now. I have a column chart where there's a column for Y1, Y2, etc. etc. I'd like columns for Y1 - YN. But I'd like a horizontal line for YAvg and YIA. I know I can "trick" it if I do a combo graph with separate columns of data: X Y Co Ind Plant 1 Y1 Yavg YIA Plant 2 Y2 Yavg YIA .. .. P...

###### appearing even though the cell is large enough to display everything
I have a cell with text in it and on my screen it appears as a series of pound signs. I have made the cell large enough that all the text should fit so I do not know why the pound signs will not disappear. Format the cell to General. Excel has a problem with cells that contain >255 but <1024 characters. Gord Dibben MS Excel MVP On 26 Apr 2006 12:55:14 -0700, jmelaragno@gmail.com wrote: >I have a cell with text in it and on my screen it appears as a series >of pound signs. I have made the cell large enough that all the text >should fit so I do not know why the pound sign...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Unable to display default folder in cached mode
Hello, I have installed and configured Exchange 2003/Outlook 2003 clients. Some of them, after creating the Outlook profile, displays "Unable to display your default folder" message on Outlook stratup and shuts down Outlook. When I turn off the "Use cached mode", it works fine. I have tried it on other computers/other users profile and still have some 3-4 user mailboxes with this issue. There is Novell client installed on computers, but other 60+ users are fine. Any help would be appreciated. Peter Hornak Peter Do the users having problems have a local or remot...

Autofilter for fixed columns
Hi, I have a big table from column A to BP, though the number of column is not fixed. Sometimes it has additonal one or 2 extra columns append at the back, so it could reach until column BR. Regardless of how many columns I have in this table, I want to have a macro that could turn on autofilter, filter column J for a value e.g. "John, C", and then filter column AC and R for non-blank cells. I tried the following, but it doesn't work. ..Columns("J:J").AutoFilter field:=1, Criteria1:="John, C" ..Columns("AC:AC").AutoFilter field:=1...

Column spacing
Is there any way I can control the spacing between the columns in the layout guides (chosen from the Arrange menu)? I want to set 0.7 cm column spacing for a text frame split into 3 columns and see the guides for this setting so that I can then use individual text frames on a second page. I know I can set the column spacing in the text frame formatting option but this is not reflected in the layout guides. Please can anyone help? I am using Publisher 2000. I have this work around - I make my own ruler. Make pull a rectangle, looking at the "size" indication in the lower r...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Simple 3D Column
I am currently getting my blood tested every two weeks and have made a simple 3d column chart with the data. This shows the results well. However i would like a red line showing the maximum and minimum levels that would show the "Normal" parameters of the blood. For instance the column for this week rises to 11.5 and the top and bottom (Normal) parameters are 13.5 to 18 so I would like a line at 13.5 and one at 18 with my column of 11.5 over the lines. Hope this is clear as this is one of my first attempts at charting. -- thepict@albaweb --------------------------------------...

compare two columns and display a third
here's the challenge: -I need to compare 2 columns: A1:A10 and B1:B20 (different range) -For each common value I would like to display the information contained in a third column C1:C20 I did some research on this forum and http://www.cpearson.com/ but so far no good. any help will be greatly appreciate to avoid doing it manually 2000 times. chris90 =COUNTIF(A1:A10,B1) in C1 gives 1 if B1.value occurs in A1:A10, 0 if not. Fill down to C10! or =COUNTIF(A1:A10,B1) > 0 gives TRUE or FALSE respectively. Regards, Stefi "ch90" wrote: > here's the challenge: > -I...

displaying negative values
how do I hide the display of negative numbers? =if(A1<0,"",A1) HTH Regards from Brazil Marcelo "cfuller" escreveu: > how do I hide the display of negative numbers? Hi, That will work if cell A1 has a formula in it. If negative numbers are just typed in, you can use conditional formatting. Highlight the range of cells you want to format. Go to Format > Conditional Formatting... Enter cell value is less than 0. Click the Format... button and select Color: and then click the white icon. HTH -- Ken Hudson "Marcelo" wrote: > =if(A1<0,"&qu...

running 9 and 10 on same mahine
We are currently running version 9 using SQL 2005 and Windows server 2003. It is possible to install and run version 10 on the same machine? If so, do I need a separate instance of SQL 2005? Both versions are being used in a teaching environment so the normal caveats about mission critical systems does not apply. Thanks. Dr. Jerry Flatto University of Indianapolis Indianapolis, IN, USA 2007 Excellence in Education Pinnacle winner On Jul 31, 12:48 pm, Jerry Flatto <JerryFla...@discussions.microsoft.com> wrote: > We are currently running version 9 using SQL 2005 and Windows ser...