average without min qty

  • Thread starter Thread starter Looping through
  • Start date Start date
L

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
 
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
 
Back
Top