averaging with conditions

T

tam25

A1 Height1 Height2 Height3 Height mean
B1 157.6 157.7 159 ?

I need a formula that will average the two closest figures and ignore the
third figure. eg Height 1 and Height 2 are closest therefore average these
two but ignore Height 3. It won't always be that height 1 and 2 are are the
closest. It could be that height 1 and 3 are closest or height 2 and 3 are
closest.

Help?
 
T

Teethless mama

=(IF(ABS(MEDIAN(F1:F3)-MIN(F1:F3))<ABS(MEDIAN(F1:F3)-MAX(F1:F3)),MIN(F1:F3),MAX(F1:F3))+MEDIAN(F1:F3))/2
 
T

T. Valko

What result would expect from these values:

157.6
157.7
157.8

157.65 or 157.75 ?

Biff
 
T

tam25

thanks for that, it doesn't quite work though. I had a figure of 94 and
94.4 and it returned 94.3 not 94.2. Would you know why?
 
T

T. Valko

Using TM's formula on:

94
94.4

I get the correct result: 94.2

This modified version will take care of numbers where the difference is
equal, like:

157.6
157.7
157.8

=IF(ROUND(ABS(MEDIAN(F1:F3)-MIN(F1:F3)),2)=ROUND(ABS(MEDIAN(F1:F3)-MAX(F1:F3)),2),AVERAGE(F1:F3),(IF(ABS(MEDIAN(F1:F3)-MIN(F1:F3))<ABS(MEDIAN(F1:F3)-MAX(F1:F3)),MIN(F1:F3),MAX(F1:F3))+MEDIAN(F1:F3))/2)

Biff
 
Top