Excel Tip of the Week #349 - Rounding redux

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a General User level post in which we are making a definitive examination of all things rounding-related in Excel.  This was last covered as a two-parter in TOTW #252 and #254.

What's all the fuss about?

Excel, like all computer programs, naturally works in binary, encoding each number as a string of 1s and 0s that represent powers of 2.  It converts and displays these values to decimal for human consumption on-screen.  But there is a limit to how much information a single cell can contain - fifteen significant digits.  Add a sixteenth and Excel will keep a record of if that changes the size of the number, but it won't actually record the sixteenth digit:

Of course, other kinds of calculations also don't express exactly in decimal (and/or in binary), and so inexact answers are stored instead - e.g. thirds.  While Excel has some additional rules built in to try and correct certain common kinds of issues, these aren't perfect:

The error in the third example - where a 0.00000000000000355 difference has been created where non exists - is called a floating point error; see TOTW #289 for more on those.

And of course, as accountants we often work with currency amounts, which even if e.g. translated from a foreign currency can't have more than two decimal places of detail.  So we often need to round numbers - whether it's to aid readability, to ensure proper representation of currency amounts, or to prevent floating point errors from causing slips.

The tools available to us

There are four major ways to round numbers available in Excel:

Type Examples Notes
Round via formatting Reduce decimal places Doesn't change the number itself - just how it is represented on the screen
Decimal place rounding ROUND, ROUNDUP, ROUNDDOWN Rounds the number to a certain decimal place
Multiple rounding MROUND, CEILING.MATH, FLOOR.MATH Rounds the number to the nearest multiple of some base
Precision as displayed Excel option setting Rounds the number stored to match the formatting chosen - not recommended

There's a longer discussion of precision as displayed in TOTW #244 but the long and short of it is that this functionality, which automatically "chops off" numbers to match how they are formatted, is very dangerous.  It's easy to accidentally and permanently lose critical detail by a simple formatting change or format paste.  Don't use it!

In most cases, just changing the formatting is sufficient.  While little floating point or other rounding errors might exist, these are usually tiny and won't cause issues, and keeping the results of calculations precise is the best way to go.

But if you do need to round, round last.  Rounding numbers at each and every stage of a calculation increases inaccuracy, by repeatedly dropping the precision of the numbers being stored.  Keep the rounding for the latest possible stage.

Here are the decimal rounding functions:

=ROUND(number, number of decimal places)
=ROUNDUP(number, number of decimal places)
=ROUNDDOWN(number, number of decimal places)

Number can be the number to be rounded itself, a cell reference to where that number is, or a nested calculation that produces the number to be rounded.
Number of decimal places is self-explanatory - a number to indicate how many decimal places the figure should be rounded to - e.g. 2 for 2 dp, 0 for round numbers, or -3 for round to the thousands place.

The only difference between the three functions is the direction they round in - ROUND rounds value >=5 up and <5 down, and the other two do what they say on the tin.

For comparison, here are the multiple rounding functions:

=MROUND(number, multiple)
=CEILING.MATH(number, multiple, mode)
=FLOOR.MATH(number, multiple, mode)

Number is the same as before.
Multiple is the base that the number will be rounded to - e.g. 0.5 or 1/3.
Mode is an option that affects how the latter two functions round negative numbers:

  • 0 - negative numbers round away from / toward 0
  • any other number - the reverse

Rounding with retained totals

A final word on rounding - and that is to address the frequent goal of "rounding but keeping totals the same".

It's understandable where this desire comes from - after all, statutory accounts have to add as written, but the figures presented are usually rounded to the nearest thousand or million.  But, of course, the sum of rounded numbers and the rounded exact total won't always agree - and in fact can be very different indeed.  But the most commonly used approach, which is to have one sub-category (usually the largest) be a balancing figure, causes much the same problem:

Here if we had rounded and then added, we would have a total of £52k - which would undershoot by £2,000.  But by using a balancing figure, instead we move the inaccuracy onto the balancing figure - which is now overstated by over £2,000.  There is no way around this - rounding by its nature introduces inaccuracy, and that inaccuracy has to live somewhere.  The third option - rounding the numbers presentationally and accepting that the column won't add by eye - keeps all the accuracy but sacrifices consistency.  This is simply a choice you have to make.

Previous post - Fixing badly formatted dates
TOTW index 
Next post - Power Query: Creating a ranking

Anonymous