Puzzle for accountants and other excel users

[Seagull over NZ]

OK, got an odd one for you.

In cell A1 type the value 25.52. In cell B1 type 161.71. In cell C1 type 187.23

Hopefully you will agree that 25.52 plus 161.71 equals 187.23.

Now in cell D1 type the following formula:

=IF(ROUND(+A1+B1-C1,2)=0,C1,+A1)

Basically this is saying that if A1 and B1 added together equal C1, then take C1, else take A1.

Now, if you remove the round function and put in cell E1 the following formula:

=IF(+A1+B1-C1=0,C1,+A1)

So tell me, why do you get 2 different answers when you haven't typed in more than 2 decimal places?

Is this an excel bug??

Sorry its a bit dull but this has caused about 5 professional qualified accountants a right nightmare !!

 

[Everest]

Calculators rule.

 

[Redhead]

Give me a minute i'll blow the dust off me abbacus

 

[Gritt23]

I think it is a bug, as my formulas often seem to do that if I stick an "=0" in there. Change the "=0" to "<0.00001" or some such tiny decimal and it should work ok.

 

[Easy 10]

I just KNEW you'd be straight onto this Gritty. Moth to the flame.

 

[dwayne]

...definitely an excel bug.

 

[Vankleek Hill Seagull]

Try this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

or this

http://support.microsoft.com/default.aspx?scid=kb;en-us;214118

 

[chip]

...definitely an excel bug.

No, not a bug.

Essentially you are testing a floating point number for equality with zero. As this is represented by an exponent and mantissa, it is unlikely that after addition and subtraction the result will be exactly zero due to rounding error (precision) in the representation of the original numbers. The suggestion to test against a small number is wise in this case. The use of round merely converts the floating point number to an integer.

What do you expect from accountants? Not the ability to understand numbers I hope?

These sort of errors have been known to cause fatalities. In Inertial navigation systems, these errors can add up to tens of miles over the course of a long-haul flight. If you studied computing in the 70s or 80s this would be one of the first things you where taught. Hope that helps.

 

[dwayne]

show's how much I know

cheers chip

 

[chip]

The benefits of learning Fortran years ago

 

[Cian]

The benefits of learning Fortran years ago

 

[Marc]

I thought it was gonna be something FUNNY, like you put all that forumla in and you gte a picture of a bird wapping her breasts out whilst singing the national anthem for Bolivia!

 

[1066gull]

And that's why I hate Microsoft.

 

[Cian]

And that's why I hate Microsoft.

Despite this having NOTHING WHATSOEVER TO DO WITH MICROSOFT, MONKEY BOY?

Floating point 'problems' are inherent in ALL computer systems.

 

[Shropshire Seagull]

And that's why I hate Microsoft.

Oh dear .....

Good to hear that "floating Point" and "Mantissa" issue again - I remember that rearing its ugly head on the early 286 PCs in the eighties - oh they were the days (before Windows)

 

[Woodchip]

He's right to hate Microsoft though. Just need to check his facts first.

Not sure who I hate more though, EA or Microsoft.

 

[clapham_gull]

Decimals cannot be expressed in binary exactly and thus stored in the computers memory exactly.

This problem occurs because of the precision of your computer, which must represent and manipulate numbers in binary. Microsoft Excel compares the exact binary representation of the numbers rather than their decimal equivalents, which are displayed on the screen. Therefore, rounding errors can occur in the binary representation of the numbers that are not evident when comparing the decimal values visually.

The best way round it is to do all your calculations in pence - ie drop the decimal point.

 

[Woodchip]

And never ever ever ever ever ever ever ever ever ever try and calculate time with excel. Now there is a headache!!!

 

[clapham_gull]

And never ever ever ever ever ever ever ever ever ever try and calculate time with excel. Now there is a headache!!!

Ok if you convert everything to seconds first...

Or even frames (25 a second), which I the way I have to use it.

 

[Simster]

Actually, I'd say it was a bug in Excel. Sure, that problem is one that exists in all systems - but it has been ironed out in some applications, noteably the Microsoft Calculator application.

I suppose to get round this issue, you can always write your own version of ROUND in VBA...