Sự cố với công thức trong bảng tính Excel

Excel features allow you to perform calculations of almost any complexity thanks to formulas and functions. However, sometimes users may encounter the fact that the formula refuses to work or gives an error instead of the desired result. In this article, we will look at why this happens, and what actions to take to solve the problem.

Solution 1: Change cell format

Very often, Excel refuses to perform calculations due to the fact that the wrong cell format is selected.

For example, if a text format is specified, then instead of the result we will see just the formula itself in the form of plain text.

In some situations, when the wrong format is selected, the result can be calculated, but it will be displayed in a completely different way than we would like.

Obviously, the cell format needs to be changed, and this is done as follows:

1. To determine the current cell format (range of cells), select it and, being in the tab "Nhà", pay attention to the group of tools "Con số". There is a special field here that shows the format currently in use.
2. You can choose another format from the list that will open after we click on the down arrow next to the current value.

Cell format can be changed using another tool that allows you to set more advanced settings.

1. Having selected a cell (or having selected a range of cells), right-click on it and in the list that opens, click on the command “Định dạng ô”. Or instead, after selection, press the combination Ctrl + 1.
2. Trong cửa sổ mở ra, chúng ta sẽ thấy mình ở trong tab "Con số". Here in the list on the left are all the available formats that we can choose from. On the left side, the settings of the selected option are displayed, which we can change at our discretion. Press when ready OK.
3. In order for the changes to be reflected in the table, we activate the editing mode one by one for all cells in which the formula did not work. Having selected the desired element, you can proceed to editing by pressing the key F2, by double-clicking on it, or by clicking inside the formula bar. After that, without changing anything, click Enter.

Lưu ý: If there is too much data, it will take a long time to manually complete the last step. In this case, you can do otherwise – use điền vào điểm đánh dấu. But this one only works if the same formula is used in all cells.

1. We perform the last step only for the topmost cell. Then we move the mouse pointer to its lower right corner, as soon as a black plus sign appears, hold down the left mouse button and drag it to the end of the table.
2. We get a column with the results calculated using formulas.

Solution 2: turn off the “Show formulas” mode

When we see the formulas themselves instead of the results, this may be due to the fact that the formula display mode is activated, and it needs to be turned off.

1. Chuyển tab mới "Công thức". In the tool group “Formula dependency” nhấn nút “Show Formulas”if it is active.
2. As a result, cells with formulas will now display the results of calculations. True, because of this, the boundaries of the columns may change, but this is fixable.

Solution 3: Activate automatic recalculation of formulas

Sometimes a situation may arise when the formula has calculated some result, however, if we decide to change the value in one of the cells to which the formula refers, the recalculation will not be performed. This is fixed in the program options.

1. Đi tới menu "tập tin".
2. Select a section from the list on the left "Thông số".
3. In the window that appears, switch to the subsection "Công thức". On the right side of the window in the group “Computation Options” chọn hộp bên cạnh tùy chọn “tự động”if another option is selected. Click when ready OK.
4. Everything is ready, from now on all formula results will be recalculated automatically.

Solution 4: fixing errors in the formula

If errors are made in the formula, the program may perceive it as a simple text value, therefore, calculations on it will not be performed. For example, one of the most popular errors is a space placed before the sign "Bằng". At the same time, remember that the sign Ăn ý must always come before any formula.

Also, quite often errors are made in function syntaxes, since filling them out is not always easy, especially when multiple arguments are used. Therefore, we recommend using Trình hướng dẫn hàm to insert a function into a cell.

To make the formula work, all you need to do is carefully check it and correct any errors found. In our case, you just need to remove the space at the very beginning, which is not needed.

Sometimes it’s easier to delete a formula and write it again than to try to look for an error in an already written one. The same goes for functions and their arguments.

Lỗi thường gặp

In some cases, when the user made a mistake when entering a formula, the following values ​​may be displayed in the cell:

• #DIV/0! is the result of division by zero;
• #N/A – input of invalid values;
• #NUMBER! – incorrect numerical value;
• #VALUE! – the wrong kind of argument is used in the function;
• #EMPTY! – wrong range address;
• #LINK! – the cell referred to by the formula has been deleted;
• #NAME? – invalid name in the formula.

If we see one of the above errors, first of all we check if all the data in the cells participating in the formula is filled in correctly. Then we check the formula itself and the presence of errors in it, including those that contradict the laws of mathematics. For example, division by zero is not allowed (error # DEL / 0!).

In cases where you have to deal with complex functions that refer to many cells, you can use the validation tools.

1. We mark the cell containing the error. In the tab "Công thức" trong nhóm công cụ “Formula dependencies” Nhấn nút “Calculate formula”.
2. In the window that opens, step-by-step information on the calculation will be displayed. To do this, press the button “Compute” (each press advances to the next step).
3. Thus, you can track each step, find the error and fix it.

You can also use the useful một công cụ “Kiểm tra lỗi”, which is located in the same block.

A window will open in which the cause of the error will be described, as well as a number of actions regarding it, incl. formula bar fix.

Kết luận

Working with formulas and functions is one of the main features of Excel, and, of course, one of the main areas of use of the program. Therefore, it is very important to know what problems can arise when working with formulas, and how they can be corrected.