Rounding in excel based on format

Is it possible to round as displayed?

Didn't find your answer?

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.

Out of my mind
By runningmate
26th Sep 2016 14:25

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.

Thanks (1)
Replying to runningmate:
Morph
By kevinringer
26th Sep 2016 14:35

Thanks runningmate - this is exactly what I need.

Thanks (0)
avatar
By paulwakefield1
26th Sep 2016 17:15

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!

Thanks (2)
Replying to paulwakefield1:
Simon Hurst
By Simon Hurst
26th Sep 2016 18:48

...as dangerous as a pint glass full of wasps in a dark pub garden on a warm September evening...

Thanks (1)
Replying to shurst:
By Democratus
27th Sep 2016 09:12

I like that simile

Thanks (1)
Tom Herbert
By Tom Herbert
27th Sep 2016 13:50

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.

Thanks (1)
avatar
By James_Whitelegg
29th Sep 2016 13:37

ASAP Utilities has a similar function which can be used just on a range.

Thanks (1)