• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Excel can't add

C

cbrunny

Diamond Member
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?
 
Last edited:
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.
 
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!
 
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.
 
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.
 
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?
 
ignatzatsonic said:
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.
Click to expand...

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 😛
 
Fardringle said:
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?
Click to expand...

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.
 
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.
 
Fardringle said:
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?
Click to expand...

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.
 
cbrunny said:
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.
Click to expand...
I'd definitely go back then and look at your cell formatting. That can cause decimals to be displayed quite a few different ways.
 
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.
 
sweenish said:
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.
Click to expand...

I understand what you're saying, but I use Excel for a living. Literally every day. It wasn't me.
 
PricklyPete said:
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.
Click to expand...
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.
 
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.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top