100% in conditional formatting

Hi, how can I express 'greater than 100%' in conditional formatting? This is my column:

And this is the conditional formatting command I'm using:

I've tried 0.01, 1, 1.0 and 100, and it just returns everything red. Any help would be very much appreciated.

Best Answer

  • LisaB:-)
    LisaB:-) ✭✭
    Answer ✓

    I've uncovered a can of worms here. Firstly, I can see that the conditional formatting on the Variance column is wrong, as red should be black and vice versa, but I can fix that. Secondly:

    This is the source data - Sheet 1 (see column Current spend as % of total)

    This is the sheet which links to it - Sheet 2

    the conditional formatting on Sheet 2 says this, but it's clearly not working. The purpose is to identify in red figures that are over 100%.

    The formula in Sheet 2 for Current spend as % of total is =IFERROR(([Spend to Date]3) / ([TOTAL Budget]3), 0

    Really appreciate your help, thank you.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!