>>Back to eMagazine Archives
>>Back to The Software Pro Home
============================================================
Smart & Easy Software Tips, Tricks & Techniques: Issue #46
Publisher: Dawn Bjork Buzbee mailto:Dawn@SoftwarePro.com
The Software Pro(R), Certified Microsoft Office Expert
Microsoft Certified Application Specialist (MCAS) Instructor
(Office 2007 & Windows Vista expertise)
http://www.SoftwarePro.com
============================================================
In This Issue: Excel Flaw or Hidden Truths?
* The Problem with Excel Formatting
* Try This
* Can Your Data Be Trusted?
* The ROUND Function
* Roundup: Head 'Em Up, Move 'Em Out
============================================================
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 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
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 such as 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.
============================================================
Special Notes Aren't the Solution
============================================================
Have you seen or used the footnote or footer in a worksheets, "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 where volumes, lease ownerships, prices, and other factors will often be quantified out to 3, 6, 12 or more decimal places.
============================================================
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.
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 examples of how formatting and the ROUND function impact your Excel formulas, head to:
http://www.softwarepro.com/howto/exl_round_functions.htm#round
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: 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 it's 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.
For more details and examples of these functions, go to:
http://www.softwarepro.com/howto/exl_round_functions.htm#roundup
============================================================
Add the ROUND functions to your Excel tricks to uncover the important values that are hiding in your work.
=====================================================
Got Content?
=====================================================
Do you have a company website or newsletter? Would your
organization benefit by knowing effective ways to use their
software and technology?
You are welcome to download or reprint any of the articles on
The Software Pro(R) website for your newsletters and website.
For terms and more information >>
http://www.softwarepro.com/articles.htm
------------------------------------------------------------
Please pass this along to others who may appreciate it!
------------------------------------------------------------
Past newsletter issues are found at:
http://www.SoftwarePro.com/archives.htm
For Website or e-zine questions: mailto:Dawn@SoftwarePro.com
If you are receiving this issue as a forward, and want
your own subscription, visit
http://www.SoftwarePro.com/subscribe.htm
Privacy Policy - We hate receiving sp^m as much as
you do. Therefore, we never rent, trade, or sell
our email lists to anyone for any reason. You or
someone you know subscribed you to this e-zine list.
We never add names to our list until you voluntarily
subscribe.
(c) 2008 The Software Pro(R)
http://www.SoftwarePro.com
To Subscribe go to:
http://www.SoftwarePro.com/subscribe.htm
============================================================
Geek Speak Becomes People Friendly(tm)
Dawn Bjork Buzbee
The Software Pro(R)
11675 Bent Oaks Street, Suite 200
Parker, CO 80138 USA
(303) 699-6868, Fax (303) 766-0886
mailto:Dawn@SoftwarePro.com
http://www.SoftwarePro.com