Home

contitional formatting with function IF

%3CLINGO-SUB%20id%3D%22lingo-sub-619988%22%20slang%3D%22en-US%22%3Econtitional%20formatting%20with%20function%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-619988%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%C2%B4m%20trying%20color%20cells%20(Green-yellow-red)%20considering%20the%20value%20in%20it%2C%20for%20that%2C%20I%20added%203%20rules%2C%20one%20colour%20each%2C%20but%20depending%20also%20from%20the%20sign%20in%20another%20cell.%3C%2FP%3E%3CP%3EThat%20means%2C%20if%20the%20sign%20in%20the%20formula%20as%20a%20text%2C%20matches%20the%20sign%20in%20cell%20L15%2C%20then%20operate%20and%20give%20a%20color%2C%20if%20not%2C%20then%20compare%20the%20next%20condition%2C%20and%20so%20on...%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20the%20second%20condition%20(%26lt%3B%3D%20)%20of%20the%20formula%20does%20not%20operates.%3C%2FP%3E%3CP%3EIs%20it%20correct%20to%20use%20the%20conditional%20like%20this%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreen%3A%3C%2FP%3E%3CP%3E%3DSI(L15%3D%22%E2%89%A5%22%2CT15%26gt%3B%3DS15%2CSI(L15%3D%22%E2%89%A4%22%2CT15%26lt%3B%3DS15%2CSI(L15%3D%22%3D%22%2CT15%3DS15)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYellow%3A%3C%2FP%3E%3CP%3E%3DSI(L15%3D%22%E2%89%A5%22%2CT15%26gt%3B%3D0.9*S15%2CSI(L15%3D%22%E2%89%A4%20%22%2CT15%26lt%3B%3D1.1*S15))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERed%3A%3C%2FP%3E%3CP%3E%3DSI(L15%3D%22%E2%89%A5%22%2CT15%26lt%3B0.9*S15%2CSI(L15%3D%22%E2%89%A4%22%2CT15%26gt%3B1.1*S15%2CSI(L15%3D%22%3D%22%2CT15%26lt%3B%3DS15)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3EAlejandro%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-619988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-625922%22%20slang%3D%22en-US%22%3ERe%3A%20contitional%20formatting%20with%20function%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-625922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345311%22%20target%3D%22_blank%22%3E%40Alejandro1963%3C%2FA%3E%26nbsp%3B%2C%20in%20general%20formula%20works%20if%20you%20mean%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20570px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115610i55F5AD81ED74AC2F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnother%20story%20you%20will%20never%20check%20third%20condition%20(%3D)%20since%20you%20start%20from%20%22greater%20or%20equal%22.%20Start%20checking%20from%20%22equal%22%20as%20in%20the%20second%20variant.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638678%22%20slang%3D%22en-US%22%3ERe%3A%20contitional%20formatting%20with%20function%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time%2C%20as%20you%20said%2C%20the%20formula%20worked%20but%20searching%20why%20it%20didn%C2%B4t%20I%20realised%20I%20wrote%20in%20two%20different%20ways%20the%20text%20to%20be%20compared%2C%20%22%E2%89%A4%20%22%20didn%C2%B4t%20match%20to%20%22%E2%89%A4%22%20because%20the%20first%20text%20on%20the%20column%20had%20a%20space-tab%20after%20the%20symbol%20and%20in%20the%20formula%20it%20didn%C2%B4t%20have%20that%20space.%3C%2FP%3E%3CP%3EThese%20hidden%20orthographic%20mistakes%20are%20to%20be%20taken%20into%20account.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EAlejandro%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Alejandro1963
New Contributor

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

2 Replies

@Alejandro1963 , in general formula works if you mean like this

image.png

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

@Sergei Baklan 

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies