average without min qty

[Looping through]

Does any know how to exclude the min price in a list of numbers I am using a
sumproduct that works, but I want to exclude the mins to returns a slightly
higher price.

Can someone help?

=SUMPRODUCT(--('05'!$B$2:$B$209=$A3),--('05'!$E$2:$E$209>0),('05'!$E$2:$E$209))/SUMPRODUCT(--('05'!$B$2:$B$209=$A3),--('05'!$E$2:$E$209>0))

Thanks
Peter

 

[Jacob Skaria]

The below formula will exclude 0's and the minimum value and then return an
average. Please note that this is an array formula. You create array formulas
in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=AVERAGE(IF(('05'!$B$2:$B$209=$A3)*('05'!$E$2:$E$209>
MIN(IF(('05'!$B$2:$B$209=$A3)*('05'!$E$2:$E$209>0),'05'!$E$2:$E$209))
),'05'!$E$2:$E$209))

If this post helps click Yes