Round function

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:

#,###,

for 1,235k

#,###,k

for millions

#,###,,

for 1m

#,###,,”m”

The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.