Microsoft Excel rounds: defect or hidden truth?

Excel formatting issue

My frequent contacts for client, Microsoft Excel issues from each one similar question ", but either the hotfix or update Excel. When the e-mail message I receive to manually calculate the same values get different answers for some seems to be wrong in my formula. ?

Is to update the "bug" before modifying or Microsoft Excel, service packs, first they are not required. Is the acronym WYSIWYG (pronounced nifty wig) or have heard.

WYSIWYG what = what you get to see

However in some Excel worksheets, is not WYSIWYG to see meaning in what you get. Start a frustration to replicate Excel answer to manually calculate the visible values can not.

See for yourself.

Values are "true" to examples of changes to set the Excel format rather than the value that you can try to.

Open Microsoft Excel (any version). New worksheet, select cells: 1. 987654this number in a cell repeatedly, click the decrease decimal toolbar icon (number of Excel 2010 and Excel 2007: Home tab groups; Excel 2003: located on the formatting toolbar) and 2. See changes to the display until the value is displayed as 0. Look in the formula bar to verify that the original value has not been modified; this value is used in Excel formulas. The number 2. Likely readers display 0 to audit your work if you use.

Of Tip # 1: results like commas or currency and to choose a popular number formatting options in Excel, Excel is displayed rounded decimal number, such as 2 or 0. Value and obstacles change the formatting of these operations or not behind the result of the same number of decimal places to round is. Maintains a full value that is passed instead of formulas in other worksheets in Excel.
?
Not the solution of the disclaimer.

See disclaimer or "due to rounding error may or may? "And uses it in the States worksheet. Like these statements how the accuracy and reliability of results is based on this information decision reviews of Excel data can we trust?

This problem occurs if you have the number only whole number you use in Excel or limited to dollars and cents (replaced the currency of your country). Many applications, however in the accounting and finance, percentage, constituting might include other values might compute shares to see many more decimal places. The problem of these calculations is to quantify the volume, lease ownership, pricing, and other factors appear immediately-3, 6, 12 or more decimal digits is common, for example, oil and gas industry is other.

Can how important this issue to give an idea, he was hired by oil analysts, "urgent consultation" when shareholders threatened numbers on the property expenses worksheet literally "adding not so. Companies to sue

What is the answer?

To change the Excel formula to match the value behind your answers show formula adds ROUND function. To view worksheets in the same number of decimal places using the ROUND function to use, you can calculate the value.

Is the basic structure of the ROUND function.

= ROUND (formula, # of decimal places)

Is the official Excel term = (number, precision) rounds.

ROUND function is a very complex formula can apply easily. As a function of the outermost expression, such as ROUND (S5 * (H5+J5), 2) = adds.

Some Excel functions and calculations, the ROUND function can be nested in other functions also. Here, the result is an example of the round function to guarantee calculation to correct number of decimal places contained in the argument of the IF function (parts).

= IF ((W5 * 100) > 0, ROUND ((W5 * 100), 0), ROUND ((-W5 * 100), 0))

Of Tip # 2: The number of decimal places in the ROUND function always scale cell formatting choices to match. This way you can create formulas in spreadsheet and WYSIWYG in fact.

Of Tip # 3: problems when to audit a worksheet automatically creating a correct formulas that result in the ROUND function rounding is consistent, so you can dominate.

ROUND functions are important values are hidden in your work to reveal, so add Excel trick.

Dawn Bjork Busby Microsoft Office Specialist (MOS) master certified instructor and certified expert certified instructors, Microsoft application professionals (paid), Microsoft Office and as the software Pro ? and Microsoft Certified Trainer (MCT) is. Dawn software speakers share smart ways to use software effectively through her work as a trainer, consultant, and author of six books. Software for more tips, tricks, strategy and technology the found at http://www. SoftwarePro.com.