formulas

[Guest]

i have a spread sheet i use to calculate percentages and then take those to find a rolling average. here is an example of my problem: column "a" is the amount correct, column "b" is the amount wrong, column "c" is the perctage of "a & b", column "d" is the rolling average. a1=10, b1=1, c1=90%, d1=90%...a2=10, b2=2, c2=80%, d2=85% (the average of d1 and d2)...a3=0, b=0, c3=#div/0!, d3=#div/0!...a4=10, b4=3, c4=70%, d4=#div/0! what i need to do is make d3= d2's value and for d4 to average d1-d4 but skip any with a null or 0 value (ie. d3) how can this be done.

 

[JE McGimpsey]

First, I assume that column A is the total possible correct, since if it
were the number correct then (10/(10+1)) = 91%, not 90%.

in that case,

C1: =IF(A1<>0,1-(B1/A1),"")
D1: =1-SUM(B$1:B1)/SUM(A$1:A1)

and copy down.

Note that D1 assumes that at least A1 has a value.