Excel can't add

[cbrunny]

So... Excel 2013 thinks this is true:

78.3 + .1 = 78.3999999999999000

Formula is =E13+0.1

Where E13 is the cell directly above and has a value of 78.3. I have a long column of =CellAbove+.1, and every tenth one adds wrong.

I have to resolve this my using this formula:

=ROUNDUP((E13+0.1),1)

Which works, but is stupid that I have to do this in the first place.

Anyone know why Excel does this?

 

[PricklyPete]

I am getting old and am forgetting answers to things I used to know. I believe it has to do with the way the floating point numbers are stored/operated on. I'm sure someone will straighten out my answer.

 

[cbrunny]

I googled it and apparently it's related to something about binary numbers. I don't fully understand what it means but it is interesting if nothing else. I'm surprised though - really surprised!

 

[quikah]

Hmm, works fine for me in Excel 2013.

What are your cells formatted as?

 

[mikeymikec]

Works for me (ie. the correct answer) in Excel 2007 and LibreOffice 4.2.

 

[nickbits]

Excel:mac 2011 also works fine

 

[zokudu]

Excel 2010, works fine here.

 

[cbrunny]

Weird... I'm using 2013 64-bit, which is known to be less stable and reliable than the 32-bit. I wonder if its related to that.

 

[Chiefcrowe]

Works great here, i get 78.4. I'm on Excel 2013, 32-bit

 

[Ketchup]

...What are your cells formatted as?

I was going to ask the same.

 

[ignatzatsonic]

I've seen similar stuff, dating back to Excel 2003 or 2007.

I once had a row with manually entered values in 3 adjacent cells, such as 5, 6, and 7, side by side. Not complex values, just single digit like that.

Excel refused to acknowledge the middle cell and insisted the sum of the values was 12, not 18. Always the middle cell of 3 that was ignored.

I deleted rows, re-entered values, changed values arbitrarily, inserted a new worksheet, etc, etc. Never did get to the bottom of it. I didn't spend a lot of time Googling for an explanation. Seemed to be a one-time inexplicable deal.

 

[Fardringle]

Are you sure that the value of E13 is exactly 78.3 and not actually 78.2999999999999000 and just being displayed as rounded up to 78.3?

 

[Mushkins]

I've seen similar stuff, dating back to Excel 2003 or 2007.

I once had a row with manually entered values in 3 adjacent cells, such as 5, 6, and 7, side by side. Not complex values, just single digit like that.

Excel refused to acknowledge the middle cell and insisted the sum of the values was 12, not 18. Always the middle cell of 3 that was ignored.

I deleted rows, re-entered values, changed values arbitrarily, inserted a new worksheet, etc, etc. Never did get to the bottom of it. I didn't spend a lot of time Googling for an explanation. Seemed to be a one-time inexplicable deal.

I see stuff like this all the time at work.

Nine times out of 10 its a spreadsheet that was made nearly fifteen years ago and has been passed through four different versions of Excel by now. The fix we recommend is almost always to copy/paste the raw data into a brand new spreadsheet and specifically select the "text only, no formatting" option, then recreate any formatting and use that one moving forward.

An Excel 97 spreadsheet is "compatible" with Excel 2013, emphasis on the quotes

 

[corkyg]

I think Fardringle got it.

 

[ControlD]

Works fine on my Excel 2013 64-bit version as well.

 

[us3rnotfound]

Could be linked to shortfalls in the IEEE 754 standard: http://support.microsoft.com/kb/78113

Edit: Not sure this article describes the Excel problem here as we're not discussing huge or small numbers where we're limited to 15 digits of precision in our calculation.

 

[Dulanic]

Are you sure that the value of E13 is exactly 78.3 and not actually 78.2999999999999000 and just being displayed as rounded up to 78.3?

I think you're dead on here. I am not one to jump to Microsofts defense, but 99.9999% of the issues I see /w Excel are PEBCAK errors.

 

[sweenish]

This is similar to how the equation for a best fit line appears wrong sometimes unless you tell it to show you 10 decimal places.

Somewhere, you have a weird number.

 

[cbrunny]

Are you sure that the value of E13 is exactly 78.3 and not actually 78.2999999999999000 and just being displayed as rounded up to 78.3?

Was my first thought too, but i expanded the decimal point quite far and its 78.3. I input 78.3 manually too. Err, rather I input 77.5 or whatever 10 rows above and copied the formula down.

Doesn't really matter though. I just find it odd. Maybe I had a corrupted work sheet or something.

 

[Scarpozzi]

Was my first thought too, but i expanded the decimal point quite far and its 78.3. I input 78.3 manually too. Err, rather I input 77.5 or whatever 10 rows above and copied the formula down.

Doesn't really matter though. I just find it odd. Maybe I had a corrupted work sheet or something.

I'd definitely go back then and look at your cell formatting. That can cause decimals to be displayed quite a few different ways.

 

[sweenish]

Somewhere, there was PEBKAC.

Every time I've run into this, it was a not bad math.

Maybe some sloppy defaults in Excel, but not bad math.

 

[cbrunny]

Somewhere, there was PEBKAC.

Every time I've run into this, it was a not bad math.

Maybe some sloppy defaults in Excel, but not bad math.

I understand what you're saying, but I use Excel for a living. Literally every day. It wasn't me.

 

[Spungo]

I am getting old and am forgetting answers to things I used to know. I believe it has to do with the way the floating point numbers are stored/operated on. I'm sure someone will straighten out my answer.

Correct. It's because "float" and "double" numbers are base 2. You could say that base 10 numbers are estimated in base 2.

The easiest way to explain this is using a square root. Using a base 10 number system, what is the square root of 3? This question is impossible. You can't accurately describe this using a decimal number. You might write it down as 1.73205. Now put that in your calculator:
1.73205 x 1.73205 = what?
I'm getting 2.9999972025
What went wrong? The number system is not appropriate.

Modern programs shouldn't have this problem anymore. The newer "decimal" type of variable is more appropriate for money related calculations. Instead of using a simple base 2 number system, decimal uses base 10 expressed in base 2. What does that mean? It means lots of bit combinations are not allowed. I'll give an example. You're only allowed to have bit combinations that add up to 9.
1001 - this is the maximum number allowed for a set of 4 bits
As a decimal type, 4 bits has a maximum value of 9. If this were a regular float or double, 4 bits would have a maximum value of 8+4+2+1 = 15.

You can see how memory expensive this gets when you want one higher. How many bits does it take to represent the number 10? Since they come in sets of 4, I need 8 bits:
(0001)(0000)
Expressing the number 10 in regular base 2 would only need 5 bits:
1010

It's exactly the same as the Y2K problem. Ghetto technology leads to ghetto math. Why use 4 digit years when you can use 2 digit years? This could save millions of dollars in hardware. Why use expensive decimal types to represent non-whole numbers when you can cheap out and use binary types? They're usually close enough. Usually.

 

[riahc3]

I think your fault is a technical one with a bug in Excel. Remember that 9 + 10 is 21 so Excel might not have that bug patched.