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

You could try this formula

=(SUM(A1:F1)-MIN(A1:F1))/(COUNT(A1:F1)-1)

"jatman" wrote:

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


"jatman" wrote:

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

=AVERAGE(IF(A1:F1<MIN(A1:F1),A1:F1))

ctrl+shift+enter, not just enter


"jatman" wrote:

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

Hello,

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

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

Regards,
Bernd