Excel can't add!?

fester

Am I missing something here - or is this just wrong?!




I'm using Excel 2003 SP3

Overheal

try ;

or you know, =(B1+B3)

cython

fester wrote: »

Am I missing something here - or is this just wrong?!




I'm using Excel 2003 SP3

Technically, yes, it is giving you the wrong answer, but I think that this is simply due to the way floating point numbers (i.e. numbers with a decimal point like the ones you are working with) are stored in computers. Without getting into mind-numbing detail of the sort that is used to explain this to people studying it, for the vast majority of floating point numbers, computers are only able to store approximations to the numbers, because of the binary representation (see http://en.wikipedia.org/wiki/IEEE_754-2008 for more details, and further resources) having a finite range of numbers available. Thus some of the numbers you are working with may not be stored exactly in memory, rather they are represented as the closest number that can be represented.

Couple that with the potential for overflow/truncation/etc when you are working with numbers of vastly different magnitudes (which 72.99 and 2422500 are), and you have situations whereby the limited set of numbers on computers can introduce errors, as you are observing.

I realise that this may not make things much clearer to you, but I lectured a course in this before, and the notes I was given to explain it to 3rd year engineering students took up pages and pages, so I can only fit a very limited explanation in a post on here!

Fysh

I managed to recreate this behaviour in Excel 2007. I'm guessing it's something to do with how you're formatting the cells and how many decimal places you're setting. (I've also just reproduced it in OpenOffice.org 3 Calc, with the cell formatting set to 15 decimal places and 7 leading zeroes).

Interestingly in OpenCalc, if you use the formula B4=SUM(B1:B3) you get the correct result, but if you use B4=SUM(B1;B2;B3) you get the same erroneous result.

A bit of googling on the subject reveals the cause of the problem - open Excel help and search for "Excel limits", then look for an entry called "calculation specifications & limits" (or at least that's the name in Excel 2007). Numerical precision is accurate to 15 digits. Setting the cell formatting to use 15 decimal places means that you're working with 22-digit numbers in this example; thus, the calculation you're performing is correct to within the specified limits.

All of that being said, I'm very curious to know what kind of transactions you would need to process regularly in Excel that involve numbers greater than 15 digits with 15 decimal places. You'd want to be a very confident scientist (whether theoretical or experimental) to be claiming that your numerical results are correct to within 22 significant figures

Edited to add:
Microsoft KB article on the topic here.

fester

Thanks guys, makes for some interesting reading. It's good to know this limitation. And I guess I can live with it

Cheers

mukki

wow,

hope i earned more last year then excel says i did