ROUND Functions in Excel
For any version of Microsoft Office Excel including Excel 2010,
Excel 2003, and Excel 2002
Excel Flaw or Hidden Truths?
The Problem with Excel Formatting
In the past month, I've received 3 separate email 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:
- Open Microsoft Excel (any version).
- In a new worksheet, in any cell, type this number: 1.987654
- Select the cell and click repeatedly on the Decrease Decimal
toolbar/icon (Excel 2010 & 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).
- 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
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.
Head 'Em Up, Move 'Em Out! What do you think of when you hear
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
Create the ROUNDUP function with:
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:
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.
Be an Excel Expert!
How effectively are you using Excel to create, format,
and analyze your important data?
Become an Excel expert with the
Excel with Excel
Learning Series. Master functions, filtering,
linking, PivotTables, charts and graphics, macros, and more!
Click Here to
Choose Your Excel Training