ROUND Functions in Excel

For any version of Microsoft Office Excel including Excel 2007, Excel 2003, and Excel 2002

Excel Flaw or Hidden Truths?

The Problem with Excel Formatting

In the past month, I've received 3 separate e-mail messages from clients and readers about a Microsoft Excel "problem", that is, each one asked a similar question, "Is there a fix or patch for Excel? It looks like some of my formulas are wrong because I get a different answer when I manually calculate the same values."

First, although there have been previous "bug" fixes or service packs to update Microsoft Excel, those aren't needed in this case. Have you heard of the acronym, WYSIWYG (pronounced wizzy-wig)?

WYSIWYG = What You See Is What You Get

In some Excel worksheets, however, WYSIWYG means What You See ISN'T What You Get. The frustration begins when you can not duplicate the Excel answers when you manually calculate visible values.

See It for Yourself

To try a simple example of how Excel formatting can change a displayed value but not the "real" value:

1. Open Microsoft Excel (any version).
2. In a new worksheet, in any cell, type this number: 1.987654
3. Select the cell and click repeatedly on the Decrease Decimal toolbar/icon (Excel 2007: on the Home Tab/Number Group; Excel 2003: located on the Formatting toolbar) and watch the changes to the display until the value shows as 2.0 (see example below).
   
   
    
4. Look in the formula bar to verify that the original value hasn't changed; this value is what Excel uses in formulas. The displayed number of 2.0 is what your readers will likely use if they audit your work.

TIP #1: When you select a popular number formatting option in Excel like currency or comma, Excel will round the displayed results to the specified number of decimal places such as 2 or 0. The obstacle is that these formatting actions do not change or round the value behind the result to the same number of decimal places. Instead, Excel retains the full value as it is passed on to other formulas in the worksheet.

Disclaimers Aren't the Solution

Have you seen or used disclaimers in worksheets that state "errors may occur due to rounding?" With statements like these, how can the readers of your Excel data trust the accuracy and reliability of the results and the decisions based on this information?

You may not have encountered this problem if the numbers you use in Excel are only whole numbers or limited to dollars and cents (substitute your country currencies). Many accounting and financial applications, however, include percentages, fractional shares, and other values that may calculate out to many more decimal places that you choose to display. These calculation problems are common, for instance, in oil and gas and other industries where volumes, lease ownerships, prices, and other factors will often be quantified out to 3, 6, 12 or more decimal places.

To give you an idea of how critical this issue can be, I was hired by an oil analyst for an "emergency consultation" when a shareholder threatened to sue the company because the numbers on his property expense worksheet literally "didn't add up."

What's the Answer?

To change your Excel formulas so that the displayed answers match the values behind the results, add the ROUND function to your formulas. Using the ROUND function, you can calculate your values using the same number of decimal places that you want to display in your worksheet.

The basic structure for the ROUND function is:

    =ROUND(formula, # of decimal places)

The official Excel lingo is =ROUND(number, num_digits)

The ROUND function can be easily applied to even very complex formulas. Just add it as the outermost function to any formula as shown in the formula below.

With some Excel functions and calculations, the ROUND function may also be nested within other functions. Here is an example of the ROUND function included in the arguments (parts) of the IF function to insure that the result is calculated to the correct number of decimal places.

TIP #2: The number of decimal places in the ROUND function should always match the number of decimal places chosen for the formatting of the cell. This way, you can actually create worksheets and formulas that are WYSIWYG.

To see an example of how formatting and the ROUND function impact your Excel formulas:

   Excel Example: ROUND function in Excel.xls

TIP #3: Even though some formulas may create correct results without the ROUND function, be consistent so that you can automatically rule out rounding problems when auditing worksheets.

ROUNDUP & ROUNDDOWN

Head 'Em Up, Move 'Em Out! What do you think of when you hear the term Roundup? If you've seen any Western movies or TV programs or live in the Western USA like I do, then you might be thinking of cattle herding and not of an Excel function.

The ROUNDUP function and its buddy ROUNDDOWN are specialized variations of the ROUND function. The assumption with ROUND is that anything 5 or higher will round up to the next number, for instance, if the number 1.316 was rounded to 2 decimal places, it would change to 1.32. As the names imply, the ROUNDUP function always rounds up and the ROUNDDOWN function always rounds down regardless of the value.

When would you use the ROUNDUP function?
This might be your preferred choice when calculating pricing or other values where consistency is key.

Create the ROUNDUP function with:
   =ROUNDUP(number, num_digits)

When would you use the ROUNDDOWN function?
Imagine that you are a small business owner that is tracking the number of vacation hours earned by employees based on the number of hours that they have worked. With this function, you insure that an employee does not receive more vacation than they have earned. Now, if you are the employee, you might want to keep this idea to yourself.J

Create the ROUNDDOWN function with:
   =ROUNDDOWN(number, num_digits)

To look at a simple Excel example of each of the rounding functions, open this sample workbook:

   Excel Example: More ROUND functions in Excel.xls

Add the ROUND functions to your Excel tricks to uncover the important values that are hiding in your work.

Copyright 1995-2008 The Software Pro^® & Dawn Bjork Buzbee - All Rights Reserved
(303) 699-6868 § Dawn@SoftwarePro.com § 11675 Bent Oaks Street, Suite 200; Parker, Colorado 80138 USA § www.SoftwarePro.com
Geek Speak Becomes People Friendly™
Microsoft Certified Application Specialist (MCAS) Instructor (Office 2007 & Windows Vista)
Certified Microsoft Office Specialist Master Instructor § Certified Microsoft Office Expert § Certified Women's Business Enterprise (WBE)
The Software Pro^® is a registered trademark