Excel problem!?

Real numbers are represented on computers in floating-point representation, typically IEEE 754.

0.1 cannot be represented exactly in this format. With double precision 64-bit floats, the precision is about 15-16 decimal places.

Your formula is recursive, increasing in depth with each row downward. Each increase in depth serves to magnify the round-off error. The accuracy starts at 15-16 decimal places, but decreases with each row.

In your spreadsheet, increase the number of digits displayed to 18 decimal digits. You will see the error increasing with each row.

Addendum: In answer to the additional question,

0.125 can be exactly represented in IEEE floating point format because 0.125 is a power of 2.

0.125 = 1/8 = 2^(-3)

Any power of 2 can be exactly represented as long as it is in the range of the exponent, about 10^(-308) to 10^308.

Furthermore, any number that can be represented exactly with 52 bits of mantissa and 11 bits of exponent and sign bit has no initial round-off error.

It will only do that if the a1 is replaced by $a$1 i.e. becomes an absolute reference. The way you have done it will have the formula in A3 referring to A2, the formula in A4 referring to A3 etc because A1 is automatically updated when you click and drag. $A$1 is an absolute reference and will always refer to the contents of A1 whether you drag it down or across the spreadsheet

If you format the cells for maximum number of decimal places, you will see that starting in cell A3 you no longer have .1

I think this is just a computer arithmetic problem. Remember that computers basically do everything with circuits that are either on or off, and so they are basically base two calculators. Just like you can not represent 1/3 in a finite number of decimal places because it is an infinite decimal, it turns out you can not represent 1/10 as a finite base two number. So you may think you entered .1, but you have a finite approximation to it in the computer, just as .3333333 is a finite approximation to 1/3.

When using real numbers in Excel (as in virtually all computer programs) it is approximated using a reasonably large, but not infinitely large amount of computer memory. This represents a usually fairly good approximation of the number built from a base and an exponent.

In cell A1, the approximation is done fairly well and it represents the number 0.1 perfectly

When you are using the cell again however (cell C1), it does not use the value from celll B1, but it uses the reference, so your equation is actually as follows.

=(((A1*10)-0.9)*10)-0.9

Since the intermediate value in the calculation is not perfect, the output is no longer perfect, and the effect that you have seen is created.

In order to correct this, you can use the round function as below to force your level of *virtual* significant digits

=ROUND((A1*10)-0.9,1)

In my opinion _tessar_ answer is bang on the money I had to contend with a similar problem when I worked on the euro conversion problem in my old job many badly designed databases systems perform a calculations like a converting to euro from Irish pound in my case and then perform a summing operation a more correct approach is to sum then convert, else you will introduce rounding and conversion error’s you will always be slightly off but you need to ensure the error is so small as to not matter. Your example is a good example of error propagation each successive row will produce increasing in accurate results

Hi there, well you might want to make your formula absolute.

if you will always be referencing cell A1. ($A$1*10)-.09

If you click in the cells that you dragged down you'll see that they are referencing the cell directly above (which is a new calculation by this point) them and then multiplying that cell's result by 10 and subracting.09. I hope that makes sense!

Rounding problems like you discribe appear in software not just Excel they are dur to the decimal number being converted to binary for the processor to do the calculation the convertinfg the result back to decimal to display it

try using the function dec2bin() then bin2dec() it should give the same answer as the original number but as soon as use fractional value it starts going out of kilter.

Make sure you use an absolute cell reference for a1. Hit the f4 key after typing it. It should look like $a$1.

Excel has a bad rounding error.

by cell a27 = -246716227.594479.

If you set the cell format to "number" with 17 decimal places you can see the error starting in cell A3!!

Math Guy has the first question exactly correct. The reason the second example works is that there is no round off involved.