Microsoft Office 2010 Expert, Office 2007 Expert, software speaker, software training, consulting, software tips & tricks, Office 2007 trainer, Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Microsoft Access, Microsoft Outlook, tips, Microsoft Office 2007 trainingSoftware Tips & Tricks, Expert Speaker, Microsoft Office 2010 Training, Microsoft Certified Trainer, Microsoft Office Consultant, Books, Videos, Web Training 
 Home Products Programs Meet Dawn Free E-zine Media Room Contact 
Excel: ROUND Functions  

Technology Trainer
Software & Tech Speaker
Software Consultant
Tips & Tricks
The Pro Shop
Free Software Articles
How-To Lessons
Tips Newsletter
Bonus Resources

 
Dawn Bjork Buzbee
The Software Pro


Microsoft Certified Trainer (MCT)

Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor

Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor

Microsoft Certified Application Specialist (MCAS) Instructor

Certified Microsoft Office Expert

Certified Women's Business Enterprise (WBE)
WBENC Certified

WOSB (Women-Owned Small Business) Certified

(303) 699-6868
Dawn@SoftwarePro.com

ROUND Functions in Excel

For any version of Microsoft Office Excel including Excel 2010, 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 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:

  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 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).

    excel 2007, decrease decimal icon, formatting numbers, rounding, decimal places
     
  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.

excel, rounding, functions, excel errors, formatting in excel, round function

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?

excel, numbers, microsoft excel, formula, excel functionsYou 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.

rounding, Excel functions, round function, example of the round function in Excel

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.

nested functions in Excel, round function, if function, microsoft excel functions, microsoft office excel, rounding

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.Go To Top of Page

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.

excel, roundup, rounddown, excel functions, microsoft excel 2007

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.


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

Go To Top of Page
 



Tips, Tricks, Secrets you need to know now!
Discover the secrets you wish you knew before...all of this plus 15 BONUS SHORTCUTS and access to handouts with 477+ MORE SHORTCUTS AND TIPS when you
sign-up for the Smart & Easy Software Tips & Tricks eMagazine!

Start Being More Productive Today!
CLICK HERE to
sign-up by email.
Home for Dawn Bjork Buzbee, The Software Pro
Contact Dawn Bjork Buzbee, The Software Pro
Hot Excel Tip
Press the [F2] function key to edit the active cell.
 
Did You Know?
Excel 2003 allows up to 7 nested levels in a formula.

Wow! Excel 2007 now allows up to 64 nested levels in a formula!

 
www.SoftwarePro.com
HomeContact Us | Training | Speaking | Consulting | Services | Tips & Tricks | Products | How-To Lessons | Free Articles | Newsletter | Resources | Site Map

Copyright 1995-2013 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 Trainer (MCT)
Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor
Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Specialist (MOS) Master Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE) WBENC Certified
WOSB (Women-Owned Small Business) Certified
The Software Pro
is a registered trademark
Terms & Conditions       Privacy Policy