string.Format() on double values gives inaccurate results

Question

I've got this code:

<tbody>
    @foreach (var day in user.WorkDays)
    {
        <tr>
            <th>@day.Date.ToString("MM/dd/yy")</th>
            <td>
                <ul>
                    @foreach (var note in day.Notes)
                    {
                        <li>@note.Text</li>
                    }
                </ul>
            </td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Regular * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Overtime * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Doubletime * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Sick * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Vacation * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Holiday * 100) / 100)</td>
            <td>@string.Format("{0:0.00}", Math.Truncate(day.Totals.Overall * 100) / 100)</td>
        </tr>
    }
</tbody>
<tfoot>
    <tr>
        <th>Totals:</th>
        <th></th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Regular * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Overtime * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Doubletime * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Sick * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Vacation * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Holiday * 100) / 100)</th>
        <th>@string.Format("{0:0.00}", Math.Truncate(user.Totals.Overall * 100) / 100)</th>
    </tr>
</tfoot>

It produces this result:

enter image description here

(Open the image in a new tab to see it full-sized.)

If you look at the values on the right-most column, you'll notice that they do not add up to the 79.98 at the bottom right of the table. I calculated that they add up to 79.93.

Since I know somebody is going to ask, yes, 79.98 is the correct total. It's the values that are supposed to add up to that total that are incorrect.

What am I doing wrong? I've been fiddling around with this for far too long and haven't seen any change.

Edit:

After reading some comments, it's clear that the Math.Truncate() calls are not helping. Here's what I had previously:

<tbody>
    @foreach (var day in user.WorkDays)
    {
        <tr>
            <th>@day.Date.ToString("MM/dd/yy")</th>
            <td>
                <ul>
                    @foreach (var note in day.Notes)
                    {
                        <li>@note.Text</li>
                    }
                </ul>
            </td>
            <td>@day.Totals.Regular.ToString("0.00")</td>
            <td>@day.Totals.Overtime.ToString("0.00")</td>
            <td>@day.Totals.Doubletime.ToString("0.00")</td>
            <td>@day.Totals.Sick.ToString("0.00")</td>
            <td>@day.Totals.Vacation.ToString("0.00")</td>
            <td>@day.Totals.Holiday.ToString("0.00")</td>
            <td>@day.Totals.Overall.ToString("0.00")</td>
        </tr>
    }
</tbody>
<tfoot>
    <tr>
        <th>Totals:</th>
        <th></th>
        <th>@user.Totals.Regular.ToString("0.00")</th>
        <th>@user.Totals.Overtime.ToString("0.00")</th>
        <th>@user.Totals.Doubletime.ToString("0.00")</th>
        <th>@user.Totals.Sick.ToString("0.00")</th>
        <th>@user.Totals.Vacation.ToString("0.00")</th>
        <th>@user.Totals.Holiday.ToString("0.00")</th>
        <th>@user.Totals.Overall.ToString("0.00")</th>
    </tr>
</tfoot>

Now, those values add up to 80 even. Even that's .02 higher than the actual value.

Comments like `Please help me, Stack Overflow. You're my only hope.` really should be edited out. But there is something about pleading in geek that makes me want to +1 this. — crthompson, Apr 29 '14 at 22:05
Oh, come on. It doesn't take away from the quality of this post *at all*. — keeehlan, Apr 29 '14 at 22:05
You are showing how you display the values, but not how you calculate them in the first place. That code is needed to diagnose the issue. — Eric J., Apr 29 '14 at 22:08
Unfortunately, I already troubleshooted that. I ran some tests on the calculating code and it does indeed add up to `79.98`. @EricJ. — keeehlan, Apr 29 '14 at 22:09
Perhaps @kehrk you miss my point. However, more to the answer, you should probably use decimal instead of float. As Eric says we need to see the calculation code to see why you're getting errors. — crthompson, Apr 29 '14 at 22:09
It's OK, I know what you mean. But yes, I did try using `decimal`, and unfortunately this data is come from LINQ to Entities, and `decimal` does not match up with SQL Server's `float`. @paqogomez — keeehlan, Apr 29 '14 at 22:10
Your string format is already displaying only 2 decimals, what's the point of all the truncation? — jamesSampica, Apr 29 '14 at 22:13
I don't understand how you are expecting the sum to be accurate if you're truncating numbers. — Paolo Moretti, Apr 29 '14 at 22:13
I actually only added that bit after reading a few answers on Stack Overflow. Eliminating the truncation and using only `.ToString("0.00")`, the values added up to `80` even. @PaoloMoretti — keeehlan, Apr 29 '14 at 22:17
The reality is the underlying numbers cannot be accurately shown as 2 decimal places. Consider if you have: `1.004, 1.004, 1.004` but displayed as `1.00, 1.00, 1.00`. Your displayed/manually calculated sum is obviously `3.00`, but if you sum the underlying values you'll have `3.012` (which will display as `3.01`). I'm not sure _what_ you _want_ to do here. Perhaps you should be displaying each row's value to more significant digits. — Chris Sinclair, Apr 29 '14 at 22:27
To be honest, this is an internal web app for generating reports on employees' logged time, as you could probably tell. I know that the inaccuracy is *extremely minimal*, but I'm trying to please the bosses *and* our payroll girl. So here we are. @ChrisSinclair — keeehlan, Apr 29 '14 at 22:28
@kehrk: If you like, perhaps you can make it so if you mouse-over you can show the value to the full set of significant digits. EDIT: Or rather, since this is employee's _time_, perhaps you should collectively decide to round to the nearest minute for each row (or always round up, or use banker's rounding, or whatever) and your calculated total should be the addition of those rounded numbers. EDITx2: You should be using data types and rounding that _makes sense_ for the work you're doing. Does it make sense to log 8 hours, 10 minutes, and 2.349866666784 seconds? Perhaps use `decimal` instead? — Chris Sinclair, Apr 29 '14 at 22:30
Do you have the option to change your sql server column from `float` to `decimal`? — crthompson, Apr 29 '14 at 22:35

score 4 · Accepted Answer · edited May 23 '17 at 12:22

4

So here's the real question - should the "total" be the total of the original numbers or the truncated numbers. (As a side note, ROUND would reduce the error but not eliminate it.). If the total should be the sum of the original numbers then you can either:

Leave the report alone and explain in a footnote that the total may not match exactly due to rounding.
Add a row for "rounding error".
Allocate the rounding error to one or more of the items (here's a question and argument I had on how to do that)

If the total should be the total of the rounded numbers, then that's easy to do in the report, but it won't match the total of the original numbers (unless you round the original numbers in the source).

edited May 23 '17 at 12:22

Community

1
1

answered Apr 29 '14 at 23:09

D Stanley

149,601
11
178
240

This is a good set of suggestions. I'll be taking this to our payroll girl and my boss to come to a solution. – keeehlan Apr 29 '14 at 23:18
Since you're dealing with payroll #3 is probably a bad option. Hopefully the consumers of the report understand that there may be rounding errors. – D Stanley Apr 29 '14 at 23:23
I think they do. It's just a matter of explaining it and making sure that they understand what's going on. – keeehlan Apr 29 '14 at 23:30

score 3 · Answer 2 · answered Apr 29 '14 at 22:24

You really should read this paper:

David Goldberg. 1991. What every computer scientist should know about floating-point arithmetic. ACM Comput. Surv. 23, 1 (March 1991), 5-48. DOI=10.1145/103162.103163 http://doi.acm.org/10.1145/103162.103163

Abstract Floating-point arithmetic is considered as esoteric subject by many people. This is rather surprising, because floating-point is ubiquitous in computer systems: Almost every language has a floating-point datatype; computers from PCs to supercomputers have floating-point accelerators; most compilers will be called upon to compile floating-point algorithms from time to time; and virtually every operating system must respond to floating-point exceptions such as overflow. This paper presents a tutorial on the aspects of floating-point that have a direct impact on designers of computer systems. It begins with background on floating-point representation and rounding error, continues with a discussion of the IEEE floating point standard, and concludes with examples of how computer system builders can better support floating point.

But the problem most likely is that your are either truncating or rounding the values for display and computing the sum of the raw values. Hence the delta.

If you want them to match:

sum the displayed values
use decimal instead of double.

This is not a floating-point error problem. The OP is truncating the components to 2 digits and trying to match the total of the components to the total of the _truncated_ numbers. — D Stanley, Apr 29 '14 at 23:04

score 2 · Answer 3 · answered Apr 29 '14 at 22:18

2

The real issue here isn't string.Format(). The issue here is Math.Truncate().

Allow me to demonstrate:

Math.Truncate(4.499999 * 100) / 100 = 4.49
Math.Round(4.499999, 2) = 4.50

Now .01 is a relatively small amount but your way of calculation is what's causing the issue here. Your example has 14 rows. If every row was wrong, your display would be wrong if people were to calculate it themselves (even though your total is correct).

answered Apr 29 '14 at 22:18

techvice

1,315
1
12
24

I'm not sure how that helps. I do not want rounding, I want an exact `string` representation of a highly-precise `double` value that's being pulled from a SQL Server `float`. – keeehlan Apr 29 '14 at 22:21
floating point values aren't highly precise: by design, precision is traded for range. – Nicholas Carey Apr 29 '14 at 22:26
@kehrk You want to show a highly precise double but only want 2 decimals. Well then, the Math.Round() method should suit your needs. – techvice Apr 29 '14 at 22:29
I'll give it a shot. Hopefully I'm just misunderstanding the logic behind `Math.Round()` and putting it into practice will shed some light on it. @techvice – keeehlan Apr 29 '14 at 22:33
Actually, nope. It doesn't work. It causes the totals of my values to add up to `80` instead of the correct `79.98`. @techvice – keeehlan Apr 29 '14 at 22:40

string.Format() on double values gives inaccurate results

3 Answers3