Solve Common Problems of formulas in Excel 2007

Hi friends,

Will Creating Formulas in excel We normally make Mistake and Stuck over there many times. But with Excel 2007 we can Solve some of our formula.So here are the some of the Tips for solving formula related issues in Excel 2007.

Turn error checking rules on or off

1. Click the Microsoft Office Button, click Excel Options, and then click the Formulas category.
   
   
2. Under Excel checking rules, select or clear the check boxes of any of the following rules:
   
   
   
   • Cells containing formulas that result in an error
     
     The formula does not use the expected syntax, arguments, or data types. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each of these error values have different causes and are resolved in different ways.
     
     
   • Inconsistent calculated column formula in tables
     
     A calculated column can include formulas that are different from the column formula, which creates an exception. Calculated column exceptions are created when you do any of the following:
     
     -Type data other than a formula in a calculated column cell.
     -Type a formula in a calculated column cell, and then click Undo Button image on the Quick Access Toolbar.
     -Type a new formula in a calculated column that already contains one or more exceptions.
     -Copy data into the calculated column that does not match the calculated column formula.
     -Move or delete a cell on another worksheet area that is referenced by one of the rows in a calculated column.
     
     
   • Cells containing years represented as 2 digits
     
     The cell contains a text date that can be misinterpreted as the wrong century when it is used in formulas. For example, the date in the formula =YEAR("1/1/31") could be 1931 or 2031. Use this rule to check for ambiguous text dates.
   
   
   

Correct common formula errors one at a time

1. Select the worksheet that you want to check for errors.
   
2. If the worksheet is manually calculated, press F9 to recalculate now.
   
3. On the Formulas tab, in the Formula Auditing group, click the Error Checking in-group button.
   
   
   • The Error Checking dialog box is displayed when errors are found.
   
   
4. f you have previously ignored any errors, you can check for those errors again by doing the following:
   
   
   • Click Options.
   • In the Error Checking section, click Reset Ignored Errors.
   • Click OK.
   • Click Resume.
   
   
5. Position the Error Checking dialog box just below the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
   
6. Click one of the action buttons in the right side of the dialog box. The available actions differ for each type of error.
   
   Note:If you click Ignore Error, the error is marked to be ignored for each consecutive check.
   
   
7. Click Next.
   
8. Continue until the error check is complete.