Averages
 

Could someone please advise me a nice simple formula for giving an average
for non zero entries. The average function in Excel provides an average of a
list of numbers including numbers that are zero. I need the average of just a
series of numbers where zeros are not considered in the result.

Averages
 

If your numbers are in A1:A10 then an array formula will do it in all Excel
versions.

=AVERAGE(IF(A1:A10<0,A1:A10)) enter this with Ctrl+Shift+Enter not just
Enter

Excel 2007

=AVERAGEIF(A1:A10,"<0")


"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an average
for non zero entries. The average function in Excel provides an average of
a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.

Averages
 

=sum(a1:a10)/(count(a1:a10)-countif(a1:a10,0))

markmcd wrote:

Could someone please advise me a nice simple formula for giving an average
for non zero entries. The average function in Excel provides an average of a
list of numbers including numbers that are zero. I need the average of just a
series of numbers where zeros are not considered in the result.

--

Dave Peterson

Averages
 

Try this"

=Sum(A1:A100)/Countif(A1:A100,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an average
for non zero entries. The average function in Excel provides an average of
a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.

Averages
 

My bad !

See Dave's formula.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Try this"

=Sum(A1:A100)/Countif(A1:A100,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an
average
for non zero entries. The average function in Excel provides an average
of a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.

Averages
 

OR, try this:

=SUM(A1:A10)/COUNTIF(A1:A10,"0")

Assumes *no* negatives.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyer" wrote in message
...
My bad !

See Dave's formula.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Try this"

=Sum(A1:A100)/Countif(A1:A100,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an
average
for non zero entries. The average function in Excel provides an average
of a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.

Averages
 

Try like this:

=SUM(A1:A100)/SUM(COUNTIF(A1:A100,{"0","<0"}))


"RagDyer" wrote:

Try this"

=Sum(A1:A100)/Countif(A1:A100,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an average
for non zero entries. The average function in Excel provides an average of
a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.

Averages
 

I think this brings up a point of logic.

If zero is not to be used, how do we go from positive to negative without
there being the possibility of the existence of zero?

How do you set the calculations to *bypass that exact* zero transition
point?

Therefore, I assumed:
No Zero
No Negatives

But of course ... the OP is the final word!

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Teethless mama" wrote in message
...
Try like this:

=SUM(A1:A100)/SUM(COUNTIF(A1:A100,{"0","<0"}))


"RagDyer" wrote:

Try this"

=Sum(A1:A100)/Countif(A1:A100,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"markmcd" wrote in message
...
Could someone please advise me a nice simple formula for giving an
average
for non zero entries. The average function in Excel provides an average
of
a
list of numbers including numbers that are zero. I need the average of
just a
series of numbers where zeros are not considered in the result.