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
-
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.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi, thanks Andree. That's not working, this is what I get when I use 1 to express 100%:
-
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)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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?
-
How are they linked? How is the source data populated?
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi, thanks both. The cells are linked to another sheet, and that cell has a calculation.
-
What is the formula?
-
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.
-
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.
-
No worries and happy to help. 👍️
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!