Averages

[markmcd]

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.

 

[Tyro]

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")

 

[Dave Peterson]

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

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.

 

[RagDyer]

Try this"

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

 

[RagDyer]

My bad !

See Dave's formula.

 

[RagDyer]

OR, try this:

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

Assumes *no* negatives.

 

[Teethless mama]

Try like this:

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

 

[RagDyeR]

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!