# Average without Min Max and zero

#### Glenn_2008

##### New Member
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)))

Glenn

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### MrExcel MVP
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)))

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

##### New Member
This worked, great!!

thanks a lot for your help 1,106,895
Messages
5,514,016
Members
408,982
Latest member
Cooksey19