Why Can't Numbers Subtract Correctly?

Hi TheTubbyOne,

Welcome to Apple Support Communities!

Welcome also to the way that computers handle floating point arithmetic. This is not a bug in Numbers, just a fact of life. That is how computers convert "human" (decimal) numbers to binary and back to decimal.

A Screen Shot of a decimal subtraction and an integer subtraction:

Use the ROUND function in the final formula (don't keep 'rounding' at each step). Let Numbers handle the arithmetic in the intermediate steps.

Compare that to the integer subtraction in Row 3.

Tested in Numbers 3 and with pencil and paper 🙂.

Regards,

Ian.

This topic has been covered many times in these forums. Numbers (and all other programs on all other computers) use the sam specification for how a numeric value is represented on a computer. In all cases there are small, well-known errors when you show all decimal places.

Try the same computation on MS Excel... you;ll get the same difference between your expected value and the true value.

Try LibreOffice... same thing

Try Excel on a Windows computer... same thing

the specification is discussed here:

https://en.wikipedia.org/wiki/Floating_point

You can also search for the spec:

IEEE-754

The correct answer is to round results using the round function or format the numeric values in cells to the number of places you need.

HI T1,

You wrote: "I have tried the same computation in Excel and LibreOffice at your request, and to my point they both have made considerations for the discrepancy (whether intentional or not)."

"Made considerations," perhaps, but the consideration appear to be only cosmetic. The underlying value in the cell is the same, judging by this example from Apache OpenOffice. See notes on this and the similar table made in Numbers ('09) below:

Formulas:

C1: =A1-B1 D1: =A1-B1

C1: Column C was made wider to accommodate more digits. The "+" places button in the toolbar was used to increase the number of decimal places until the first 0 appeared, then the "X" places was clicked once.

D1: Places after the decimal were left at the default value.

C2, then filled right to D2 and down to row 26: =C$1*10^ROW(). Both columns were then widened to accommodate the calculated results.

Numbers example below uses the same formulas. Formatting is slightly different.

Left at the default width of 1 inch, and the default formatting setting of 'Automatic,' cells C1 and D1 both displayed the result as 0.3. As Column C was widened, the displayed value changed to the result shown.

Entering the formula shown above for the OpenOffice example, and filling it right and down as described there gave the results shown in Numbers.

With one exception, the cells of columns C and D in the Numbers table are left at the default "Automatic" formatting, with Numbers determining the number of places following the decimal. The exception is in the selected cell, where I've changed the cell format to "Number." As can be seen, the result in this cell is the same as that obtained by OpenOffice—both applications displayed rounded results in this column until the point where there were fewer decimal values than they were set for, then started displaying it differently—OO as the actual conversion result, Numbers as the same number as before, but now in scientific notation, and with its end obscured by the cell overflow marker on the right.

The thing that interests me here, though, is that although OpenOffice (and presumably Libre Office and MS Excel) are showing your expected result, they are still using the IEEE-754 standard result in their calculations, as can be seen in rows 14 to 26 of the Open Office screen shot.

Regards,

Barry