|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 13th, 2009, 11:57 PM
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
April 14th, 2009, 12:44 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate running average but not count a zero.
Hi,
You can try =E1/countif(D$1  1,"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
April 14th, 2009, 05:09 AM
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 1,"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
April 14th, 2009, 04:49 PM
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 1,"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 1,"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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
