Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I add only the highest three numbers in a range of data?
I am using Excel to record, add and rank scores for a gymnastics meet. We
have teams of as many as 20 girls, but only the top three scores count towards a team score. How can I program a SUM function that adds only the highest three scores. |
#2
|
|||
|
|||
Hi!
=SUM(LARGE(A1:A20,{1,2,3})) Biff -----Original Message----- I am using Excel to record, add and rank scores for a gymnastics meet. We have teams of as many as 20 girls, but only the top three scores count towards a team score. How can I program a SUM function that adds only the highest three scores. . |
#3
|
|||
|
|||
You can use an array formula to total the top scores. For example, with
scores in cells B2:B100 -- =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3")))) To array enter the formula, hold the Ctrl and Shift keys, then press Enter. There's an example he http://www.contextures.com/xlFunctions01.html#Top5 emac_mommy wrote: I am using Excel to record, add and rank scores for a gymnastics meet. We have teams of as many as 20 girls, but only the top three scores count towards a team score. How can I program a SUM function that adds only the highest three scores. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
"Debra Dalgleish" wrote...
You can use an array formula to total the top scores. For example, with scores in cells B2:B100 -- =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3")))) To array enter the formula, hold the Ctrl and Shift keys, then press Enter. .... If the number of values to average is small (subjective, but 3 is definitely a small number), and if you're hardcoding the 2nd arg to LARGE anyway ("1:3"), why not use =SUM(LARGE(B2:B100,{1,2,3})) which doesn't have to be entered as an array formula? |
#5
|
|||
|
|||
Are you not receiving all posted messages Harlan?
This is exactly what Biff posted, as a suggestion in his original reply to this thread ... 2 days ago! That made me also wonder at Debra's not seeing Biff's post? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Harlan Grove" wrote in message ... "Debra Dalgleish" wrote... You can use an array formula to total the top scores. For example, with scores in cells B2:B100 -- =SUM(LARGE(B2:B100,ROW(INDIRECT("1:3")))) To array enter the formula, hold the Ctrl and Shift keys, then press Enter. ... If the number of values to average is small (subjective, but 3 is definitely a small number), and if you're hardcoding the 2nd arg to LARGE anyway ("1:3"), why not use =SUM(LARGE(B2:B100,{1,2,3})) which doesn't have to be entered as an array formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I check if data in an external data range is changed afte. | Excel Worksheet Functions | |||
How can I check if data in external data range is changed after re | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |