Graphs and Charts (5)
Working with VB project (12)
Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)

Excel calculates incorrectly. Why?

Often when calculating the difference of two cells in Excel, you can see that it is not equal to zero, although the numbers are the same. For example, in cells A1 and B1 the same number is written 10.7, and in C1 we subtract the other from one:

And the strangest thing is that in the end we don’t get 0! Why?

The reason is obvious - cell format
First, the most obvious answer: if you are comparing the values ​​of two cells, you need to make sure that the numbers there are really equal and not rounded by the cell format. For example, if we take the same numbers from the example above, then if we select them - right mouse button - Format cells-tab Number-select the Numeric format and set the number of decimal places to 7:

Now everything becomes obvious - the numbers are different and were simply rounded by the cell format. And naturally they cannot be equal. In this case, it would be optimal to understand why the numbers are the way they are, and only then make a decision. And if you are sure that numbers should actually be rounded to tenths, then you can use the ROUND function in the formula:
=ROUND(B1 ;1)-ROUND(A1 ;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
There is also a more radical method:

• Excel 2007: Office button -Excel options -Additionally (Advanced) -
• Excel 2010: File -Options -Additionally (Advanced) -Set precision as displayed
• Excel 2013 and higher: File -Options -Additionally (Advanced) -Set precision as displayed

This will write all the numbers on all sheets of the workbook exactly as they are displayed by the cell format. It is better to perform this action on a copy of the book, because it brings all numerical data in all sheets of the book to the form as they are displayed on the screen. Those. if the number itself contains 5 decimal places, and the cell format is set to only 1, then after using this option the number will be rounded to 1 decimal place. At the same time cancel this operation This is not possible unless you close the book without saving.

The reason is software
But often in Excel you can observe a more interesting “phenomenon”: the difference between two fractional numbers obtained by the formula is not equal to exactly the same number written directly into the cell. For example, write the following formula in a cell:
=10,8-10,7=0,1
by appearance the result should be an answer TRUE. But in fact it will be FALSE. And this example is not the only one - this Excel behavior is far from uncommon in calculations. It can also be found in a less explicit form - when calculations are based on the values ​​of other cells, which in turn are also calculated by formulas, etc. But the reason is the same in all cases.

Why are seemingly identical numbers not equal?
First, let's look at why Excel considers the above expression to be false. After all, if you subtract 10.7 from 10.8, in any case you get 0.1. So somewhere along the way something went wrong. Let's write the left side of the expression in a separate cell: =10.8-10.7. 0.1 appears in the cell. Now select this cell - right mouse button - Format cells-tab Number-select the Numeric format and set the number of decimal places to 15:


and now you can see that in fact the cell contains not exactly 0.1, but 0.100000000000001. Those. in the 15th significant digit we have a “tail” in the form of an extra unit.
Now let’s figure out where this “tail” came from, because both logically and mathematically it shouldn’t be there. I will try to tell you very briefly and without unnecessary abstruse language - if you wish, you can find a lot of them on this topic on the Internet.
The thing is that in those distant times (this is approximately the 1970s), when the PC was still something like exotic, there was no single standard for working with floating point numbers (fractional, to put it simply). Why this standard at all? Then what computer programs They see numbers in their own way, but fractional ones generally have the status “everything is complicated.” And at the same time, the same fractional number can be represented in different ways and operations with it can also be processed. Therefore, in those days, the same program, when working with numbers, could produce different results on different PCs. Taking into account all the possible pitfalls of each PC is not an easy task, so at one point the development of a unified standard for working with floating point numbers began. Omitting various details, nuances and interesting things about the story itself, I will only say that in the end it all resulted in IEEE754 standard. And in accordance with its specification, in the decimal representation of any number, errors are allowed in the 15th significant digit. Which leads to inevitable errors in calculations. Most often this can be observed in subtraction operations, because It is the subtraction of numbers that are close to each other that leads to the loss of significant digits.
More information about the specification itself can also be found in the Microsoft article: Floating-point arithmetic results in Excel may be inaccurate
This is precisely the reason for this behavior in Excel. Although in fairness it should be noted that not only Excel, but all programs based on this standard. Of course, this begs a logical question: why did they adopt such a buggy standard? I'd say there was a trade-off between performance and functionality. Although there may have been other reasons.

Another thing is much more important: how to deal with this?
In fact, no way, because... This is a software "bug". And in this case, there is no other way out but to use all sorts of patches like ROUND and similar functions. At the same time, ROUND should be used here not as was demonstrated at the very beginning, but a little differently:
=ROUND(10.8 - 10.7 ;1)=0.1
=ROUND(10.8-10.7.1)=0.1
those. in ROUND we must place the “buggy” expression itself, and not each of its arguments separately. If you put every argument, it will not have any effect, because the problem is not in the number itself, but in how the program sees it. And in this case, 10.8 and 10.7 have already been rounded to one digit and it is clear that rounding each number separately will not have any effect at all. You can, however, get out of it in another way. Multiply each number by a certain value (say by 1000, to remove 100% decimal places) and then subtract and compare:
=((10,8*1000)-(10,7*1000))/1000=0,1

I would like to believe that at least someday Microsoft will be able to defeat the described feature of the IEEE754 standard, or at least make a patch that will perform simple calculations no worse than a 50-ruble calculator :) Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Main function Excel programs– working with formulas. This is what it is most often used for. Sometimes it happens that formulas in Excel do not work, in which case something needs to be done. This is exactly what will be discussed further.

Fixing the problem

There are quite a few reasons why the formula does not work in Excel. Perhaps your program or specific range settings are set incorrectly, or the formula is entered incorrectly.

Formulas don't work when dragging

The essence of the error is that several columns are created, for example, 2, and the third should display the result. There the formula is indicated and stretched down to cover the entire range, but in the end the value from the first cell is simply transferred to the next ones. In this case, there are several ways to fix everything:

1. Press the F9 button to start recalculating the sheet.
2. Change the cell format to General. To do this, select it and right-click. A menu will open where you should select “Cell Format” and change to “General”.
3. Open the parameters for calculating formulas and check that the marks are as on the screenshot.

The formula doesn't count

There are several reasons why the formula in Excel does not calculate:

• Invalid cell format. To change it, you need to select the desired range of cells and select “Format Cells”.

• The “Show formulas” mode is activated. To disable it, you need to go to the “Formulas” section in the top panel, click on “Formula Dependencies” and disable “Show Formulas”.

• The formula or its syntax was entered incorrectly. In this case, the formula is displayed instead of the value. You need to check it carefully.

These are some of the main reasons why Excel does not calculate formulas.

The program does not calculate the formula entered into the cell

This often happens because the cell format is set to text. How to change it to another has already been described earlier.

The amount is not calculated

There are two options why the Excel application does not calculate the amount:

1. The cell format is test.
2. Amount calculation is deactivated. Needed using right button Click on the special panel with the mouse and set the checkbox to “Amount”.

That's why the amount doesn't count.

Cell data is not summed up correctly

Some users have encountered problems where Excel incorrectly calculates the sum of selected cells. This often happens because some cells, or even most of them, are in text format. Because of this, not all data is summarized and the result is incorrect. You need to change the format to "General" to fix the problem.

Recalculation disabled

It happens that the user has calculated all the data, but when they are adjusted in the cells associated with the formula, the result does not change. This means that the parameters are set incorrectly:

1. Open program settings.
2. There you should go to the “Formulas” section. At the top there will be a block “Calculation parameters”, and there will be a parameter “Calculations in the book”, where the “Automatic” option should be selected. If not, set it properly and click “OK”.

Problems with formulas working in Microsoft Excel

Now, if any value is changed, the result will be recalculated.

From all this it is clear that there are many different reasons why Excel calculates formulas incorrectly or refuses to calculate them at all. If the user displays a formula where the result should be, it means the cell format is set to text or there is an error in the formula itself. View mode may be activated. Users also often forget to put “=” before the beginning of the formula. If the values ​​in related cells change, but the result remains the same, you need to check whether auto-update is activated. In some cases, an error is displayed in the cells with totals, then you should check all the values.

Today I came across a situation where... excel formulas did not work when pulling, i.e. there were two columns and it was necessary to calculate the difference of the first two columns in the third column. As a result, the formula is made and stretched down to the end, but the result from the first cell is copied to the remaining cells.

1 Option

It is likely that you have disabled automatic sheet recalculation; to do this, just press the key F9

2 Option for all versions of Office

Check that the cell format is not text (due to this, calculations and, accordingly, formulas may not work)

PS to fix this, select the cells and select General format (see figure Cell Format)

3 Option for Office 2010

Check the formula calculation parameters in the settings.

To do this, click on the menu FILE-> PARAMETERS-> FORMULAS and in the “Calculation Options” menu, look where you have a checkmark (see the picture it should be like this)

4 Option for Office 2007/2010

For Office 2007 the path is like this Formula->Calculation Options-> and put a checkmark on Automatically, except for data tables.(see picture below)

Lifehacker readers are already familiar with Denis Batyanov who shared with us. Today Denis will talk about how to avoid the most common problems with Excel, which we often create for ourselves.

Let me make a reservation right away that the material in this article is intended for novice Excel users. Experienced users have already energetically danced on this rake more than once, so my task is to protect young and inexperienced “dancers” from this.

You don't give table column headings

Many Excel tools, such as sorting, filtering, smart tables, and pivot tables, assume that your data contains column headings. Otherwise, you either won’t be able to use them at all, or they won’t work quite correctly. Always make sure your tables contain column headings.

Empty columns and rows inside your tables

This confuses Excel. When it encounters an empty row or column inside your table, it starts to think that you have 2 tables, not one. You will have to constantly correct it. Also, don’t hide rows/columns you don’t need inside the table; it’s better to delete them.

There are several tables on one sheet

If these are not tiny tables containing reference books of values, then you shouldn’t do this.

It will be inconvenient for you to fully work with more than one table on a sheet. For example, if one table is located on the left and the second on the right, then filtering one table will affect the other. If the tables are located one below the other, then it is impossible to use the freezing of areas, and you will also have to constantly search for one of the tables and perform unnecessary manipulations in order to place the table cursor on it. Do you need it?

Data of the same type is artificially located in different columns

Very often, users who know Excel rather superficially prefer this table format:

It would seem that we have a harmless format for accumulating information on agent sales and their fines. This table layout is visually well perceived by humans, as it is compact. However, believe me, it is a complete nightmare to try to extract data from such tables and get subtotals(aggregate information).

The fact is that this format contains 2 dimensions: to , you must decide on the row by looping through the branch, group and agent. When you find the desired column, then you will have to look for the required column, since there are many of them. And this “two-dimensionality” greatly complicates working with such a table even for standard Excel tools - formulas and pivot tables.

If you build a pivot table, you will find that there is no way to easily get data by year or quarter, since the indicators are separated into different fields. You don't have one sales volume field that can be conveniently manipulated, but rather 12 separate fields. You'll have to manually create separate calculated fields for quarters and years, although if it were all in one column, the pivot table would do it for you.

If you want to use standard summation formulas like SUMIF, SUMIFS, SUMPRODUCT, you will also find that they will not work effectively with this table layout.

Spreading information across different sheets of the book “for convenience”

Another common mistake is having some standard format tables and needing analytics based on this data, distribute it into separate sheets Excel workbooks. For example, they often create separate sheets for each month or year. As a result, the amount of data analysis work is effectively multiplied by the number of sheets created. You don't have to do that. Accumulate information on ONE sheet.

Information in the comments

Users often add important information, which they may need, in the cell comment. Keep in mind that what is in the comments you can only look at (if you find it). It's difficult to get it into the cell. I recommend that you create a separate column for comments.

Formatting mess

Definitely won't add anything good to your table. This looks off-putting to the people who use your spreadsheets. At best, they won’t attach any importance to this, at worst, they will think that you are unorganized and sloppy in your affairs. Aim for the following:

Merging cells

Use cell merging only when you absolutely cannot do without it. Merged cells make it very difficult to manipulate the ranges they belong to. There are problems when moving cells, when inserting cells, etc.

Combining text and numbers in one cell

A painful impression is made by a cell containing a number appended at the back with the text constant “RUB.” or "USD" entered manually. Especially if it's not printed form, but a regular table. Arithmetic operations with such cells are naturally impossible.

Numbers as text in a cell

Avoid storing numeric data in a cell in text format. Over time, some of the cells in such a column will have a text format, and some will have a regular format. This will cause problems with formulas.

If your table will be presented through an LCD projector

Choose the most contrasting combinations of color and background. Looks good on the projector dark background and light letters. The most terrible impression is made by red on black and vice versa. This combination looks extremely low-contrast on a projector - avoid it.

Page mode of worksheet in Excel

This is the same mode in which Excel shows how the sheet will be divided into pages when printed. Page borders are highlighted in blue. I do not recommend constantly working in this mode, which many people do, since the printer driver is involved in the process of displaying data on the screen, and this depends on many reasons (for example, the printer is networked and this moment unavailable) is fraught with freezes in the visualization process and recalculation of formulas. Work as usual.

Even more useful information you can learn about Excel at

If Excel can't recognize the formula you're trying to create, you may receive an error message like this:

Unfortunately, this means Excel doesn't understand what you're trying to do, so you might be better off quitting and starting over.

First of all click the button OK or key ESC to close the error message.

You will be returned to the cell with the incorrect formula, for which editing mode will be turned on and the problem area will be highlighted. If you don't know what to do with this and want to start over, exit edit mode by pressing the key again ESC or button Cancel in the formula bar.

If you want to move forward, the following checklist contains troubleshooting instructions to help you find out what might be wrong.

Do you see the hash error (#)?

Excel displays various hash errors (#), such as #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? and #NULL! to indicate what in the formula is not working correctly. For example, #VALUE! the error is caused by incorrect formatting or unsupported data types in the arguments. Plus you'll see #REF! error if the formula refers to cells that have been deleted or replaced with other data. Troubleshooting recommendations will vary for each error.

Note:# # # # is not a formula related error. This means that the column is not wide enough to display the contents of the cell. Simply drag a column to expand it, or go to the tab home > format > Automatic column width.

Review the following sections to address the errors you are seeing:

Every time you open a spreadsheet with formulas that reference values ​​in other spreadsheets, you will be prompted to update the references or leave them as is.

Excel displays the dialog box above to ensure that the formulas in the current worksheet always point to the most updated values ​​in case the reference value has changed. You can update the links or skip this step. Even if you decide not to update the links, you can do so manually directly in the spreadsheet at any time.

You can also disable this dialog box to appear when opening a file. To do this, go to the section File > Options > Advanced > General and uncheck Request automatic link updates.

The formula returns a syntactic construct, not a value

If the formula doesn't return a value, use the instructions below.

Formula doesn't evaluate

If the formula does not evaluate, check to see if it is included in Excel function automatic calculation. Formulas are not calculated if manual calculation is enabled. To check if it is enabled automatic calculation, follow the steps below.

additional information For information about calculations, see Change formula conversion, iteration, or precision.

The formula contains one or more circular references

A circular reference occurs when a formula references the cell in which it resides. The fix is ​​to move the formula to a different cell or change the formula syntax to avoid circular references. However, in some scenarios you may need a circular reference because they result in iterating functions, repeating until a certain numeric condition is met. In this case, you will need to enable or disable circular reference.

For more information about circular references, see Remove or resolve a circular reference

Does the function begin with an equal sign (=)?

If an entry does not begin with an equal sign, it is not considered a formula and will not be calculated (this is a common mistake).

If you enter SUM(A1:A10) Excel will display the text string SUM(A1:A10) instead of the result of the formula. In addition, when you enter 11/2 Excel displays a date, such as 2nd Nov or 11/02/2009, instead of dividing 11 by 2.

To avoid unexpected results like this, always start your formula with an equal sign. For example, enter = SUM(A1:A10) And =11/2 .

Do opening and closing parentheses match?

If a formula uses a function, it proper operation It's important that every opening parenthesis has a closing parenthesis, so make sure that each parenthesis has a matching pair. For example, the formula =IF(B5<0);"Недопустимо";B5*1,05) won't work because it has two closing parentheses and only one opening parenthesis. The correct version of this formula is as follows: =IF(B5<0;"Недопустимо";B5*1,05) .

Does the syntax include all required arguments?

Functions in Excel have arguments - values ​​that must be specified for the function to work. Only a small number of functions work without arguments (for example, PI or TODAY). Check the formula syntax that appears when you start typing the function to make sure that all required arguments are provided.

If you need to quickly review the syntax of a specific function, see the list of Excel functions (by category).

Are there unformatted numbers in the formulas?

Do not enter numbers formatted with dollar signs ($) and decimal separators (,) in formulas because dollar signs represent absolute references and commas are argument separators. Instead of $1,000 You must enter 1000 in the formula.

If you use formatted numbers in your arguments, you will get unexpected calculation results, but you may also see a #NUM! error. . For example, if you enter the formula = ABS (-2 134) to find the absolute value -2134, Excel will display #NUM! error because the ABS function only takes one argument and it sees the numbers -2 and 134 as separate arguments.

Note: The result of a formula can be formatted using decimal separators and currency symbols after entering a formula with unformatted numbers (constants). As a general rule, it is not recommended to use constants directly in formulas: they can be difficult to find if you need to update values, and in addition, typos are often made when entering them. It is much more convenient to place constants in separate cells, where they will be accessible and easily referenced.

A formula may not return the expected results if the cell's data type is not suitable for the calculation. For example, if you enter the simple formula =2+3 in a cell that is in text format, Excel will not be able to calculate the entered data. The cell will display the string =2+3 . To fix this error, change the cell's data type from Text on General, as described below.

Select a cell.

On the tab home click the arrow next to the list Number format(or click CTRL+1) and select General.

Press the key F2 to switch to edit mode, and then press ENTER to confirm the formula.

If you enter a date in a cell that has numerical data type, it can be displayed as a numeric value rather than as a date. To display this number as a date in the collection Number format select format date.

Are you trying to do multiplication without using the * symbol?

A cross is often used as a multiplication operator in a formula ( x), but Excel must use an asterisk (*) for this purpose. If you use an "x" in a formula, an error message appears and you are prompted to correct the formula by replacing x on "*".

Are quotation marks missing around text in formulas?

If the formula contains text, it must be enclosed in quotation marks.

For example, the formula ="Today" & TEXT(TODAY();"dddd, dd.MM") combines the text string "Today" with the results of the TEXT and TODAY functions and returns a result like the following: Today is Monday, 30.05.

In the formula, the "today" field includes a space before the closing quotation mark to provide white space between the words "today" and "Monday, May 30." If there are no quotation marks around text in a formula, #NAME? may appear. error .

Does the formula include more than 64 functions?

Are sheet names enclosed in apostrophes?

For example, to return the value from cell D3 on the quarterly data sheet in a workbook, enter: = "quarterly data"! D3. Without quotes around the sheet name, does the formula display #NAME? error .

You can also click values ​​or cells on another sheet to add a reference to them in the formula. Excel will automatically enclose the sheet names in quotation marks.

If a formula references an external workbook, is the path to the workbook correct?

For example, to reference cells a1 through A8 on the Sales sheet in the Q2 activity book open in Excel, enter: = Sales! A1:A8. If there are no square brackets in the formula, #REF! .

If the workbook is not open in Excel, enter the full path to the file.

For example =LOW("C:\My Documents\[Transactions for the second quarter.xlsx]Sales"!A1:A8).

Note: If the full path contains spaces, you must enclose it in apostrophes (at the beginning of the path and after the book name before the exclamation point).

Advice: To get the path to another workbook, the easiest way is to open it, enter the equal sign (=) in the original workbook, and then use the keys ALT+TAB go to the second workbook and select any cell on the desired sheet. After this, the source book can be closed. The formula will automatically update to show the full path to the sheet name with the correct syntax. This path can be copied and pasted if necessary.

Are you trying to divide numeric values ​​by zero?

Dividing a cell by another cell that has a zero value (0) or no value results in a #DIV or 0! Error.

To resolve this error, you can simply check to see if the denominator exists.

IF(B1;A1/B1;0)

The meaning of this formula is this: IF B1 exists, return the result of dividing A1 by B1, otherwise return 0.

Does the formula refer to deleted data?

Before you delete data in cells, ranges, specific names, sheets, and workbooks, always check to see if you have formulas that reference them. You can replace formulas with their results before deleting the referenced data.

If you are unable to replace formulas with their results, read about the errors and possible solutions:

If a formula references cells that have been deleted or replaced with other data and returns a #REF! Select the cell with #REF! If the position you specify is before the first or after the last element in the field, the formula returns a #REF! error. In the formula bar, click the #REF! and delete it. Then enter the range for the formula again.

If a specific name is missing and a formula that references that name returns #NAME? error Define a new name that refers to the desired range, or change the formula so that it refers directly to the range of cells (for example, a2:D8).

If a sheet is missing and a formula referencing it returns #REF! Error: We cannot resolve this issue, unfortunately a sheet that has been deleted cannot be recovered.

If a workbook is missing, it does not affect the formula that references it until you update the formula.

For example, if you use the formula =[Book1.xlsx]Sheet1"!A1, and such a book no longer exists, the values ​​referencing it will be available. But if you change and save a formula that references this workbook, a dialog box appears Update values prompting you to enter a file name. Click the button Cancel and ensure data integrity by replacing the formula that references the missing workbook with its results.