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?

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

"Gudmundur" wrote:

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?

or a shorter version

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

Mike

"Mike H" wrote:

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

"Gudmundur" wrote:

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?

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

=SUM(A1:A10)/COUNTIF(A1:A10,"0")
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Gudmundur" wrote:

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?

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

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

--
__________________________________
HTH

Bob

"Gudmundur" wrote in message
...
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?

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Gudmundur" wrote:

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?

Hi,

In 2007

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Gudmundur" wrote:

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?