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

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

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

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

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

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

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

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

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