UKC

I've been on this massive spreadsheet all day and my head is gone.  I've got an odd result that I'm not sure is a bug, a setting or I'm doing something stupid.  This is right at the end and I'm just validating some end figures

Col A = 72.73

Col B = 81.45

Col C = B-A

The answer is 8.72 right?

If I try the same formula with A=72.73 and B=81.46, I should get the answer 8.73 but the cell is showing 8.729999999999

Likewise if I try it with A=72.73 and B=81.47, I should get the answer 8.74 but the cell is showing 8.739999999999

It's 'displaying' as correct, but inside the cell is where I'm seeing the recurring 9's....problem is these are part of a much larger aggregation so the numbers may affect the overall total.

Both A and B are definitely formatted as 2DP before I run the formula.  I've actually manually typed them in as they appear...and the formula is still being a dick

This is something obvious, right?

Have you tired formatting c to 2 dp as well?

All the cells are formatted to 2DP so they 'appear' on the screen fine, but it's when you click in the cell and view the actual value that you see the .xx9999999999 bit.

It's going to be stupidly simple.  But I'm too fried to work it out.

I've blasted through the column with a bit of VBA, done the subtration in the code, got the correct answer and plonked that in the cell.  it seems to have done the trick...I'm not going to delve any further in case I upset the gods of Microsoft...

The display formatting of A and B won't affect the value stored in the cell, you'll need to round them if it's important that they are accurate to 2dp.

The results you're seeing are just the cumulative effect of the various operations performed in the spreadsheet which give results to many decimal places.

If the 2dp is arbitrary then the result you get is more accurate than if you use 2dp throughout (notwithstanding that this may be a spurious level of accuracy depending on the input data). If it's financial calculations, or there is another similar reason to need 2dp then use the ROUND function where appropriate.

There are other reasons why you can get odd looking results, but they're a bit nerdy and probably not relevant here.

Rounding errors due to finite precision storing most non whole numbers.

Just like you can’t write 1/3 perfectly as 0.3333, a computer can’t store many non whole numbers perfectly - being base 2, it can store perfectly 1/2, 1/4, 1/8, 1/16 etc and combinations of those.  So pennies - for example - stored as a fractional part of a number with pounds are regularly mutilated behind the scenes by finite precision not in base 10 but base 2, and these rounding errors can accumulate.

It’s normally swept under the carpet but sometimes it pokes it’s head and bites you.

Edit: it’s not going to bite you here so long as you don’t have numbers billions of times different in magnitude, and so long as you don’t compare any amounts for exact equality.

I imagine formatting to 2 dps rounds the contents as expected for display.  Rounding the numbers may not fix the problem as the rounded number may not be precisely storable in base 2.

Bit odd that excel is expanding the value for display, but there you go.  Proper grown up financial tools use a different way of storing numbers that whilst slower doesn’t have these rounding errors.

It seems like you're confusing formatting with rounding?

Just because something is formatted to 2 decimal places it doesn't mean the value being stored in the cells (that will be used for any calculations) is being rounded.  If you want Excel to round at any stage then use the ROUND function - https://support.microsoft.com/en-gb/office/round-function-c018c5d8-40fb-405...

But they're very simple numbers.  they shouldn't need rounding at all should they?

81.47-72.73 = 8.74

But it's showing as 8.7399999

(and this is on a fresh spreadsheet, no formatting, rounding applied).

But like I say...the solution I've cobbled seems to have worked...touch wood. In need sleep

Simple numbers in decimal are not simple numbers in binary.

1/3 is 0.33333.... in base 10 so not a simple number to represent.

In base 3 a third is 1/10 and it is a simple number to represent as 0.1 in base 3.

That seems like a floating point precision error. Which can happen with long series of operations although not sure how you are getting it with a simple hardcoded value.

What version of excel are you on?

That's not how Excel works.

If you have a cell =A1-B1 and it shows 8.74, when you click on it you see the formula.

I think you've either done something silly or have some macro doing this.

Sorry - I also did a  "paste special values" on it.   So I've removed the formula, and just left the value behind.

Office 365,,,

Initially it was a long series of operations leading to the two operands, so I thought that could have been the problem.  But I've just gone into a fresh workbook, typed

72.73 into cell A2,

81.44 into B2,

"=B2-A2" into C2

Copied and paste special values the results of C2 into itself.

And it's showing 8.70999999999999

There is no problem.  It’s pasting the actual value it had.  You must have exposed some quirk about when it decides to show the real value rather than a rounded one.

It’s not long series of operations at fault.  A single calculation involving two non whole numbers is all it takes.

Set the formatting to round and sweep it under the carpet.  It’s always been there until now, you just didn’t know it...

It is safely swept under a heavy rug now, with a large desk moved on top of it.

If I put those numbers in (Excel Office Pro Plus 2016), is shows 8.74 in the cell, and 'A1-B1' in the formula box. That's with the cell formatting as 'General'.

When you say 'showing', where exactly is it 'showing' as 8.7399999?

Aha! If I copy the result and paste the value into a cell, it does appear as 8.73999999999999 in the formula box, but 8.74 in the cell.

If the second value is 72.72, the result, in both cells and formula box, is 8.75.

It's not you, it's Excel.

I'm doing a paste special value on it.  It then shows the .xx9999 in the formula box / formula bar

Yes, I spotted that after posting, and have edited my post. It's a floating point representation issue. It's just an unlucky value that makes that issue apparent.

looks like its excel

maybe multiple everything by 100 to start so they are all integers, not really what you were after but ...

Just to show that it was there all along, I made a similar spreadsheet, and rather than doing a "Copy, Paste Special > Values, I selected the cell containing the formula and did "Format Cells > Number : Category > Number" and whacked up the decimal places.  Low and behold, the abominable rounding error was there all along, but being hidden by whatever the default display format is, something I am suddenly a bit uncertain on...

Where this tends to catch people - having an imprecise value rounded to a precise one for display, and not being aware of it, is when you do equality comparisons.  In the second screenshot, I've got one cell containing the result of the subtraction, and the cell below it containing the "same" number that I typed in.  Both are compared to the same number manually typed in to a formula.  The one I typed in returns "TRUE" and the one resulting from the subtraction returns "FALSE" because, as we now know, the value behind the scenes is minutely different.  Imagine trying to understand what's going on there without having learnt about finite precision rounding errors!  It's classic cause of undergraduate integration code going round a while loop one to many times and introducing a small error.  Which is one of the reasons why I rage against anyone teaching a while loop for definite stuff.

Never compare non whole numbers for equality, compare them for proximity to the test value within a small amount larger than rounding error, perhaps 10^-10 of their magnitude.

Hmm.. when I do this, the sample shows as 8.710000000000, not 8.70999999999

So it does for me too; I should have tried your numbers.   Try my numbers - I just made some up that weren't obvious base-2 friendly.  

If I change your 13.17 to 13.18, I get 0.86999[...] instead of 0.87000

All this just goes to show how fickle and odd the behaviour of these numbers are in computers.

The worst thing about finite precision rounding errors on floating point numbers  is that maths is no longer associative.  Try pasting these in to Excel:
= (1e20 + 1) - 1e20
= (1e20 - 1e20) + 1

People use computers day in day out to do maths in all sorts of important and some times critical systems.  Many of them have no idea of the horrors that lurk a few key presses away.

Thanks for taking the time to replicate and explain, it's much appreciated...my faith in excel is suddenly on shaky ground. 

Do they work at Fujitsu, developing financial systems for the Post Office...?