Need help with Conditional formatting

Hello Experts,

Need your help with one issue which I am trying to resolve, I have a data in which I applied the Conditional formatting based on the helper column as below -

1) If Current Coverage CN (M) = Previous Coverage CN (M) it will result N

2) If Current Coverage CN (M) > Previous Coverage CN (M) it will result T

3) If Current Coverage CN (M) < Previous Coverage CN (M) it will result F

The colours for the above conditions I have set in the conditional formatting is Red for F, Green for T and Yellow for N.

But here the strange thing is conditional formatting is working for some cells and for some cells it's not working for example -

Row no. 5 Previous Coverage CN(M) = Current Coverage CN(M) but it is showing green colour ideally it should show yellow.

Same way Row No.1 Current Coverage CC% = Previous Coverage CC% which is 29% but it is showing Green colour in Current Coverage CC % whereas below cell in the same column that is row no.2 is equal to Previous Coverage CC% that is 42% and it showing correct result as yellow.

Please refer the attachment.

The formula for helper I have used as below.

=IF([Current Coverage CN(M)]@row = [Previous Coverage CN (M)]@row, "N", IF([Current Coverage CN(M)]@row > [Previous Coverage CN (M)]@row, "T", "F")).

Can anyone suggest what is the issue here?


  • anilkrag1984
    edited 11/19/22

    Adding to the above issue, just tested the helper condition wherein I applied the formula as below to match Current Coverage CC% to Previous Coverage CC% which 29% and this is resulting F instead of N which means 29% in both the cell doesn't match.

    =IF([Current Coverage CC%]@row = [Previous Coverage CC%]@row, "N", "F")

    Please note the value updated in the sheet are pulled from other sheet through sumifs.

    Attached the screenshot for your reference.

    Please note - The cell below 29% in Column Current Coverage CC% is working fine but there is some issue with cell value 29%, Also please Current Coverage CC(M) Cell no.4 is also not matching with Previous Coverage CC(M) cell no. which is $15.

    Attached the screenshot for your reference.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try adjusting the number of decimals shown. It may be that one is $23.01 and another is $23. Without decimals it would look like they are equal, but the data stored on the backend is showing that they are not.

  • Hi Paul, Thank you for your response checked the data you are correct data stored at the backend is 28.8 and 28.3. Tried adjusting the decimals 0.00<- but not working can you please advise?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to incorporate a ROUND/ROUNDDOWN/ROUNDUP (your choice) function in your formula.

    =IF(ROUNDDOWN([Column Name]@row) = ROUNDDOWN([Column Name]@row), "Yellow", IF(...........................

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!