| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| first ten and last ten averages | Excel Worksheet Functions | |||
| Averages | Excel Worksheet Functions | |||
| Column Chart With Averages Displayed | Charts and Charting in Excel | |||
| Help with averages please | Excel Worksheet Functions | |||
| Averages | Excel Worksheet Functions | |||
Linear Mode
