Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

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

Highlight value reduction in google sheet while ignoring blank cells

I have a google sheet of values that vary across dates and I want to be able to highlight cells where the value drops/reduces by five or more compared to the previous value. The main issue I have is that there are also blank cells in the table so I am not sure how to ignore them.

Example of a table

    jan1    jan2    jan3    jan4    jan5
A    60      72              50      80
B    40                      32      60
C            80      88      90      75

I would want to highlight A-jan4, B-jan4, and C-jan5. Is there a way to do this with conditional formatting?


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

1 Answer

0 votes
by (71.8m points)

Try:

=ARRAYFORMULA((LOOKUP(2,1/ISNUMBER($A2:A2),$A2:A2))>LOOKUP(2,1/($B2:B2<>""),$B2:B2))

apply to range B2:F

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...