Ignore two lowest numbers in a range

[Just_Joe]

I am setting up a wookbook that keeps score and average score totals. I need to be able to ingone the two lowest scores (numbers) in a column when summing total and caculating the average.

Any ideas?

Thanks

Just_Joe

[daddylonglegs]

Do you know how many scores there will be? If not you can try this for the sum, assuming numbers are in A1:A100

=SUM(A1:A100)-SUM(SMALL(A1:A100,{1,2}))

then, if that formula is in C2 you can get the average with

=C2/(COUNT(A1:A100)-2)

[Just_Joe]

Awsome! Numbers are actually in J2-J37, so I used this

=SUM(J2:J37)-SUM(SMALL(J2:J37,{1,2}))

which totals in J38, so I put this in J39

=J38/(COUNT(J2:J37)-2)

Perfect!

Is this the best to use for calculating an average =AVERAGE(J2:J37)?

Thanks

[daddylonglegs]

If you want an average of all numbers then just

=AVERAGE(J2:J37)

would be the normal way, yes.

Obviously I didn't use AVERAGE above but that was just because

1) you already have a SUM and you only then need to divide by the COUNT
2) The complexity of that approach twinned with the requirement to drop the lowest 2 numbers

You could get an AVERAGE without the two smallest numbers like this

=AVERAGE(SMALL(J2:J37,ROW(INDIRECT("3:"&COUNT(J2:J37)))))

confirmed with CTRL+SHIFT+ENTER

[Just_Joe]

Excellent this is working out perfect. Thanks again for your assistance!

Just_Joe