Inconsistent Conditional Formatting

AmandaMerrigan

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!

AmandaMerrigan

Ah-ha! 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!

Paul Newcome

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 back-end data and can leave it at $0.00.

Ryan Sides

@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.

AmandaMerrigan

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 ;)

Ryan Sides

@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?

AmandaMerrigan

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.

Ryan Sides

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.

AmandaMerrigan

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?

Paul Newcome

What is the exact formula you are using?

AmandaMerrigan

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.

AmandaMerrigan

Ah-ha! 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!

Ryan Sides

I'll have to put that one in my knowledge bank for sure! Glad you got it figured out.

Paul Newcome

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 back-end data and can leave it at $0.00.