如何查看两个单元格之间滚动平均值的+/-变化 [英] How to see the +/- change in rolling average between two cells
问题描述
从我的照片中可以看到,我有一个保龄球得分和一些跑步平均值的列表.我似乎无法解决的问题是,我希望能够看到游戏与上一游戏之间的平均变化.如果平均值下降,则例如为-1.2%,如果上升则为+ 2.1%.如果可能的话,我真的希望负平均值为红色,正平均值为绿色.
然后您可以做:
= ARRAYFORMULA(IF(A2:A"",{0;(INDIRECT("F2:F"&ROWS(F3:F))-F3:F)*-1},))
最后对它进行颜色格式化:
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.
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:
这篇关于如何查看两个单元格之间滚动平均值的+/-变化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!