100% in conditional formatting

LisaB:-)

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.

LisaB:-)

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.

Andrée Starå

Hi @LisaB:-)

Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1. You'd need to use decimal values instead for it to work 

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1

Depending on your country/region, you'll need to exchange the comma to a period.

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

LisaB:-)

Hi, thanks Andree. That's not working, this is what I get when I use 1 to express 100%:

Andrée Starå

@LisaB:-)

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

LisaB:-)

I'm trying to make a duplicate without sharing confidential information but it's tricky. However, in the process of creating that duplicate, I added a new column and typed in the values, and that worked with the conditional formatting, so it must be something to do with the fact that these cells are linked from another sheet. Any ideas?

Paul Newcome

How are they linked? How is the source data populated?

Andrée Starå

@LisaB:-)

Can you check if it's linked as a number or text in a so-called helper column?

You can use the functions ISNUMBER and ISTEXT.

What did you find out?

LisaB:-)

Hi, thanks both. The cells are linked to another sheet, and that cell has a calculation.

Paul Newcome

What is the formula?

LisaB:-)

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.

LisaB:-)

Sorry guys! I've just realised that it was a mistake in the conditional formatting that referenced both column Variance and column Current spend, but didn't show in the conditional formatting pane. Sorry for wasting your time, many thanks for your assistance.

Paul Newcome

No worries and happy to help. 👍️

Andrée Starå

@LisaB:-)

Happy to help!

Easy to miss!

Glad you got it working!