Need help (Average excluding zero values)

M

Mike

This is what i have & it works but one problem there will be zero values & i
need to exclude them if possible
=AVERAGE(IF(AA5="L",E$5:E$65))
I have tried several different combinations I can,t get it
 
G

Gary Keramidas

that includes zero values in the average for me. are they blanks and not zeroes?
 
B

Bernard Liengme

Is you aim to compute the average only when AA5 ="L" ?
Then you formula should be =IF(AA5="L", AVERAGE(E$5:E$65), "")

To omit zeros in the average
=IF(AA5="L",AVERAGE(IF(E$5:E$65<>0,E$5:E$65)),"")
This is an array formula and Excel will enclose in in braces { ... }

This is a non-array formula that does the same
=IF(D1="L",SUM(A1:A7)/COUNTIF(A1:A7,"<>0"),"")
best wishes
 
P

p45cal

Bernard said:
This is a non-array formula that does the same
=IF(D1="L",SUM(A1:A7)/COUNTIF(A1:A7,"<>0"),"")
best wishes

Careful there, if the range includes blanks they be counted as zeroe
towards the average
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top