Excel number/currency/accounting formats will round to the chosen number of digits, but the underlying value remains un-rounded. I'm forever coming across errors because of the differences and having to insert =ROUND all over the place. It would be handy if excel had a function that when a value is displayed as rounded, excel automatically rounds the actual cell contents. Does anyone know of such a function. I mean that if a VAT gross to net formula returned 23.5826 which is displayed as 23.58 the cell contents would also alter to 23.58.
PS Sift - what tags should I have used for this post?
Replies (7)
Please login or register to join the discussion.
When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. If you later choose to calculate with full precision, the original underlying values cannot be restored.
1.Click the File tab, click Options, and then click the Advanced category.
2.In the When calculating this workbook section, select the workbook you want and then select the Set precision as displayed check box.
As runningmate has pointed out, this is a workbook wide setting and there is a loss of accuracy. IMO it is one of the most dangerous options available in Excel. :-)
Use with extreme care!
...as dangerous as a pint glass full of wasps in a dark pub garden on a warm September evening...
Thanks for the question Kevin. As far as I'm aware you've used the correct tag. I might have a word and see if we can get ExcelZone compendium shortened to 'Excel'. I feel this would make more sense.