J
jabitz
I'm not a regular reader in this news group, so please excuse me if this
problem has been answered several times before.
This is in relation to an engineering application utilizing Excel. I've
been able to reduce the scope of my problem in a much more complex
spreadsheet down to a simple addition of a constant as follows.
In a first cell I have a constant numerical value of 1.12 and
repetitively add this constant to a series of numbers starting with a
value of 28.00.
1.12 (value = 1.12 in A1)
28.00 (value = 28.00 in A3)
29.12 (Formula: =$A$1+A3)
30.24 (Formula: =$A$1+A4)
31.36 .
32.48 .
33.60 .
34.72
35.84
36.96
38.08
39.20
40.32
41.44
42.56
43.68
44.80
45.92
47.04
48.16
49.28
50.40
51.52
52.64
53.76
54.88
56.00
57.12
58.24
59.36
60.48
..
..
..
When I copy and paste values of the results and examine the cell values
in the data entry line I actually see,
28
29.12
30.24
31.36
32.48
33.6
34.72
35.84
36.96
38.08
39.2
40.32
41.44
42.56
43.68
44.8
45.92
47.04
48.16
49.28
50.4
51.52
52.64
53.76
54.88
56
57.1199999999999 (would expect 57.12)
58.2399999999999
59.3599999999999
60.4799999999999
I understand Excel is limited to 15 digits of number precision, but
still do not understand the results of the values starting at
57.1199999999999.
Furthermore, this behavior is data dependent being off in the other
direction or not even occurring. For example, if the constant is
changed to 1.13 the results are,
28
29.13
30.26
31.39
32.52
33.65
34.78
35.91
37.04
38.17
39.3
40.43
41.56
42.69
43.82
44.95
46.08
47.21
48.34
49.47
50.6
51.73
52.86
53.99
55.12
56.25
57.3800000000001 (would expect 57.38)
58.5100000000001
59.6400000000001
60.7700000000001
Is this behavior simply written off as a manifestation of the "15 digits
of number precision" limitation of Excel?
I can sometimes get around this behavior by setting the "precision as
displayed" option in the preferences -> calculation pulldown, but this
doesn't always work.
Can any Excel experts comment on this and provide a method to
consistently prevent this behavior?
Thank you for any insight you can provide.
problem has been answered several times before.
This is in relation to an engineering application utilizing Excel. I've
been able to reduce the scope of my problem in a much more complex
spreadsheet down to a simple addition of a constant as follows.
In a first cell I have a constant numerical value of 1.12 and
repetitively add this constant to a series of numbers starting with a
value of 28.00.
1.12 (value = 1.12 in A1)
28.00 (value = 28.00 in A3)
29.12 (Formula: =$A$1+A3)
30.24 (Formula: =$A$1+A4)
31.36 .
32.48 .
33.60 .
34.72
35.84
36.96
38.08
39.20
40.32
41.44
42.56
43.68
44.80
45.92
47.04
48.16
49.28
50.40
51.52
52.64
53.76
54.88
56.00
57.12
58.24
59.36
60.48
..
..
..
When I copy and paste values of the results and examine the cell values
in the data entry line I actually see,
28
29.12
30.24
31.36
32.48
33.6
34.72
35.84
36.96
38.08
39.2
40.32
41.44
42.56
43.68
44.8
45.92
47.04
48.16
49.28
50.4
51.52
52.64
53.76
54.88
56
57.1199999999999 (would expect 57.12)
58.2399999999999
59.3599999999999
60.4799999999999
I understand Excel is limited to 15 digits of number precision, but
still do not understand the results of the values starting at
57.1199999999999.
Furthermore, this behavior is data dependent being off in the other
direction or not even occurring. For example, if the constant is
changed to 1.13 the results are,
28
29.13
30.26
31.39
32.52
33.65
34.78
35.91
37.04
38.17
39.3
40.43
41.56
42.69
43.82
44.95
46.08
47.21
48.34
49.47
50.6
51.73
52.86
53.99
55.12
56.25
57.3800000000001 (would expect 57.38)
58.5100000000001
59.6400000000001
60.7700000000001
Is this behavior simply written off as a manifestation of the "15 digits
of number precision" limitation of Excel?
I can sometimes get around this behavior by setting the "precision as
displayed" option in the preferences -> calculation pulldown, but this
doesn't always work.
Can any Excel experts comment on this and provide a method to
consistently prevent this behavior?
Thank you for any insight you can provide.