1

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.

1
  • What is your question? What have you tried? Did something not work?
    – Blindspots
    Dec 3, 2022 at 9:36

1 Answer 1

0

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) 
      ) 
    ) 
  ) 
)
1
  • Amazing, solved it, thank you!
    – Yoav Mor
    Dec 3, 2022 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.