Forum Discussion
Strange result in Excel
It;s a simple accounting statement.
In other similar sheets the validation cell is 0, without decimals.
Florin2185 wrote: ``I can send you the Excel file itself.``
Got it in a PM. It confirms what Hans and I had explained. In summary, the formulas should be:
A4: =ROUND(SUM(A1:A3), 2)
A9: =ROUND(SUM(A5:A8), 2)
A13: =ROUND(SUM(A11:A12), 2)
A25: =ROUND(SUM(A16:A24), 2)
A26: =ROUND(A4+A9+A10-A13-A14-A15-A25, 2)
Ironically, the infinitesimal result in A26 is because of the unnecessary parentheses around the original formula. If you had written simply =A4+A9+A10-A13-A14-A15-A25, the result would have been exactly zero. But that is not a reliable solution, because it depends on a quirk of Excel that is unpredictable.
Also note that many of the "constants" appear to be the result of calculations that were copy-and-pasted-value, or the equivalent. Their actual binary value is not the same as the binary approximation of the decimal number rounded to 15 significant digits. For example, what appears to be 308635.86 in A1 is actually 308635.86 - 5.82E-11, an infinitesimal difference.
If you want those constants to be accurate to 2 decimal places, it would be prudent to also explicitly round those calculations to 2 decimal places.
Caveat: Someone might sugggest setting the option "Precision as displayed" in order to avoid explicitly rounding formula results. I discourage that for a lot of reasons. But if you choose to expermient, be sure to make a copy of the Excel file before setting that option. Merely setting the option might have unintended consequences that are irrerversible.
See the attached file for more details. Let me know if you would like further explanation.
- In this case the error has no importance. The numbers was exported to Excel from accounting software.
But how can I be sure the result is accurate when I have thousands of data?
When Excel computes the mean or variance or correl or covar for stock exchange quotations, which I got from public sources which I have no idea how are generated?Florin2185 wrote: ``how can I be sure the result is accurate when I have thousands of data?``
That issue goes beyond the decimal-to-binary issues that we are discussing here. Even when the raw data is presented in decimal (typically, a CSV file), the precision of the data is an individual judgment call.
And BTW, that is one the key reasons why I discourage the use of "Precision as displayed". It might alter the raw data before I have had a chance to apply my own judgment.
There is no consistency, even within the financial industry. For example, for the same security, finance.yahoo.com usually reports historical prices accurate to the cent when we export the data into Excel, whereas schwab.com might report prices accurate to 4 or 6 decimal places.
Occassionally, I see finance.yahoo.com data with more decimal places. If it is of the form 110.019997 or 110.050003, I assume that reflects binary-to-decimal conversion problems of their own, unrelated to Excel. I assume such data is accurate to 2 decimal places. OTOH, if the data is of the form 108.356598, I assume the data is accurate to 4 or 6 decimal places.
Obviously, I cannot completely automate such decisions. Or when I do, I will choose an arbitrary precision for all data, typically 2 or 4 decimal places.
But that is my own "individual judgment call". You might choose differently. There is no "right" or "wrong" answer, IMHO.
(But when the data includes number of shares and market value, I try to chose a precision for unit price and number of shares so that their product equals the market value, rounded to 2 decimal places. )
-----
Florin2185wrote: ``When Excel computes the mean or variance or correl or covar [....]``
My rule is: I ensure that the raw data is accurate to an arbitrary precision ("judgment call").
But generally, I have no expectations of the precision of such calculations, unless I am presenting them for instructional purposes, and it is important that readers can reproduce my results.
That is, generally, I use the full binary result from such function calculations, unrounded. Often, that is necessary in order for the Excel calculation to match mathematical expectations, as well as we can.