Sum top 4 scores in list of 7

Shane · Apr 9th, 2002, 05:26 AM

Hi! When I looked at this, I thought I could do it quickly but found that's it's more tricky.

I have a list of seven scores recorded in columns B through H against each of say 50 people. In column I, I want to sum the top 4 scores out of the seven scores recorded by each person.

Sorting the fields horizontally is not an option because each score must remain under the event in which it was recorded. Transposing the data for manipulation is also a no go as there are other formulae at work on this worksheet. I think the answer must lie in a formula (or combined formulae). Lastly, I need the solution to be flexible, say I want to take the top 5 scores from 8 events for example.

If you have any ideas, I'd appreciate your comments, thanks!

Dave Hawley · Apr 9th, 2002, 05:45 AM

Hi

In any cell put:

=Large($B$2:$H$100,A1)

Copy this down 4 rows. Now in A1:A4 put the numbers 1 to 4. In the cell below the LARGE function put your SUM formula.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-04-09 04:47 ]

Shane · Apr 9th, 2002, 05:54 AM

Classy Dave - many thanks!

Dave Patton · Apr 9th, 2002, 07:51 AM

=SUM(LARGE(B3:H3,{1,2,3,4}))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 05:26 AM	#1
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Hi! When I looked at this, I thought I could do it quickly but found that's it's more tricky. I have a list of seven scores recorded in columns B through H against each of say 50 people. In column I, I want to sum the top 4 scores out of the seven scores recorded by each person. Sorting the fields horizontally is not an option because each score must remain under the event in which it was recorded. Transposing the data for manipulation is also a no go as there are other formulae at work on this worksheet. I think the answer must lie in a formula (or combined formulae). Lastly, I need the solution to be flexible, say I want to take the top 5 scores from 8 events for example. If you have any ideas, I'd appreciate your comments, thanks!

Apr 9th, 2002, 05:45 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi In any cell put: =Large($B$2:$H$100,A1) Copy this down 4 rows. Now in A1:A4 put the numbers 1 to 4. In the cell below the LARGE function put your SUM formula. _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-04-09 04:47 ]

Apr 9th, 2002, 05:54 AM	#3
Shane Board Regular Join Date: Apr 2002 Location: Dublin, Ireland Posts: 51	Classy Dave - many thanks!

Apr 9th, 2002, 07:51 AM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	=SUM(LARGE(B3:H3,{1,2,3,4}))