EZ guide to rounding

Simon Hurst explains how to understand and control how values are rounded in Excel and to appreciate just how dangerous the 'Set precision as displayed' option really is.

The problem of rounding

Generally, the precision of the value used in an Excel calculation is entirely independent of the value that is actually displayed in a cell. In this example, we have formatted the same number with varying numbers of decimal places and created references to each value. As you can see, the format has no effect on the value used:

Here be dragons

As stated above, this is generally how Excel works, but there is a situation in which the precision used is the same as that displayed. In Excel options, in the Advanced category and the ‘When calculating this workbook’ section there is an option: set precision as displayed. As the description suggests, this option causes Excel to only use the values actually displayed in each cell for its calculations.

This option might appear superficially attractive but it is as fraught with danger as something that's particularly fraught with danger.

In an attempt to demonstrate just how dangerous the option is, here is a worksheet with calculations based on a VAT rate value that is held in a named cell on a different worksheet:

If we had accidentally set the format of the cell that contained the VAT rate to display no decimal points, perhaps when formatting a block of cells, then turning on ‘Set precision as displayed’ would have the following effect:

Of course, we could always turn the option off again:

As we can see, once turned on, and in accordance with the warning displayed when the option is turned on, vital precision can be irretrievably lost, possibly on some worksheet that we hadn’t even considered.

If that wasn't bad enough, even if all your cells were originally set to display the correct level of precision, if you were to accidentally reduce the number of decimal places displayed in a cell after the option was turned on, this could change your calculations without warning.

Some Excel features are reasonably safe to use, some are too dangerous to use unless you fully understand how they work, and some are just too dangerous to use.

Rounding in a formula

If Precision as displayed isn't recommended as a method of handling rounding problems then what can be done to address the issue? Excel includes a whole range of functions to deal with rounding. In this example, we want to divide £1,000 profit between three partners:

We have formatted our profit share cells in column C to display to round pounds. (For details on number formatting and controlling the display of decimal places, have a look at the EZ guide to number formatting).

Although the cells display 333, the actual value is 333.3 recurring hence the sum of the three cells rounds to 1000 not 999. We could use the ROUND() function to change this. The function has two arguments: the value or calculation to be rounded and the number of decimal places to round to:

Here we have used ROUND() to round our calculation to 0 decimal places. If we used this formula for all our profit shares we would end up with each partner receiving £333 with £1 left over at the end. We need to allocate our spare £1 somewhere, so we have calculated our third profit share as the total profit, less the sum of the other rounded shares.

Using a positive number as the second argument of ROUND() to set the number of decimal places is fairly straightforward, but you can also set the argument to a negative number. This rounds to the left of the decimal point, so =ROUND(C3,-3) will round to thousands:

In this example we have shown the use of the second ROUND() argument to round to £'000 and also included an example of a custom number format that displays numbers to £'000 by adding a single comma after the last 'digit' of the number format code.

ROUND() is just one of several functions that round values in different ways:

• ROUNDUP()
• ROUNDDOWN()
• MROUND()
• FLOOR()
• CEILING()

The following set of calculations compares the use of ROUNDUP() and ROUNDDOWN() with ROUND(). All three functions use the second argument to control the number of decimal places, so in each case we are rounding to round numbers:

MROUND(), CEILING() and FLOOR() are similar but instead of representing a number of decimal places, the second argument is used to hold a 'multiple' to round to. In the following example, we are rounding to the nearest 5p:

Power Query/Get & Transform

If you have a large number of values that you need to round, using one of the rounding functions might be a bit cumbersome. If you have installed the Power Query add-in for Excel 2010 or 2013, or if you have Excel 2016 which includes the Power Query tools as a built-in part of the Data Ribbon tab, you could create a query based on your unrounded data.

Here we have a table of data in Excel. We have used the From Table/Range command to read that data into the Power Query editor. The Number Column group of the Transform Ribbon tab includes a rounding command. The Round… option allows you to specify the number of decimal places to round to in a similar way to Excel's ROUND() function. In this case we have selected our Value column and chosen to round it to 0 decimal places:

We can then use the Close & Load command to load our resulting, rounded, table into a worksheet. Normally you would choose to load the table to a separate worksheet but here, for demonstration purposes, we have loaded the rounded table next to our original data: