averaging with conditions
 

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?

averaging with conditions
 

=(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


"tam25" wrote:

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?

averaging with conditions
 

What result would expect from these values:

157.6
157.7
157.8

157.65 or 157.75 ?

Biff

"tam25" wrote in message
...
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?

averaging with conditions
 

157.7

"T. Valko" wrote:

What result would expect from these values:

157.6
157.7
157.8

157.65 or 157.75 ?

Biff

"tam25" wrote in message
...
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?

averaging with conditions
 

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?

"Teethless mama" wrote:

=(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


"tam25" wrote:

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?

averaging with conditions
 

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

"tam25" wrote in message
...

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?

"Teethless mama" wrote:

=(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


"tam25" wrote:

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?