Dec 20 2021 12:26 PM
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.
Dec 20 2021 12:37 PM
In the desktop version of Excel, you can hide all zero values in the worksheet:
If you don't want that, you have several alternative options:
Dec 20 2021 12:41 PM
Dec 20 2021 01:02 PM - edited Dec 20 2021 01:05 PM
@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.
Dec 20 2021 01:06 PM
@JoeUser2004 There we go!! It finally gave me the option to upload the file!! It's all been un-protected now.
Dec 20 2021 01:24 PM
Dec 20 2021 01:24 PM - edited Dec 20 2021 03:07 PM
Well, I'm sure that you can figure this out a lot faster than we can, since presumably you understand the calculations.
To begin with, the formula in Calculations!B96 breaks down as follows:
=(122.84-108.0081-0-8.5783-0-0-0-5.9296-0.32)-0
for
=('Vendor Sheet'!C5-Calculations!K96-Calculations!J96-Calculations!I96-Calculations!H96-G96-F96-E96-D96)-C96
My guess is: the calculations in K96, I96 and E96 should be explicitly rounded to 2 decimal places.
But K96 is =K93+K94, and K93 appears to be 99.09 exactly. (It actually is; but only by accident.)
OTOH, K94 is =K93*9%. Almost certainly, that should be =ROUND(K93*9%, 2) .
You can track down the calculation errors for I96 and E96.
Nevertheless, the formula in B96 should be:
=ROUND('Vendor Sheet'!C5-K96-J96-I96-H96-G96-F96-E96-D96-C96, 2)
That eliminates the "floating-point error".
In fact, you should do that for __all__ of your calculations that you expect to be accurate to some number of decimal places (always 2?).
PS.... Better: =ROUND('Vendor Sheet'!C5-SUM(C96:K96), 2)
Caveat.... Alternatively, someone might suggest setting the "Precision as displayed" option. I do __not_recommend__ that in general, and especially in your case -- at least, not without careful review of all of your calculations and cell formats. But if you choose to experiment, be sure to make a back-up copy of the Excel file before setting the option. Merely setting the option might change some constants unintentionally and irreversibly, if they are displayed with less precision on purpose.
Dec 20 2021 01:29 PM - edited Dec 20 2021 03:04 PM
@HansVogelaar wrote: ``=ROUND(Calculations!B96+Calculations!C96,2)``
And yes, that should be done __as_well__.
But that alone is hardly the complete correct answer, except by accident.