+- MacResource (https://forums.macresource.com)
+-- Forum: My Category (https://forums.macresource.com/forumdisplay.php?fid=1)
+--- Forum: Tips and Deals (https://forums.macresource.com/forumdisplay.php?fid=3)
+--- Thread: Excel thinks 7 + 7 + 3 = 18...what gives? (/showthread.php?tid=89008)
Pages:
1
2
Excel thinks 7 + 7 + 3 = 18...what gives? - StingMe - 12-05-2009
Currency-formatted cells - why is the value of the third decimal place taken into account for the final calculation? Shouldn't it be dropped?
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - N-OS X-tasy! - 12-05-2009
New Math.
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - N-OS X-tasy! - 12-05-2009
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.
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - Dick Moore - 12-05-2009
Apparently not. But honestly, I've never used currency formatting, so i don't know what to expect.
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - Winston - 12-05-2009
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
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - StingMe - 12-05-2009
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
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - Gareth - 12-05-2009
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)
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - NewtonMP2100 - 12-05-2009
.....low SAT scores.....?
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - MGS_forgot_password - 12-05-2009
You must be using the federal government edition of Excel.
Re: Excel thinks 7 + 7 + 3 = 18...what gives? - Jimmypoo - 12-05-2009
Burn it! Witch! Witch!