Getting averages from a column
 

Hi, I would like to work out the average from a column of numbers but where
no data is available, it reads #N/A or #REF! So I can not use the automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

assuming your data is in a1:a8 and you want to exclude 0 values, try

=SUM(IF(ISNUMBER(A1:A8),A1:A8))/COUNTIF(A1:A8,"0")

entered using Cntrl+Shift+Enter

"tommy" wrote:

Hi, I would like to work out the average from a column of numbers but where
no data is available, it reads #N/A or #REF! So I can not use the automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

I think any average would be influenced by any zeros in the column

Does that mean you want to exclude 0?

This will exclude 0 and any errors. Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10<0,A1:A10)) )

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

You can use SUMIF and normally enter (assuming no -ve values with the
COUNTIF):

=SUMIF(A1:A8,"<1E100")/COUNTIF(A1:A8,"0")

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
assuming your data is in a1:a8 and you want to exclude 0 values, try

=SUM(IF(ISNUMBER(A1:A8),A1:A8))/COUNTIF(A1:A8,"0")

entered using Cntrl+Shift+Enter

"tommy" wrote:

Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

Thanks I'll give it a go.

"JMB" wrote:

assuming your data is in a1:a8 and you want to exclude 0 values, try

=SUM(IF(ISNUMBER(A1:A8),A1:A8))/COUNTIF(A1:A8,"0")

entered using Cntrl+Shift+Enter

"tommy" wrote:

Hi, I would like to work out the average from a column of numbers but where
no data is available, it reads #N/A or #REF! So I can not use the automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

Thanks I'll give it a go.

"T. Valko" wrote:

I think any average would be influenced by any zeros in the column

Does that mean you want to exclude 0?

This will exclude 0 and any errors. Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10<0,A1:A10)) )

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

Hi, I tried this and it worked great, thank you.

"T. Valko" wrote:

I think any average would be influenced by any zeros in the column

Does that mean you want to exclude 0?

This will exclude 0 and any errors. Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10<0,A1:A10)) )

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.

Getting averages from a column
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
Hi, I tried this and it worked great, thank you.

"T. Valko" wrote:

I think any average would be influenced by any zeros in the column

Does that mean you want to exclude 0?

This will exclude 0 and any errors. Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10<0,A1:A10)) )

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"tommy" wrote in message
...
Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think
any
average would be influenced by any zeros in the column, Can any one
help.
Thanks in advance, Tommy.

Getting averages from a column
 

Good catch on the negative values. Maybe change the countif to count.

=SUMIF(A1:A8,"<1E100")/COUNT(1/A1:A8)


"T. Valko" wrote:

You can use SUMIF and normally enter (assuming no -ve values with the
COUNTIF):

=SUMIF(A1:A8,"<1E100")/COUNTIF(A1:A8,"0")

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
assuming your data is in a1:a8 and you want to exclude 0 values, try

=SUM(IF(ISNUMBER(A1:A8),A1:A8))/COUNTIF(A1:A8,"0")

entered using Cntrl+Shift+Enter

"tommy" wrote:

Hi, I would like to work out the average from a column of numbers but
where
no data is available, it reads #N/A or #REF! So I can not use the
automatic
system found in auto sum, is there any way around this. Also I think any
average would be influenced by any zeros in the column, Can any one help.
Thanks in advance, Tommy.