Calculating averages (olympic style)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

good morning,

i have a worksheet and i need to calculate averages with a catch. i know
how to do basic averages and stuff. i need a formula to calculate the
following example:

data is in row 1. column a, b, c, d, e and f all have values entered in
them. i need the formula to drop the lowest value and give an average for
the remaining 5 only.

thank you,

jat jaswal
 
If there will ALWAYS be at least 5 numbers in A1:H1
Then....try this:
=AVERAGE(INDEX(LARGE(A1:F1,{1,2,3,4,5}),0))

Otherwise:
=SUM(A1:F1,-MIN(A1:F1)*(COUNT(A1:F1)>5))/(MIN(COUNT(A1:F1),5))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Hello,

Sorry, that does not work if min value appears more than once.

I suggest
=(SUM(A1:F1)-MIN(A1:F1))/4

Regards,
Bernd
 
Back
Top