Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

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

Bowling Scores

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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))

0


then you can do:

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

0


and finally color format it:

0


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...