Formatting Excel Spreadhsheets
Go to: Previous Article Next Article
The challenge with formatting Excel
I regularly obtain e-mail messages from consumers and contacts about a Microsoft Excel "problem", that may be, each and every one particular asked an identical problem, "Is there a resolve or patch for Formatting Excel? It appears like several of my formulas are mistaken because I get an unique reply after i manually determine precisely the same values.
"First, while there are preceding "bug" fixes or provider packs to update Microsoft Excel, these aren't desired in this case. Have you heard about the acronym, WYSIWYG (pronounced wizzy-wig)?
WYSIWYG = What you See Is That which you Get
To attempt an easy case in point of how formatting excel can transform a shown value although not the "real" worth:
Inside a new worksheet, in any cell, style this number: 1.987654
Select the cell and click frequently to the Decrease Decimal toolbar/icon (Excel 2010 & Excel 2007: around the Home Tab/Number Group; Excel 2003: located within the Formatting Excel toolbar) and watch the changes to the display until the worth shows as 2.0.
TIP #1: When you pick a popular number formatting Excel option like currency or comma, Excel will round the displayed results to the specified selection of decimal places such as 2 or 0. The obstacle is that these formatting Excel actions do not change or round the value behind the result to precisely the same amount of decimal places. Instead, Excel retains the full worth as it is passed on to other formulas in the worksheet.
Disclaimers Are not the Solution
Have you ever seen or used disclaimers in worksheets that state "errors may occur due to rounding?" With statements like these, how can the reviewers of your Excel data trust the accuracy and reliability on the results and the decisions based on this information?
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 mainly because the numbers on his property expense worksheet literally "didn't add up.
"What's the Reply?
The basic structure for the ROUND function is:=ROUND(formula, # of decimal places)The official Excel lingo is =ROUND(variety, num_digits)The ROUND function can be easily applied to even very complex formulas. Just add it as the outermost function to any formula, such as =ROUND(S5*(H5+J5),2).
=IF((W5*100)>0,ROUND((W5*100),0),ROUND((-W5*100),0))
TIP #2: The variety of decimal places in the ROUND function should always match the amount of decimal places chosen for the formatting excel cells. This way, you can actually create worksheets and formulas that are WYSIWYG.
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.
Add the ROUND function to your Excel tricks to uncover the important values that are hiding in your work.
Article Source: Articlelogy.com
- Credit Cards A big selection of Cards in all flavors: Bad Credit Cards, Secured Cards, Prepaid Cards, Credit Cards for Canada, Low Interest Cards, etc -
Word Count: 512
Reduce Your Debts Without Bankruptcy. See How Much You Can Save. Free Debt Analysis
