Rounding can often be a problem in a spreadsheet. We’ll start with some relatively simple background stuff then move on to some lesser-known aspects of rounding and displaying rounded values.
What you see isn’t what you calculate – mostly!
The first thing to grasp is that the way Excel displays the contents of a cell generally has no effect on the actual contents of that cell as used in calculations. So a value of 100.05678 will remain as 100.05678 even if the format of the cell is set to display no decimals. There is one important exception to this. There is an option in the ‘Calculations’ tab of the Tools, Options screen that forces Excel to change the contents of the cell to match the ‘precision’ of the format. So if the cell is formatted to display no decimal places, the contents will be permanently rounded to no decimal places – our 100.05678 would become 100.00000:
Before turning on ‘Precision as displayed’
This is an option to be used with great care. It applies to the entire workbook and as soon as it is turned on potentially decreases the accuracy of any figure displayed to fewer decimal places than it actually contains.
A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:
The ‘sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)
More fun with rounding
As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:
To change the format to display numbers rounded to thousands you would use the following custom format:
The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.