Tuesday, January 16, 2018

Correct an inconsistent formula

Correct an inconsistent formula

An error indicator appears when the formula does not match the pattern of other formulas near it. This does not always mean that the formula is wrong. If the formula is wrong, making the cell reference consistent often solves the problem.

For example, to multiply column A by column B, the formulas are A1*B1, A2*B2, A3*B3, and so on. If the next formula after A3*B3 is A4*B2, Excel identifies it as an inconsistent formula, because to continue the pattern, the formula should be A4*B4.

  1. Click the cell that contains the error indicator and look at the Formula Bar to verify that cell references are correct.

  2. Click the arrow next to the button that appears.

    Inconsistent formula in C4	mac_inconsistent_formula

    The shortcut menu shows the options that are available to resolve this warning.

  3. Do any of the following:

Select

To

Copy Formula from Above

Make the formula consistent by copying the pattern from the cell above. In our example, the formula becomes A4*B4 to match the pattern A3*B3 in the cell above.

Ignore Error

Remove the error indicator, for example, if the inconsistency in the formula is intentional or otherwise acceptable.

Edit in Formula Bar

Review the formula syntax and verify that cell references are what you intended.

Error Checking Options

Select the types of errors that you want Excel to flag. For example, if you don't want to see error indicators for inconsistent formulas, clear the Flag formulas that are inconsistent with formulas in adjoining cells check box.

Tips: 

    • To ignore error indicators for multiple cells at a time, select the range that contains the errors that you want to ignore. Next, click the arrow next to the button that appeared Inconsistent formula warning button , and on the shortcut menu, select Ignore Error.

    • To ignore error indicators for a whole sheet, first click a cell that has an error indicator. Then press COMMAND + A to select the sheet. Next, click the arrow next to the button that appeared Inconsistent formula warning button , and on the shortcut menu, select Ignore Error.

See also

Detect errors in formulas

Hide error values and error indicators

No comments:

Post a Comment