Average Formula without counting zeros or max or min

[ShanaVT]

I want to create a formula that takes the average of a set of values but throws out the highest value and lowest value and does not consider zeros. Any ideas?

 

[tinguyen]

A solution by using the SUMPRODUCT function :

=SUMPRODUCT((range<>MAX(range))*(range<>MIN(range))*(range<>0)*range)/SUMPRODUCT((range<>MAX(range))*(range<>MIN(range))*(range<>0))

 

[Aladin Akyurek]

I want to create a formula that takes the average of a set of values but throws out the highest value and lowest value and does not consider zeros. Any ideas?

Control+shift+enter, not just enter:

Either...

=TRIMMEAN(IF(A2:A7,A2:A7),2/SUM(IF(A2:A7,1)))

Or...

=AVERAGE(IF(A2:A7,IF(1-(A2:A7=MAX(A2:A7)),IF(1-(A2:A7=MIN(A2:A7)),A2:A7))))

whichever is appropriate.