Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging | Excel Discussion (Misc queries) | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
Changing the range of several averaging functions | Excel Discussion (Misc queries) | |||
Conditional Averaging - 2 Conditions | Excel Discussion (Misc queries) |