Excel calculations off by a 'fraction of a decimal'

[caustria08]

First off, I want to thank anybody who takes the time to read this question and apologize if the topics within this question are available in other threads. I wasn't able to find a thread based on any keywords imaginable from my perspective.

I'd like to compare a wide range of data. For my spreadsheet I used VLOOKUP to pull two numbers; more specifically the exact numbers of 76.1 & 76.

Then I have Excel subtract the difference between the two, which should be simply 0.1, which is a target number that I need to match.

The cell displays "0.1" but when I double click on the value it doesn't display 0.1. Instead it displays 0.0999999999999943. Sometimes Excel seemingly does this on some computers and on others it doesn't. Why does this happen? I want to say it must be a setting I'm completely unaware of at the moment.

Now, I did try the ROUND function. However, as you can imagine, when I apply the ROUND function to every other calculation I suddenly see that a bunch of my other target numbers don't match because of differences in rounding.

Anybody have any ideas of what's going on here?

[MrShorty]

It's not a setting, it is just an inherent part of the way computers (not just Excel) store numbers and do arithmetic: http://support.microsoft.com/kb/78113

The ROUND() function is one way to deal with this in Excel, or there are any number of other strategies. The key is to recognize that all computer calculations are "off" by this little bit, and we as programmers need to be aware of it and be prepared to deal with it.

[Ron Coderre]

Computers work with binary representations of numbers, but humans work with decimal numbers. There are some decimal numbers that cannot be exactly represented in binary. It's the same kind of issue that prevents the fraction 1/3 from being exactly represented as a decimal number.

Microsoft has this website on "How to correct rounding errors in floating-point arithmetic":
http://support.microsoft.com/kb/214118

I hope that helps.

[LJMetzger]

Most of us think in decimal, and computers mostly think in binary. Real numbers (numbers with decimal points) have a special format that they are stored in. Most decimal numbers can't be stored exactly in binary, that's why you get .9999... This is especially true when doing subtractions of numbers close to each other in value. Unfortunately, you are stuck with the ROUND function or something similar, or you may even save the value as text.

Lewis

[Elomaldo]

Hello Caustria,

What about if you round the numbers first, =round(Cell,0), and then do a pivot table, and make a vlookup from the pivot table.

Not sure if this one helps.

Thanks

[6StringJazzer]

In general, computers use binary arithmetic and have limited precision. In the same way that we cannot write the result of 1/3 in an exact decimal, some numbers cannot be represented exactly in binary. This causes problems when trying to compare two floating point numbers for exact equality. This is a topic that gets intense study by programmers doing numerical analysis. You can get different results on different computers if the computers have different hardware precision, or other differences in handling floating point numbers (I am not an expert at hardware implementation so I can't give much of an explanation there). In software, if two floating numbers are being compared they are generally compared to match within some tolerance, rather than compared for absolute equality.

Please Login or Register  to view this content.

When you double click, it should display the formula. How can it display 0.0999999999999943? How did that number get in the cell, if not by a formula?

Without knowing more about your data I can't suggest why the rounding approach isn't working for you.

[joeu2004]

Last things first....

Now, I did try the ROUND function. However, as you can imagine, when I apply the ROUND function to every other calculation I suddenly see that a bunch of my other target numbers don't match because of differences in rounding.

You should only round values when (at the time) you expect them to be accurate to some number of decimal places. And you should round to that number of decimal places.

Sometimes that means rounding a cell reference when it is used in a formula instead of rounding the value in the cell itself.

If you follow those rules literally and consistently, you should not encounter "a bunch of other numbers that do not match" due to rounding. Conversely, if you encounter that problem, it is because you did not follow those rules literally and consistently. Either you rounded when you shouldn't have, or you rounded to the wrong number of decimal places.

For example, if 76.1 really represents dollars and cents, you should round 76.1 - 76 to two decimal places even though 76.1 has only one decimal place.

For another example, suppose you sum a column of unrounded calculations, and the rounded sum is what you expected. Then you round each of the calculations in the column, and the rounded sum no longer matches expectations. The mistake might be in your expectations; for example, the sum of rounded percentages often does not equal 100%. Or the mistake might be that you should not round each calculation. Usually, I do not round intermediate percentage and interest calculations.

So clearly, whether or not to round and to what precision is a judgment call. By the way, that is one reason why the "Precision as displayed" option (see below) is a bad idea: it applies to all cells without judgment.

(For the General format, PAD rounds to 15 significant digits, which is often not the original value exactly.)

I used VLOOKUP to pull two numbers; more specifically the exact numbers of 76.1 & 76. Then I have Excel subtract the difference between the two, which should be simply 0.1, which is a target number that I need to match. The cell displays "0.1" but when I double click on the value it doesn't display 0.1. Instead it displays 0.0999999999999943. Sometimes Excel seemingly does this on some computers and on others it doesn't. Why does this happen? I want to say it must be a setting I'm completely unaware of at the moment.

You are correct. Simply stated, if we enter 76.1 into A1, 76 into A2, and =A1-A2 into A3, A3 displays 0.0999999999999943 if we format it as Number with 16 decimal places.

If you see this on some computers, but not on others, the difference might be how the "Precision as displayed" option (PAD) is set. In particular, it is set when you do not see the problem. But it is important to note that setting PAD is usually not a good idea. In fact, it is a dangerous idea!

However, PAD is a workbook setting. So for a given Excel file, you should see the same behavior on all (personal) computers.

So I suspect your "some computers" observation is really "some numbers" or "some calculations", and the difference has nothing to do with PAD or the computer.

Aside.... If you truly find that the same Excel file behaves differently with the same numbers on some computers, let me know which computers.

The reason why 76.1 - 76 does not match 0.1 might be clear if you could see the exact values. This is difficult to see because Excel arbitrarily formats only up to 15 significant digits. The exact values are (comma demarcates 15 significant digits):

Please Login or Register  to view this content.

Looking at how the 0.1 part of 76.1 is represented, the result of 76.1 - 76 should be no surprise. But we can also see that the 0.1 part of 76.1 is different from the constant 0.1.

The different representations of 0.1 are due to the physical limitations of 64-bit binary floating-point, the way that numbers are represented internally. In particular, numbers are represented by the sum of 53 consecutive powers of two ("bits") times an exponential factor. Consequently, most non-integers cannot be represented exactly.

In the case of 76.1, 7 bits are needed to represent 76. That leaves only 46 bits (powers of two) to represent the 0.1 part. In contrast, the constant 0.1 can use all 53 bits; in fact, 10 more powers of two (for complicated reasons).

TMI? The point is: the constant 0.1 can be represented more precisely than the 0.1 part of 76.1.

That is why we should always round to a required number of decimal places (usually much fewer than 15 significant digits) when we expect accuracy to that number of decimal places. We cannot depend on the appearance of numbers.

[mehmetcik]

There is an option in excel, Set Precision as displayed, under advanced settings.

Try using that.

[Ron Coderre]

Just be aware that using the "Set precision as displayed" option changes the actual values (not just the displayed values). That can introduce a whole other class of errors in a workbook.

[joeu2004]

There is an option in excel, Set Precision as displayed, under advanced settings. Try using that.

Setting "Precision as displayed" (PAD) is a dangerous idea. Usually, it is a bad idea.

It should never be suggested without a long list of caveats.

Topping the list: make a copy of the Excel file before experimenting with PAD, since setting PAD might change constants irreversibly if the format of the cell has less precision than the constant value.