How to see the +/- change in rolling average between two cells

Question

As you can see from my picture, I have a list of bowling scores and some running averages. The issue I cannot seem to solve is I would like to be able to see the change in average between a game and the previous game. If the average goes down, it would say -1.2% for example or +2.1% if it goes up. I would really like negative averages to be in red and positive ones in green if that is possible.

Here is a copy of my sheet with the desired output in column G.

Answer 1

first you will need running average:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(ISNUMBER(
 ARRAY_CONSTRAIN(SPLIT(SORT(REPT("♦ ", ROW(INDIRECT("A1:A"&COUNTA(A2:A)))-1), 1, 0)&
 "♦"&TEXTJOIN("♦", 1, C2:C), "♦"), 999^99, COUNTA(A2:A))), 
 ARRAY_CONSTRAIN(SPLIT(SORT(REPT("♦ ", ROW(INDIRECT("A1:A"&COUNTA(A2:A)))-1), 1, 0)&
 "♦"&TEXTJOIN("♦", 1, C2:C), "♦"), 999^99, COUNTA(A2:A)), )),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A), 
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&"")), 
 "select Col2", 0))

---

then you can do:

=ARRAYFORMULA(IF(A2:A<>"", {0; (INDIRECT("F2:F"&ROWS(F3:F))-F3:F)*-1}, ))

---

and finally color format it:

---

UPDATE:

with new functions, there is a new method...

average = sum / count therefore we can run running total / cumultative sum:

=SCAN(, A2:A20, LAMBDA(x, y, (x+y)))

and then just divide by sequence to get the running average:

=INDEX(SCAN(, A2:A20, LAMBDA(x, y, (x+y)))/(ROW(A2:A20)-1)) 

and to get the difference between x and x+1 cells:

={0; MAP(B2:B19, LAMBDA(z, OFFSET(z, 1, )-z))}

and the final touch:

0[=0][black];    -0.00###[<0][red];    0.00####[color 50]

---

or in one go:

={0; INDEX(ARRAY_CONSTRAIN({QUERY(
 MAP(SCAN(, A2:A20, LAMBDA(x, y, (x+y))), ROW(A2:A20)-1, 
 LAMBDA(a, b, a/b)), "offset 1", ); ""}-
 MAP(SCAN(, A2:A20, LAMBDA(x, y, (x+y))), ROW(A2:A20)-1, 
 LAMBDA(a, b, a/b)), ROWS(A2:A20)-1, 1))}

Answer 2

You get the change percentage using: =ROUND((C3-C2)/C2*100,2) in G3:G20.

Check:

• For going down from 145 to 123 this returns (with no rounding) -15.17241379.
• 145 + (145 * -15.17241379 / 100) = 123

For the colouring:

• Select the cells G3:G20.
• Go to -> Format -> Conditional formatting -> Format rules -> Format cells if… -> Greater than -> 0 -> Formatting style
  • -> [Down arrow next to letter A] -> [Pick your colour]
  • -> [Down arrow next to (bucket with) fill colour] -> [None]

PS: For the running average you can use: =AVERAGE(C$2:C2) in F2:F20.

Please comment, if and as this requires adjustment / further detail.