No including ZERO'S when using Average fucntion

[Gudmundur]

I am using the average function in a large documents for different time
periods. Some time periods have ZERO, which bias the average.

Can I use the average function but not have it count zeros in the total?

 

[Mike H]

Hi,

Here's 1 way an array formula

=AVERAGE(IF(A1:A20<>0,IF(A1:A20<>"",A1:A20)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Mike H]

or a shorter version

=AVERAGE(IF(A1:A20<>0,A1:A20))

Mike

 

[Alan Moseley]

Why don't you calculate the average yourself ie:-

=SUM(A1:A10)/COUNTIF(A1:A10,">0")

 

[Bob Phillips]

=AVERAGE(IF(rng<>0,rng))

which is array entered, that is use Ctrl-Shift-Enter not juts Enter.

 

[Jacob Skaria]

Try

=IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
1'!K3584)),"",1)

If this post helps click Yes

 

[Shane Devenshire]

Hi,

In 2007

=AVERAGEIF(A1:A8,"<>0",B1:B8)