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

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

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

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

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