Inconsistent Conditional Formatting
Hey Community!
I'm wondering if someone can help me out with why conditional formatting is not giving me consistent results.
As shown below, I would like to see red text when the Unbilled column is not zero. One section is returning red text for $0.00 and another section is leaving it black (which is what I want). The 3 cells circled have the exact same formula in each section.
When I pick my criteria from a list, I'm given multiple $0.00 options...and the more I work on the sheet, the more the list of zero dollars grows.
Any suggestions??
Thanks!
Best Answers

Ahha! Figured it out! I increased the number of decimal places and that value is not actually $0!
I'll have to change my criteria to greater than 1 and another for less than 1.
Thanks to you both for being a sounding board to help me work through it!

That's what I was thinking. Glad you got it sorted.
You could also wrap each formula in a ROUND function and specify 2 decimal places so you don't have to worry about backend data and can leave it at $0.00.
Answers

@Amanda M what happens when you change the font color of the top zeros to black?
Is your zero column format set to Currency $? (Highlight the column and click the $ in the icon menu at the top)
Also, in the first step of your conditional format, click the link that says "define custom criteria" and set it to "is not equal to" and enter 0 in the field.
I haven't seen the duplicated zeros in the list before.

That's how I have it set up...Currency column is set, and criteria is not equal to zero.
And the font colour is automatic...so black. I've tried moving the conditional formatting priority up and it doesn't make a difference either.
*I should note that I'm not a beginner user, I just have a beginner profile because I started with a new company recently ;)

@Amanda M Thanks for the note! I'm thinking the other Conditional Formats are overriding. (i.e. the one above says if Hierarchy = 4, the entire row is black font. ) Have you tried moving it to the very top of Conditional Formatting?

I tried that too. Moving right to the top doesn't help either. The grouping of cells are literally exactly the same (formula, hierarchy, etc)
Oh wait  the only difference is that the top ones showing red are $0.00 because of numbers being subtracted to get the value. The ones showing black are a result of equation $0.00$0.00
vs.
That seems to be consistent with the rest of the page as well.

Does your formula have a +"" or something similar? This would convert the number into text which Cond Format would consider as not equal to zero. Other than Cond Formatting step on itself, which you tested, I'm at a loss.

All the "x" & "z" values below utilize an IFERROR formula that returns "" with an error. But it's the same formula in the top group as the bottom (from original post) so I'm not sure why there would be a difference in formatting.
The same happens in this group, and it only seems to be if there is a mathematically equation involved.
@Andrée Starå @Paul Newcome any thoughts?

What is the exact formula you are using?

For "x"  the bottom 2 values (530,581.51 & 33,161.34) are =IFERROR([Total Sell]2 * [*Delivered/Installed Payment]# * [% to Invoice (based on completion)]@row * [% of Material cost]@row, "") and the top value is =IFERROR(SUM(CHILDREN()), "")
For "y" = the top value is =SUM(CHILDREN()) and the bottom 2 yellow cells are text entries. If I put a $0 in there it doesn't change the outcome.
For "z"  the bottom 2 values are =IFERROR([Total to Invoice]@row  [Actual Invoiced]@row, "") and the top is =[Total to Invoice]@row  [Actual Invoiced]
I removed all the IFERRORs and it's still producing the same result.

Ahha! Figured it out! I increased the number of decimal places and that value is not actually $0!
I'll have to change my criteria to greater than 1 and another for less than 1.
Thanks to you both for being a sounding board to help me work through it!

That's what I was thinking. Glad you got it sorted.
You could also wrap each formula in a ROUND function and specify 2 decimal places so you don't have to worry about backend data and can leave it at $0.00.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives