Sum Formula acts weird when added text to it

Question

I have a macro and one of it's command is to create Formula to sum things up. Everything was fine until today when someone showed me problem.

Sum Function should give me: 36286,54. When function looks like this: =SUM(I7:I128) everything is ok but when I want to add some text to it like this: =SUM(I7:I128) & " CZK" it gives me: 36286,5399999999 CZK

I double checked values, nothing seems to be wrong... Problem is not caused by creating formula via macro, same problem appears doing it manually. I investigated that those "99999999" at the end appears around 50th value when adding it using 'For Next' loop. Deleting some values makes "99999999" disappear but I have no idea why excacly those.

Everything was done on data below, any ideas what is wrong?

3 883,60
5 679,75
1 633,05
7 825,67
8 541,85
892,50
313,00
887,44
99,52
277,69
138,84
138,84
138,84
277,69
277,69
138,84
34,71
34,71
34,71
74,30
74,82
130,43
34,72
34,72
212,74
99,52
99,52
149,74
-2,74
212,74
212,74
212,74
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
13,88
34,71
34,71
69,42
31,24
31,24
31,24
31,24
31,24
31,24
31,24
31,24
41,65
41,65
41,65
10,41
10,41
20,83
20,83
20,83
20,83
20,83
13,88
3,47
6,94
15,62
57,10
190,90
10,67
34,71
138,84
138,84
277,69
55,50
34,71
86,78
86,78
166,61
13,88
67,83
117,39
117,39
67,69
62,48
8,68
135,37
104,13
69,42
69,42
14,75
15,62
15,62
15,62
31,24
46,86
62,48
26,10

Answer 1

It's most likely because of gradual errors introduced during the accumulation of all those value. I suspect that, if you instead used:

=SUM(I7:I128) - 36286,54

you'd get a very small, but non-zero, number. And indeed you do, if you examine the graphic below. To solve it, I'd suggest rounding the value explicitly before tacking on the currency indicator:

=ROUND(SUM(I7:I128),2) & " CZK"

Here's the proof of concept in Excel 2k13. With the C cell formulas being:

           C
1  =SUM(A1:A122)
2  =SUM(A1:A122) & " CZK"
3  =ROUND(SUM(A1:A122),2) & " CZK"
4  =C1-36286.54

The results is:

So you can see from C4 that the error between the actual value and the displayed value is about 0.00000000008, a vanishingly small percentage.

As to why that happens, according to Microsoft's documentation, Excel uses standard IEEE754 data types under the covers and it's a well known issue that those numbers do not have infinite precision.

Why it seems to work when you don't append a string, I don't know. Maybe Excel does some more intelligent rounding of sums at the end (perhaps based on the input data characteristics) when you're not forcing the type to be coerced to a string.

In any case, the explicit rounding seems to fix it so, being a pragmatist/realist, I'd just go with that.