Excel VBA: reading 0.1 from sheet into range results in 9.999999996e-2

Question

I have a table that I use to record measurements, all of which are increments of 0.1. The first column is just an index for the measurement, the second column is the measurement itself, then two additional columns to account for adjustments that may be needed, and another column that adds or subtracts the adjustments to the original measurement (for reference the header for this column is "Corrected Elevation"). From corrected Elevation, I calculate the most occuring measurement, "=MODE.SNGL(tbRecordedSurvey[Corrected Elevation])", and then use that value as a datum (the cell name of which is "DATUM") to adjust all the other measurements too so that my most occuring measurement is zero and I can see how far I deviate from the mode. The header for this last column is "Datum Adjust. Elevation" and the entire column has the formula "=[@[Corrected Elevation]]-DATUM". So if my intial measurement was 7.1, my most occuring measurement was 7.0, I would expect my Datum Adjust. Elevation to be 0.1. This is how it appears in the actual column.

However,

I need the data from the "Datum Adjust. Elevation" column to add to a database, so I'm using VBA to read the data. For some reason, when using an array or just a range, every instance of 0.1 or -0.1 will be read into my range or array as 9.99999996e-2, or -9.999999996e-2. This doesnt happen for any other number. If I go in and delete my formula from the column and just replace it with 0.1 then I have no issues, but that defeats the whole point of using a table in the first place. I've checked my calculation steps for each cell involved in the formula and none of them have this issue.

If I create a range in vba and set it to the specific cell as a test I get [@[Corrected Elevation]]-DATUM = 9.99999999999996E-02

But if I create ranges to check Corrected Elevation and DATUM, they read into the range just fine as 7.1 and 7.0 respectively. So its seems like all of the data going it is fine, but the result isnt, and it is only for a result of 0.1 or -0.1.

Any advice?

Answer 1

This is due to an unavoidable, as currently done, effect in the method Excel uses for dealing with the presentation of data to it as base ten numerals after which it actually converts to base two for the actual math, then back again to base ten for presentation back to you.

You will notice it is never noticed when a value is simply typed in. The problem is not with the actual conversion back and forth.It is only when arithmetic is performed on the values and the result is presented back.

It need not eb fifty steps of arithmetic. Fr the right combinations of input values, a single arithmetic operation will do the trick but... the more the merrier as additional steps mean additional opportunities for it to arise.

Excel actually maintains 27 digits of precision in an actual calculation and uses another character for signs, but then dumbs that down to 15 digits of precision in the value returned, stored, used further, and presented to you.

And in some of those cases, the result presented back is no longer exactly 0.1 but rather 0.0999999999999996.

This is, by the way, not only how Excel has done it since inception, using a convention in place in 1985 or so and venerable even then, but is also done by many other programs today so it can crop up in a large number of similar situations if one does not program for it.

The only real way past it is to not maintain unnecessary precision, which is actually a principle to follow anyway. So you overcome it by rounding results just before feeding them back into another calculation. In your case, in the columns containing interim results. You do not have to dumb it down to 0.1 and run the risk of doing just about the same as Excel is here, but rather to more digits, to make sure you do not. For short-ish calcualtion strings, I've never seen it get past that 15th digit, but if you were performing a dozen or two calculations, and did not need it to go 14 digits, I'd round at something far shorter. Carefully consider you needs, including follow-on use and how much you need.

And bear in mind, precise or not, 0.0999999999999996 is inaccurate, and inaccuracy is far, far worse on your results than losing some precision is!

A couple years ago, Excel bruted about that it was addressing the situation, and made reference to the age-old bugaboo of recording a 16 digit credit card number and losing the last digit. But in the end, they seem to have addressed that a different way and this still happens rather than it having been fixed by some new way of handling that old situation. So not much hope they intend to really change it any time soon.