Forum Discussion
Excel Formula Help for $0.00 Value
I am in need of help with a formula again. This time it is for Conditional Formatting (at least I think). So I've tried every method I can think of, as well as using Google, to get cell D5 to show blank when the returned value is $0.00. Nothing I have tried seems to work. I've attached the spreadsheet.
Any help would be greatly appreciated!! I've spent so much time trying to think of a proper formula to get it to work. I don't know what I am doing wrong.
There seems to have been a change done where I cannot upload files directly here anymore. I don't know if I'm doing something wrong but I consider myself to be rather tech savvy.
Below is a link to the OneDrive version of the spreadsheet to hopefully get around the fact that I can't upload the file directly here anymore.
https://communitas123-my.sharepoint.com/:x:/g/personal/kdfox_communitas_org/EYUhfjmx0NJIln4VrDCRXV0BqCywptUG2NDhNH9rTxlk7Q?e=me657j
7 Replies
In the desktop version of Excel, you can hide all zero values in the worksheet:
- Select File > Options.
- Select Advanced.
- Scroll down to the section 'Display options for this worksheet'.
- Clear the check box 'Show a zero in cells that have a zero value'.
- Click OK.
If you don't want that, you have several alternative options:
- Change the formula to =IF(Calculations!B96+Calculations!C96=0,"",Calculations!B96+Calculations!C96)
- Or set the number format of the cell with the formula to the custom format $0.00;-$0.00;
- I'd already tried the first option, and it didn't change anything. The 2 new options you gave me also have had no effect (I'd already tried similar formulas). I don't understand what is happening. Even when doing conditional formatting it's changing nothing.
KevinCommunitas wrote: `` I don't understand what is happening.``
Because D5 is not really zero. It is 0.00400000000000339 -- not even close.
The "0.00...00339" part is probably due to the so-called "floating-point error" (anomaly, not truly an "error"). That should be avoided by explicitly rounding a calculation (somewhere).
But the "0.004..." part is due to the underlying calculation. Calculations!C96 is truly zero. But Calculations!B96 is 0.00400000000000339. That might be avoided by the same explicit rounding, by accident. But whether or not it should be avoided, one way or another, is something only you can decide.
We probably cannot help you any further because the Calculations worksheet is password-protected.
Please "re-post" the Excel file without password-protected elements. And instead of a link to onedrive.live.com, attach the Excel file to the posting. Use the "browse" link at the bottom of the Reply pane.
