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](https://i.stack.imgur.com/tqRmz.png)
then you can do:
=ARRAYFORMULA(IF(A2:A<>"", {0; (INDIRECT("F2:F"&ROWS(F3:F))-F3:F)*-1}, ))
![0](https://i.stack.imgur.com/u3Eyw.png)
and finally color format it:
![0](https://i.stack.imgur.com/XTw1i.png)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…