Unwanted Display of Zero Values

[Anne Troy]

I know how NOT to display zeroes across the board.
I know how NOT to display zeroes using conditional formatting.
I know how NOT to display zeroes using a formula.
At least I thought I did. I consider myself fairly efficient at creating formulas.

I'm trying to get the value over 40 for overtime hours.

When G5 = 40, I cannot get it to NOT display the 0.00 in the cell.

Here's my formula....

=IF(G5=40,"",IF(MAX(0,G5-40)=0,"",MAX(0,G5-40)))

Here's another that fails...

=IF(G5=40,"",MAX(0,G5-40))

 

[VoG]

Maybe a floating point arithmetic precision issue. How about


=IF(ROUND(G5,0)=40,"",MAX(0,G5-40))

 

[Matty]

Both formulas work OK for me, i.e. the Cell with the formula in it remains blank if the value in G5 is <=40.

Strange how they're not working for you...

Matty

 

[Anne Troy]

Thanks VoG! Have you tried that Tanqueray with some pink lemonade? Nom nom!!

The rounding worked, though we are using time formulas and I used no rounding to come up with the values in the totals. Ugh.

Thanks again.
Thanks, Matty, for the shot at it.

 

[VoG]

Thanks VoG! Have you tried that Tanqueray with some pink lemonade? Nom nom!!

No I haven't. As far as I know 'pink lemonade' is not available here (unless it is called something different). I just stick to tonic although Tanqueray is drinkable neat.

Glad that it worked for you.