Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help on a formula please
Cells K5 through K12 have 8 game scores. Best 6 scores are counted. I'm
trying to remove the two worst scores and do an average on the remaining six, and keep coming up with error messages. Last formula I tried that didn't work was =AVERAGE(K5:K12)-SUM(SMALL(K5:K12,{1,2})) Any help that you can provide is greatly appreciated :) TIA, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help on a formula please
Hi!
This assumes that there will be at least 6 scores and the "best" scores are the highest scores: =AVERAGE(LARGE(K5:K12,{1,2,3,4,5,6})) Biff "Bob Smith" wrote in message nk.net... Cells K5 through K12 have 8 game scores. Best 6 scores are counted. I'm trying to remove the two worst scores and do an average on the remaining six, and keep coming up with error messages. Last formula I tried that didn't work was =AVERAGE(K5:K12)-SUM(SMALL(K5:K12,{1,2})) Any help that you can provide is greatly appreciated :) TIA, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help on a formula please
=AVERAGE(IF(K5:K12=LARGE(K5:K12,{1,2,3,4,5,6}),K5: K12))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bob Smith" wrote in message nk.net... Cells K5 through K12 have 8 game scores. Best 6 scores are counted. I'm trying to remove the two worst scores and do an average on the remaining six, and keep coming up with error messages. Last formula I tried that didn't work was =AVERAGE(K5:K12)-SUM(SMALL(K5:K12,{1,2})) Any help that you can provide is greatly appreciated :) TIA, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help on a formula please
Mine was a bit of overkill as Biff showed. If there may be less than 6
values, use =AVERAGE(LARGE(K5:K12,ROW(INDIRECT("1:"&MIN(6,COUN T(K5:K12)))))) still an array formula -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... =AVERAGE(IF(K5:K12=LARGE(K5:K12,{1,2,3,4,5,6}),K5: K12)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Bob Smith" wrote in message nk.net... Cells K5 through K12 have 8 game scores. Best 6 scores are counted. I'm trying to remove the two worst scores and do an average on the remaining six, and keep coming up with error messages. Last formula I tried that didn't work was =AVERAGE(K5:K12)-SUM(SMALL(K5:K12,{1,2})) Any help that you can provide is greatly appreciated :) TIA, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some help on a formula please
Thanks Biff, it worked very nicely. Thanks to Bob Phillips as well for his
input. "Biff" wrote: Hi! This assumes that there will be at least 6 scores and the "best" scores are the highest scores: =AVERAGE(LARGE(K5:K12,{1,2,3,4,5,6})) Biff "Bob Smith" wrote in message nk.net... Cells K5 through K12 have 8 game scores. Best 6 scores are counted. I'm trying to remove the two worst scores and do an average on the remaining six, and keep coming up with error messages. Last formula I tried that didn't work was =AVERAGE(K5:K12)-SUM(SMALL(K5:K12,{1,2})) Any help that you can provide is greatly appreciated :) TIA, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |