Why Excel does not consider the formula: 5 solutions to the problem

One of the most popular Excel features is working with formulas. Thanks to this function, the program independently produces various calculations in the tables. But sometimes it happens that the user enters the formula into the cell, but it does not fulfill its direct destination - calculating the result. Let's deal with what it can be connected with and how to solve this problem.

Elimination of computing problems

Causes of problems with the calculation of formulas in Excel can be completely different. They can be due to the specific book settings or even a separate range of cells and different errors in the syntax.

Method 1: Changes in cell format

One of the most common reasons why Excel does not consider or not correctly consider the formula, is the incorrectly exposed format of the cells. If the range has a text format, the calculation of expressions in it is not done at all, that is, they are displayed as ordinary text. In other cases, if the format does not correspond to the essence of the calculated data, the result displaced in the cell may not be displayed correctly. Let's find out how to solve this problem.

1. In order to see which format is a specific cell or range, go to the "Home" tab. On the tape in the "Number" tool block there is a field of displaying the current format. If there is the meaning of "text", then the formula will not be exactly calculated.



2. In order to change the format to click on this field. A list of formatting selection will open, where you can choose the value corresponding to the essence of the formula.



3. But the choice of types of format through the tape is not so extensive as through a specialized window. Therefore, it is better to apply the second formatting option. Select the target range. Click on it right mouse button. In the context menu, select the "Cell format" item. You can also after the range isolate, click the Ctrl + 1 key combination.



4. The formatting window opens. Go to the "Number" tab. In the "Numeric formats" block, choose the format that we need. In addition, on the right side of the window, it is possible to select the type of presentation of a specific format. After the choice is made, click on the "OK" button, placed at the bottom.



5. Select the cells alternately in which the function was not considered, and for recalculation, press the F2 function key.

Now the formula will be calculated in the standard order with the output of the result in the specified cell.

Method 2: Disconnecting the "Show Formulas" mode

But it is possible that instead of the calculation results, you are displayed expressions, it is that the program "show formulas" is included in the program.

1. To enable the display of the results, go to the "Formula" tab. On the tape in the "Dependency Dependency" tool block, if the "Display formula" button is active, then click on it.



2. After these actions in the cells again, the result will be displayed instead of the syntax of functions.

Method 3: Correction of the error in the syntax

The formula can also be displayed as text if errors were made in its syntax, for example, the letter is passed or changed. If you entered it manually, and not through the functions master, then such is quite likely. A very common error associated with the display of an expression, like text, is the presence of a space before the sign "=".

In such cases, it is necessary to carefully review the syntax of those formulas that are incorrectly displayed and make appropriate adjustments.

Method 4: Inclusion of the recalculation of the formula

There is such a situation that the formula seems to and displays the value, but when changing the cells associated with it itself does not change, that is, the result is not recalculated. This means that you incorrectly configured the calculation parameters in this book.

1. Go to the "File" tab. Being in it, you should click on the "Parameters" item.



2. The parameter window opens. You need to go to the "Formulas" section. In the "Computing Settings" block, which is located at the top of the window, if in the "Calculation in the book" parameter, the switch is not set to the "automatically" position, then this is the reason that the result of calculations is irrelevant. Rearrange the switch to the desired position. After executing the above settings to save them at the bottom of the window, press the "OK" button.

Now all expressions in this book will be automatically recalculated when any associated value changes.

Method 5: Error in the formula

If the program still makes calculation, but as a result it shows a mistake, then the situation is likely that the user is simply mistaken when entering the expression. The erroneous formulas are those when calculating which the following values ​​appear in the cell:

• #NUMBER!;
• # Mean!;
• # Empty!;
• # Del / 0!;
• # N / d.

In this case, you need to check whether the data is recorded correctly in the cells referenced by the expression, whether there are no errors in the syntax or is not laid in the formula itself any incorrect action (for example, division by 0).

If the function is complicated, with a large number of related cells, it is easier to trace the calculations using a special tool.

1. Select a cell with an error. Go to the "Formulas" tab. On the tape in the "Dependency Dependency" tool block by clicking on the "Calculate formula" button.



2. A window opens, which seems a complete calculation. Click on the "Calculate" button and view the calculation of step by step. We are looking for a mistake and eliminate it.

As we see, the reasons for the fact that Excel does not consider or does not correctly consider the formula, can be completely different. If the user is displayed instead of calculating the user, the function itself is displayed, in this case, most likely, or the cell is formatted for text, or the expression view mode is turned on. Also, it is possible to error in the syntax (for example, the presence of a space before the "=" sign). If after changing the data in related cells, the result is not updated, then you need to see how the auto-update is configured in the book parameters. Also, often instead of the correct result in the cell an error is displayed. Here you need to view all the values ​​referenced by the function. In case of error detection, it should be eliminated.