Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel FAQs: Rounding

by
15th Apr 2011
Save content
Have you found this content useful? Use the button above to save it to your profile.

A round up of tips and tricks to help you cope with Excel’s arithmetic foibles.

Why are Excel calculations sometimes incorrect?
An Any Answers thread from May last year highlighted a perennial question about Excel: how do you sum rounded numbers and end up with a figure that is maybe a pound or two out?

The issue is particularly prevalent for accounts, who often want to display accounts rounded to single figures, thousands or even millions - which are much easier for users to interpret. As David Carter explained in a two-minute tip on Excel rounding the easiest way to do this is to use a Custom Format. When you reach the menu option (Format-Custom menu up to Excel 2003; Layout/Format tab in the 2007/2010 Ribbon), look for the #,###0;[Red]-#,###0 option within the General menu (it’s at the bottom of the list in Excel 2003).Insert a full stop, then a zero, then a comma after each of the Zeros to get: #,###0.0,;[Red]-#,###0.0,

When the format is applied to a spreadsheet cell, the number will round to the nearest thousand.

As AccountingWEB users gsgordon explained, the calculation anomalies occur where the displayed figure of the result has been rounded, but the calculation source numbers were not. “Formatting cells only affects the display of cell contents, and does not round the numbers used in calculations.”

Another member, ats28, moaned, “I've often thought that thousands of hours of work would be saved if everyone just accepted that roundings sometimes produce an apparent difference. After all, each individual number and total is correct (as rounded).”

But dig a little deeper into the issue, and you’ll discover that it goes back to the quirks of computerised floating point calculation. As well as causing anguish for overworked accountants, rounding can pose logical problems, particularly for those who decide to use the Precision as Displayed option to work around the obstacle. This article offers a quick summary of the underlying science, plus a collection of practical pointers on rounding suggested by AccountingWEB’s community of Excel users.

How does Excel rounding work?
Microsoft staff and Excel experts including Simon Hurst, Gail Perry and Chip Pearson have all grappled with the technicalities of rounding. In his summary, Pearson explained that Excel normally stores numeric values as double precision floating point numbers (or doubles for short). To calculate large numbers, microprocessors store them as 8-byte variables that are accurate up to 15 decimal places.

When you change number formats in Excel, the spreadsheet normally retains the full decimal values in its memory. So a sum such as:

23.49
23.49
46.98

if shown without the decimals visible will result in:
23
23
47

Is there an easier way to display numbers the way I want?
According to AccountingWEB member jumpalongjim the easiest and most correct way to handle number roundings in Excel is to retain the fully accurate number values and select a formatting option to display numbers the way you want. While the result will not always scan as accurate, it will be logically consistent.

What’s the trouble with Precision as Displayed?
Lee Stevens and others have pointed to an option that will make your spreadsheet add up correctly based on the numbers you see on a workshieet. It’s called Precision as Displayed. In Excel 2007, click the Office button in the top left corner and select Excel Options. Select Advanced on the left hand menu bar, then scroll down to “When calculating this workbook' and tick Precision as Displayed. In Excel 2003 and prior Look for it on the Tools-Options menu and tick Precision as Displayed the calculation tab.

As the name suggests, Precision as Displayed resets all the number values on the worksheet to the number of decimal places that are displayed, so the numerical remnants within the decimal places you choose to omit are also cut from any subsequent calculation - potentially compromising the accuracy of your worksheet.

But Simon Hurst warned: “Precision as displayed changes the precision to what you see - if you only want to deal with round pounds, for example, and nothing significant beyond that. But if you don't see decimals, they have ceased to be. It affects the whole workbook and unless you've got a back up, there's no going back.”

In a case documented by Patrick O’Bierne, spreadsheet error researcher Ray Panko highlighted a market research where two sales forecast spreadsheets with 15,000 cells were rounded off to whole dollars using Precision as Displayed. But the process also reinterpreted an inflation multiplier of 1.06 as 1, effectively underestimating the market value by $36m.

What do Excel’s Rounding functions do?
Because of the Precision as Displaye issue, Excel experts including Simon Hurst advise users to rely on Excel’s ROUND function and its variants instead. Some of the more useful ones are listed below. The rest can be found by clicking the “fx” icon just to the left of the formula box at the top of the worksheet and looking through the Math & Trig options for more details.

ROUND (Number/cell reference,num_digits) - see also ROUNDUP and ROUNDOWN
In a 2006 online tutorial Tim Birkett experienced an anomaly in a tax computation where the rounded figure in the Profit subject to Corporation Tax did not add up correctly. The calculation was based on subtracting the Capital Allowance sum of 4,582.50 from Net Profits, Goodwill and Amortization (72,583). Excel correctly rounded the result up to 68,002, giving an incorrect answer.

ROUND is for situations like this that Microsoft added the ROUND function to Excel, explained Simon Hurst. ROUND retains the value of the original number but displays it in a cell rounded to whatever number of decimal places you specify (zero in Birkett’s case).

Round takes two arguments - the number to be rounded and the number of decimal places to round to. 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 prevent the format displaying three zeros, Hurst suggested inputting the following definition - noting that each section of the format ends with a comma:
#,###,;[Red]-#,###,

MROUND (number/cell ref,multiple)
MROUND returns a number rounded to the desired multiple. (May require Analysis Tookpack Add-In for pre-2007 versions of Excel). For example:
A1 6.7 B1 MROUND(A1,0.5) generates 6.5
A2 6.8 B2 MROUND(A2,0.5) generates 7.0

CEILING(number, number)
If you want to control the extent to which Excel rounds your numbers, the CEILING function can round up to the nearest 100 or 1,000 (or evena million), suggested damflyn. It’s very useful for management accounts reports, and saves typing in  +1 or -1 into some of the formulas. If the formula is linked to another cell then it is =CEILING(A1,1000). If used in conjunction. As Alan Falcondale noted, CEILING can also be used in conjunction with FLOOR(number,number) to further control the way figures are rounded.

TRUNC(number, num_digits)
This function, an abbreviation of “truncate” function is used to remove the decimal part of a number to a set number of digits. In doing so, however, it does not round the remaining decimal places or whole numbers up or down.

As with the other rounding functions, the argument bit in brackets refers first to the value to be truncated (which can also be a cell reference), and the second element is the number of decimal digits to be displayed. INT does a similar thing, but removes all decimal places leaving only the whole number or interger.

Further reading

ExcelZone Compendium: Formulae & functions - rounding
Excel rounding and the Precision as Displayed predicament
Is there a global 'round' option?
Rounding in Excel
Simon Hurst online tutorial
Rounding errors in Excel - Chris Pearson's explanation
Why does Excel Give Me Seemingly Wrong Answers? - Microsoft blog
Precision as Displayed - PDF extract from Patrick O’Beirne’s book, 'Spreadsheet Check and Control'

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.