Calculate running average but not count a zero.

[marsjune68]

A B C D E F

ROW1 DATE SCORE MATH AVERAGE
ROW2 1 1/2/2009 0 0 0
ROW3 2 1/2/2009 75 75 75
ROW4 3 1/2/2009 135 213 107
ROW5 4 1/2/2009 99 312 104
ROW6 5 1/2/2009 0 0 0
ROW7 6 1/2/2009 149 149 30


THE MATH:
=IF(D1=0,0,D1)
=IF(D2=0,0,D2+E1) and so on

THE AVER
=E1/B1
=E2/B2 and so on

I want to be able to calculate the average score in a running total. But I
do not want the running total to count "0" in the score colum.
Right now id someone scores a zero it blow the average. I do not care to
count a zero in the score colum. Below is how I want it to work.
In row 5 the person scored a zero and it did not impact the average. But
with the way I am doing the formula it is changing the 115 average to a 30
average. For my purposes 115 is the average not 30. How can I do this?


A B C D E F

ROW1 DATE SCORE MATH AVERAGE
ROW2 1 1/2/2009 0 0 0
ROW3 2 1/2/2009 75 75 75
ROW4 3 1/2/2009 135 213 107
ROW5 4 1/2/2009 99 312 104
ROW6 5 1/2/2009 0 0 0
ROW7 6 1/2/2009 149 461 115

 

[Ashish Mathur]

Hi,

You can try =E1/countif(D$11,">0"). This will ignore the 0's in the
denominator.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[marsjune68]

Thank you I will try it in the morning..

Hi,

You can try =E1/countif(D$11,">0"). This will ignore the 0's in the
denominator.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[marsjune68]

A B N O P

ROW1
ROW2 DATE SCORE MATH AVERAGE
ROW3 1 1/2/2009 0 0 0
ROW4 2 1/2/2009 75 75 75
ROW5 3 1/2/2009 135 213 107
ROW6 4 1/2/2009 99 312 104
ROW7 5 1/2/2009 0 0 0
ROW8 6 1/2/2009 149 149 30

THE MATH
O3 FORMULA =IF(N3=0,0,N3)
O4 FORMULA =IF(N4=0,0,N4+O3)
O5 FORMULA =IF(N5=0,0,N5+O4)

THE AVERAGE
P3 FORMULA =O3/A3
P4 FORMULA =O4/A4
P5 FORMULA =O5/A5

I tried the formula

=E1/countif(D$11,">0")
Maybe I did something wrong but it did not seem to work. Can you clarify it
for me please. I changed the colums. i gave what the new colums are now.