Excel thinks 7 + 7 + 3 = 18...what gives?

[StingMe]

Currency-formatted cells - why is the value of the third decimal place taken into account for the final calculation? Shouldn't it be dropped?

[N-OS X-tasy!]

New Math.

[N-OS X-tasy!]

OK, seriously...

The sum value of the third decimal place is causing the total to round up. Currency formatting doesn't change a number entered in a cell - it simply formats it to display with a dollar sign and to two decimal places.

[Dick Moore]

Apparently not. But honestly, I've never used currency formatting, so i don't know what to expect.

[Winston]

You should get the same answer as if you multiplied the subtotal by 1.2, which is 16,403.676. Rounds to what you got.

If you don't want it to work that way there is probably a truncate function you could use. But I'd rather have it give me the same answer regardless of how I did the math.


Good luck.

- Winston

[StingMe]

So Excel will round the number for display but all available decimal places are used in any calculations...can't believe I've never paid much attention to this.

Here's a workaround...by checking "Precision as Displayed" in the Calculation Preferences calculations will be based on only what is displayed. This is a workbook-level preference setting:



Doing this leads to 7 + 7 + 3 = 17 so I'm happy for now

[Gareth]

ROUND(number,num_digits) will force a cell to round to a given decimal place and thus have WYSIWYG within individual cell(s). Just replace number with the formula you are calculating and num_digits with the number of decimals you want.

i.e. =ROUND(.1*A1,2)

[NewtonMP2100]

.....low SAT scores.....?

[MGS_forgot_password]

You must be using the federal government edition of Excel.

[Jimmypoo]

Burn it! Witch! Witch!