Average based on discrepancies.

[Allo]

Hi All!.

I'm trying to figure out formula to calculate average for 3 columns, if difference in between all 3 numbers is less than 10% (max is base), then I need average of all 3 numbers, if discrepancy is more than 10%, I need average of closest 2 numbers. Please help!

 

[BAlGaInTl]

Hi All!.

I'm trying to figure out formula to calculate average for 3 columns, if difference in between all 3 numbers is less than 10% (max is base), then I need average of all 3 numbers, if discrepancy is more than 10%, I need average of closest 2 numbers. Please help!

I "think" I understand what you are asking...

Can you provide some examples for the 3 numbers and what output you expect to get?

Will the numbers always be increasing/decreasing?

Do get the difference, do you want to subtract the smaller numbers from the max?

 

[Allo]

I.e. I have 267, 243 and 366. Correct answer is average of 267 and 243.

 

[Allo]

Will the numbers always be increasing/decreasing?

- Not necessarily.

Do get the difference, do you want to subtract the smaller numbers from the max?

As soon as difference between any of numbers exceeds 10%, I have to take two closest numbers.

 

[pgc01]

Hi
Welcome to the board

Assuming the numbers in A1:A3, try:

=IF(MIN(A1:A3)/MAX(A1:A3)>=90%,AVERAGE(A1:A3),AVERAGE(MEDIAN(A1:A3),IF(MEDIAN(A1:A3)<AVERAGE(A1:A3),MIN(A1:A3),MAX(A1:A3))))<?XML:NAMESPACE PREFIX = AVERAGE(A1 /><AVERAGE(A1:A3),MIN(A1:A3),MAX(A1:A3))))< p></AVERAGE(A1:A3),MIN(A1:A3),MAX(A1:A3))))<>

 

[Allo]

Thank you. But I think I'm missing something (end of the formula).

 

[pgc01]

Thank you. But I think I'm missing something (end of the formula).

Can you explain? Please give an example of 3 numbers for which the expected result is different from the the formula result.

 

[Allo]

Now it works! Somehow I managed to miss part of the formula. Thank you very much.