Grouped numbers average

Hello again guys!

Please help me with another challenge I have.
I need to calculate the average of 3 numbers. Three numbers out of four. The forth number should be excluded from the average calculation by its 'distance' from the other 3.
For ex:
1 2 3 9 -> the average will have to be 2 (9 is excluded because it's too far from the 123 group)
2 12 14 16 -> the average will have to be 14 (2 is excluded because it's too far from the 12 13 14 group)

Or see the attached file.

Thank you!

Please try

=AVERAGEIFS(E5:H5,E5:H5,"<>"&AVERAGE(E5:H5)-MAX(ABS(E5:H5-AVERAGE(E5:H5))),E5:H5,"<>"&AVERAGE(E5:H5)+MAX(ABS(E5:H5-AVERAGE(E5:H5))))

or
=AVERAGEIFS(E5:H5,$E$4:$H$4,"<>*"&MATCH(MAX(ABS(E5:H5-AVERAGE(E5:H5))),ABS(E5:H5-AVERAGE(E5:H5)),))

Or:

Formula:

Please Login or Register  to view this content.

Thank you both guys for your quick reply!
The formula that best suits my desired results is Bo_Ry's second formula

Formula:

Please Login or Register  to view this content.

Unfortunately this formula uses the header range and I don't have such info. In the attach was there only for info.
Ive tried to replace that range with a static array like this:

Formula:

Please Login or Register  to view this content.

but no success (yes I used Ctrl+Shift+Enter)
Any ideea?
Thanks again!

AVERAGEIFS , COUNTIFS, SUMIFS cannot use data from array. All data range need to be from range.

You may try this.

=AVERAGEIFS(E5:H5,$E$4:$H$4,"<>"&INDEX($E$4:$H$4,MATCH(MAX(ABS(E5:H5-AVERAGE(E5:H5))),ABS(E5:H5-AVERAGE(E5:H5)),)))

Another option:

=SUM(LARGE(E5:H5,{1,2,3}+(MMULT({-1,1},MMULT(LARGE(E5:H5,{1,2;3,4}),{1;-1}))<0)))/3

Regards

@Bo_Ry unfortunatelly your formula has some error, it cannot be pasted ($E€3.39 ??)

@XOR LX thank you, that formula is beautiful, just one more thing:

In case the 4 values are homogenuous (0 50 0 50), Bo_Ry's formula takes into average the LAST 3 values (50 0 50) which is exactly what I want!
You think you can do that?
Thanks again!

Thank you Phuocam.
Your formula works fine but it returns the average of the first 3 numbers in case of homogeneity. I need the last 3 values.
As in
0 50 0 50 the average should be 33.33 and
50 0 50 0 the average should be 16.67

How about

=SUM(E5:H5*({1,2,3,4}<>MATCH(MAX(ABS(E5:H5-AVERAGE(E5:H5))),ABS(E5:H5-AVERAGE(E5:H5)),)))/3

Originally Posted by bulina2k

0 50 0 50 the average should be 33.33 and
50 0 50 0 the average should be 16.67

Why? Is it any extra rule about position in set.

Yes, it is an extra rule but these numbers are daily quantities and that's why I need the last 3 days average..
Thank you all very much guys!
You've been great! (again!!)