Forum Discussion

Alejandro1963's avatar
Alejandro1963
Copper Contributor
May 20, 2019

contitional formatting with function IF

Hello,

I´m trying color cells (Green-yellow-red) considering the value in it, for that, I added 3 rules, one colour each, but depending also from the sign in another cell.

That means, if the sign in the formula as a text, matches the sign in cell L15, then operate and give a color, if not, then compare the next condition, and so on...

My problem is that the second condition (<= ) of the formula does not operates.

Is it correct to use the conditional like this ?

 

Green:

=SI(L15="≥",T15>=S15,SI(L15="≤",T15<=S15,SI(L15="=",T15=S15)))

 

Yellow:

=SI(L15="≥",T15>=0.9*S15,SI(L15="≤ ",T15<=1.1*S15))

 

Red:

=SI(L15="≥",T15<0.9*S15,SI(L15="≤",T15>1.1*S15,SI(L15="=",T15<=S15)))

 

Thank you in advance

Alejandro

Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 21, 2019

    Alejandro1963 , in general formula works if you mean like this

    Another story you will never check third condition (=) since you start from "greater or equal". Start checking from "equal" as in the second variant.

    • Alejandro1963's avatar
      Alejandro1963
      Copper Contributor
      May 23, 2019

      SergeiBaklan 

      Hi Sergei,

      Thank you for your time, as you said, the formula worked but searching why it didn´t I realised I wrote in two different ways the text to be compared, "≤ " didn´t match to "≤" because the first text on the column had a space-tab after the symbol and in the formula it didn´t have that space.

      These hidden orthographic mistakes are to be taken into account.

      Regards

      Alejandro