| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non zero values
I recall reading this and now cant find it.
A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
Your formula works as long as it is array-entered. Are you sure
you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
I believe the OP has to change it to
=AVERAGE(IF(A1:A44<0,A1:A44)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... Your formula works as long as it is array-entered. Are you sure you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
That was my initial thought -- the extra parentheses -- but the
formula worked both with and without the parens.I tested in Excel 2003. Perhaps an earlier version would have problems with the extra parens. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Peo Sjoblom" wrote in message ... I believe the OP has to change it to =AVERAGE(IF(A1:A44<0,A1:A44)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... Your formula works as long as it is array-entered. Are you sure you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
Thanks Peo, that does it.
"Peo Sjoblom" wrote: I believe the OP has to change it to =AVERAGE(IF(A1:A44<0,A1:A44)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... Your formula works as long as it is array-entered. Are you sure you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
I believe it is the syntax, OP's formula is
=IF((A1:A44)<0,AVERAGE(A1:A44)) which will work if A1 is neither zero nor blank, if A1 is blank or 0 it will return FALSE If the OP got FALSE he must have a zero in the first cell -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... That was my initial thought -- the extra parentheses -- but the formula worked both with and without the parens.I tested in Excel 2003. Perhaps an earlier version would have problems with the extra parens. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Peo Sjoblom" wrote in message ... I believe the OP has to change it to =AVERAGE(IF(A1:A44<0,A1:A44)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... Your formula works as long as it is array-entered. Are you sure you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
Hi Folks,
I use Excel 2000 and only Peo's formula works correctly. Cheers -- macropod [MVP - Microsoft Word] "PA" wrote in message ... Thanks Peo, that does it. "Peo Sjoblom" wrote: I believe the OP has to change it to =AVERAGE(IF(A1:A44<0,A1:A44)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Chip Pearson" wrote in message ... Your formula works as long as it is array-entered. Are you sure you've array entered it? What result do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
=SUMIF(A1:A44,"<0")/COUNTIF(A1:A44,"<0")
Vaya con Dios, Chuck, CABGx3 "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average non zero values
And since 0 doesn't add to a sum:
=sum(a1:a44)/countif(a1:a44,"<0") CLR wrote: =SUMIF(A1:A44,"<0")/COUNTIF(A1:A44,"<0") Vaya con Dios, Chuck, CABGx3 "PA" wrote in message ... I recall reading this and now cant find it. A column of data with numerical values, some empty cells and some with zero's. I need to calculate the average, but supress cells with zeros from the calculation. I tried =IF((A1:A44)<0,AVERAGE(A1:A44)) arrayed entered, but no go. Suggestions welcomed, and my thanks. Paul -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Values / Multiple Worksheets | Excel Worksheet Functions | |||
| Conditional math using AND, Average | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| How to average a column, but exclude zero AND negative values? | Excel Discussion (Misc queries) | |||
| How do I average a formula without calculating zero values? | Excel Discussion (Misc queries) | |||
Linear Mode
