Average without Min Max and zero

[Glenn_2008]

Hi,

I've been using a sumproduct formula to get average without min and max value, but i can't figure out how also to exclude all cells in the same range where i have 0 as a value.

is the formula i use
=SUMPRODUCT((A14:G14<>MAX(P14:AA14))*(A14:G14<>MIN(A14:G14)),A14:G14)/SUMPRODUCT((A14:G14<>MAX(A14:G14))*(A14:G14<>MIN(A14:G14)))

Many thanks for your support.
Glenn

 

[Aladin Akyurek]

Hi,

I've been using a sumproduct formula to get average without min and max value, but i can't figure out how also to exclude all cells in the same range where i have 0 as a value.

is the formula i use
=SUMPRODUCT((A14:G14<>MAX(P14:AA14))*(A14:G14<>MIN(A14:G14)),A14:G14)/SUMPRODUCT((A14:G14<>MAX(A14:G14))*(A14:G14<>MIN(A14:G14)))

Many thanks for your support.
Glenn

Excluding 0 values is regarded as mathematically unsound when averaging negative and positive values. That said:

Control+shift+enter, not just enter:

=TRIMMEAN(IF(A14:G14,A14:G14),2/COUNT(1/A14:G14))

 

[Glenn_2008]

This worked, great!!


thanks a lot for your help