# 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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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))

This worked, great!!

thanks a lot for your help

Replies
2
Views
184
Replies
0
Views
249
Replies
0
Views
269
Replies
1
Views
377
Replies
7
Views
381

1,214,748
Messages
6,121,301
Members
449,022
Latest member
benmerryman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back