Numbers calculation glitch?

The number in cell D2 is not 1.3. If you go to the format sidebar and allow it to show more decimal places, you will see the actual number in that cell. It is 1.27285...

Take a look at your formulas.

D2 = B2/C2

F2 = D2*C2 which is (B2/C2)*C2 = B2

so F2 had better be the same number as in B2 (and it is).

On row 3 you plugged in D3 as exactly 1.3 (which is not B3/C3) so you got a different result in F3

If you need D2 to be exactly 2 decimal places use =ROUND(B2/C2,2)

"So even though I told Numbers to USE 1 decimal place, it still calculated the actual decimal number… "

If you 'told Numbers' using the Cell Format Inspector, you did not tell Numbers to USE one decimal place; you told Numbers to DISPLAY one decimal place.

In the example below. the number 65.4321 is entered into each of the cells in column A of the table.

Column B contains the formula =A2 in cell B2, filled down to B6.

In cell A2, the Decimals: value has been left as "Automatic"

Cell A4 (selected) has the settings shown in the screen shot. The other rows have Decimals set to the number of decimal places places displayed in that cell.

Column C has the formula shown in C1. B in the displayed formula represents "B2" in row 2, B3 in row 3, etc.

With the number of decimal places NOT set for these cells, the formula would use the same display format assigned to the cell from which it was getting the number to be multiplied by 1, making the product look like the number displayed in column B

But the number actually calculated is the product of the actual number in that row of column B and 1, which can be revealed by setting the cells in column C to show all four decimal places (as I've done in this example).

Telling Numbers how to display the number does just that.

To tell Numbers to use the actual number rounded to the nearest hundredth, you need to tell numbers to ROUND the number before doing the calculation. That's done by including the ROUND function in your formula:

In the example above, I've included only the ROUND part of a possible formula, and set the decimals to Automatic.

Column B's formula has also been changed to get the value from column A, round it to two decimal places, then multiply the result by 1.

All cells containing numeric values in this table no have Decimals set to 'Automatic' and show the actual number entered (column A) or the actual result of calculations carried out by the formula in that cell (columns B and C).

Regards,

Barry

gregoryfromkeller wrote:

Thank you SGIII... Result ended up same... Badunit's response seemed to alleviate the error. Numbers seems to be calculating the decimal result versus the rounded result.

Ah, yes, I see what you meant now. It's natural to assume that Numbers is using the numbers you see to do its calculations. But spreadsheet software has been around a long time and things unfortunately don't always work that way!

Just keep in mind that what's "under the hood" can sometimes be a little different from what you see. To control what's under the hood you sometimes need to use the ROUND, ROUNDUP, ROUNDDOWN, MROUND, and TRUNC functions. You can find out more about these functions when you need them by going in your menu to Help > Formulas and Functions Help. These are in the Numeric Functions section.

SG

It was simply a misunderstanding about cell formatting (what you see) vs actually rounding the value in the cell. It is a common misunderstanding. The choice to be made is whether you want the intermediate results rounded so what you see in each cell is the actual value in those cells (and the math all "looks" right) versus not rounding the intermediate results and having the final result be the most accurate (but the math based on what is displayed can look wonky, as you saw).

If you are multiplying then you shouldn't use the SUM function.

To multiply D2 by C2 just enter D2*C2. You could also use the PRODUCT function but it's not necessary.

SG