Need some help on a formula please

[Bob Smith]

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

 

[Biff]

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 Phillips]

=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 Phillips]

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,COUNT(K5:K12))))))

still an array formula

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Biff, it worked very nicely. Thanks to Bob Phillips as well for his
input.