Sep 14 2022 12:03 PM
Sep 14 2022 12:03 PM
We have been using the same workbook to verify payroll information for years. This payroll run though, we ran into an issue that we've narrowed down to a floating point rounding error. When we sort our workbook alphabetically, we get one total. But as soon as it is sorted by department and then alphabetically, the total changes by a penny. If we turn on the "Set precision as displayed" option, we can sort it however needed, and the total stays the same. Does anyone have any sort of explanation on why this is happening all of a sudden?
Sep 14 2022 12:32 PM - edited Sep 14 2022 02:38 PM
I'm sure I could explain it in detail if you provided some details -- a numerical example. Ideally, attach an example Excel file (redacted) that demonstrates the problem.
You have already offered one theory: floating-point rounding "error" (I prefer "anomalies").
That might alter the sort order, which in turn might alter the arithmetic result.
However, usually that difference is infinitesimal; not large enough by itself to cause a "penny" off.
I suspect that you have "normal" rounding errors -- that is, failing to round calculations such as 123.45 * 12.34% -- and perhaps that combined with floating-point anomalies cause the penny-off differences.
But that is wild speculation. It is difficult to say anything more without a concrete example with sufficient detail (including formulas) for us to duplicate the problem independently.
I presume you understand why enabling "Precision as displayed" corrects the problem. But I usually deprecate the use of PAD because of its broad implications and potential for unintentionally changing some constant values permanently.
I hope you were wise enough to enable PAD in a copy of the original file.
Sep 14 2022 12:36 PM
Does anyone have any sort of explanation on why this is happening all of a sudden?
First, let me say that I was for several years in charge of the HR and Payroll database of a major corporation.
Second, prior to that I regularly ran a management seminar that (among other things) taught diagnostic processes--how to analyze unexpected deviations from established norms so as to find cause.
So your question sets off empathetic chords in several ways.
First step in diagnosing something like this is getting a very clear and precise description of the symptoms, and you've done a reasonable job at that, describing this one cent variance, when it happens and when it doesn't happen (this latter being often significant). If we were sitting down face-to-face, there'd be a few more description questions that would be worth exploring, but let's go with what we've got for now.
The next step is to generate hypotheses by thinking about such things as (these are examples):
We need to call on you to answer those questions. I'm hoping that one of them might trigger an "Aha! Maybe that's the cause." And if it does, I'll just point out that we're still at the hypothesis stage, so any reasonable sounding explanation should be taken to the next, which is verification. That's where you go check and see (through gathering additional information to confirm or deny) whether the hypothesis checks out in the real world.
But I want to add a couple of others that occur to me:
Sep 14 2022 12:40 PM - edited Sep 14 2022 06:37 PM
PS.... I wrote: `` Ideally, attach an example Excel file (redacted) that demonstrates the problem.``
To do that, click "browse" near the bottom of the reply window.
If the forum does not permit you do that (yet), upload the file to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.net. You might like onedrive.live.com because it uses the same login as this forum. But with "onedrive", be sure the link that you provide does not allow others to edit the file.
If the forum does not permit you to post URLs (yet), spell out part of the URL. For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/sorting-and-floating-point-rounding-error/td-p/3627550 .
If you are relunctant to provide public access to an example Excel file, send it to me in a Private Message. If I can, I will "sanitize" it for others to view as well, with your permission. LMK.