average of percentages between 1% and 100% in a column

S

SteveMac

Hello,

I am trying to get the average of percentages within a
column, but would like to exclude 0% and 100% from the
formula. This is the formula I thought would work:

{=AVERAGE(IF(AND(L1:L988>0,L1:988<100)))}

(I read in an earlier post to hit ctrl+shift+enter to
apply this type of formula)

Thanks!
 
D

Don Guillett

Here is a way using sumproduct that does NOT need array entering
=SUMPRODUCT((E1:E100>0)*(E1:E100<1)*E1:E100)/SUMPRODUCT((E1:E100>0)*(E1:E100
<1))
or array enter this
=AVERAGE(IF((E1:E100>0)*(E1:E100<1),E1:E100))
 
Top