Sorting and Floating Point Rounding Error

[Hawkins42]

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? 

[#2]

@Hawkins42 

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.

[#3]

@Hawkins42 

Does anyone have any sort of explanation on why this is happening all of a sudden? 

Interesting question!

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): 

• what's different between (in this case) the alphabetical sort and the departmental sort that could conceivably lead to the one cent variance?
  • Which of the sorts produces the higher number, which the lower?
  • Are their sub-totals by department in the one case, and no subtotals in the other?
    • How are the sub-totals done; and from them, the grand-total? 
  • in all of those sub-totals, are numbers rounded to nearest cent, and if so, at what stage?
• what's changed (or is different) between this payroll run and (apparently) all prior runs, that could lead to the one cent variance?

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:

• To clarify the whole situation a bit more, I've been assuming that this total to which you refer is the total of money paid out; but it occurs to me that it may be some other total (e.g., taxes withheld, 401(k) deductions withheld, and so forth) or it could be base salaries or wages at the database end of things. So if you could clarify exactly what numbers are being totaled and coming up with this difference?
• What's the source of the data that's being totaled? Is the payroll run coming from a different system, so the data you're running to verify has been imported into your workbook from that external system? Might something have changed in any such external system?

[#4]

@Hawkins42

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.