What I'm trying to achieve is this - In Google Sheets, I have a cell that contains numbers with a comma delimiter, for example:
0.5,2,50,200,0,4
I want to have 6 conditional formatting rules, working on this cell.
The first rule will look at the first number (0.5), check if it's between a certain range (I'll hard code the range in the rule itself), for example check if it's between 0.2 and 0.7.
The second rule will have a different range for the 2nd number, etc.
In any case, if any of those conditional formatting is true, need to paint the entire cell in red.
I cannot use auxiliary cells, I can only use this one cell for this.
-
What is your question? What have you tried? Did something not work?– Blind SpotsDec 3, 2022 at 9:36
1 Answer
Split the values and process them with map()
, using choose()
and isbetween()
, like this:
=sum(
map(
split(A2, ","), sequence(1, counta(split(A2, ","))),
lambda(
value, i,
0 + choose(
i,
isbetween(value, 0.2, 0.7),
isbetween(value, 1, 1.5),
isbetween(value, 2, 3),
isbetween(value, 2, 3),
isbetween(value, 2, 3),
isbetween(value, 2, 3)
)
)
)
)