Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate running average but not count a zero.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate running average but not count a zero.
Hi,
You can try =E1/countif(D$1:D1,"0"). This will ignore the 0's in the denominator. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "marsjune68" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate running average but not count a zero.
Thank you I will try it in the morning..
"Ashish Mathur" wrote: Hi, You can try =E1/countif(D$1:D1,"0"). This will ignore the 0's in the denominator. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "marsjune68" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate running average but not count a zero.
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$1:D1,"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. "marsjune68" wrote: Thank you I will try it in the morning.. "Ashish Mathur" wrote: Hi, You can try =E1/countif(D$1:D1,"0"). This will ignore the 0's in the denominator. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "marsjune68" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Count of Days & Average WHERE Amount <0 | Excel Worksheet Functions | |||
running average | Excel Discussion (Misc queries) | |||
running average | Excel Discussion (Misc queries) | |||
Running Average | New Users to Excel | |||
Running Average | Excel Discussion (Misc queries) |