Error in excel value. What are the errors in Excel and how to fix them. Errors in Excel - Difference of dates and hours must not be negative

When you enter or edit a formula, or when one of the input values ​​of a function changes, Excel may show one of the errors instead of the formula value. There are seven types of errors in the program. Let's look at their description and solutions.

1. #CASE! — given error almost always means that the formula in the cell is trying to divide some value by zero. Most often this happens due to the fact that another cell that refers to this one has a null value or there is no value. You need to check all related cells for such values. Also, this error can occur when you enter incorrect values ​​in some functions, for example, in MOD () when the second argument is 0. Also, a divide-by-zero error can occur if you leave empty cells for data entry, and any formula requires some data. This will throw an error #DIV/0! which can confuse the end user. For these cases, you can use the IF() function to test, like =IF(A1=0;0;B1/A1) . In this example, the function will return 0 instead of an error if cell A1 contains a null or empty value.
2. #N/A- This error stands for unavailable, which means that the value is not available to the function or formula. You may see this error if you enter an invalid value into a function. To fix, first check the input cells for errors, especially if they also have this error.
3. #NAME?- this error occurs when you incorrectly specify the name in the formula or incorrectly set the name of the formula itself. To correct, check again all the names and titles in the formula.
4. #EMPTY!- This error is related to the ranges in the formula. Most often, it occurs when two non-intersecting ranges are specified in the formula, for example =SUM(С4:С6,А1:С1) .
5. #NUMBER!- an error occurs when the formula contains incorrect numeric values ​​that are outside the allowable range.
6. #LINK!- the error occurs when the cells referenced by this formula have been deleted.
7. #VALUE!- in this case, we are talking about using the wrong type of argument for the function.

If you accidentally misplaced parentheses when entering a formula, Excel will display a warning message on the screen - see fig. 1. In this message, you will see Excel's suggestion on how to arrange them. If you confirm this arrangement, click Yes. But often requires your own intervention. For this click Not and fix the brackets yourself.

Handling errors with the ISERROR() function

You can catch any errors and handle them using the ISERROR() function. This function returns true or false depending on whether an error occurs while evaluating its argument. The general interception formula looks like this: =IF(ISERROR(expression),error, expression) .

The if function will return an error (for example, a message) if an error occurs during the calculation. For example, consider the following formula: =IF(ISERROR(A1/A2),""; A1/A2) . If an error occurs (divide by 0), the formula returns an empty string. If no error occurs, the A1/A2 expression itself is returned.

There is another, more convenient function IFERROR() , which combines two previous features IF() and ISERROR() : IFERROR(value;value on error) where: meaning- expression for calculation, value on error— return result in case of an error. For our example, it would look like this: =IFERROR(A1/A2,"") .

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

Immediately make a reservation that the material of the article is intended for novice users of Excel. Experienced users have already incendiary danced on this rake more than once, so my task is to protect young and inexperienced "dancers" from this.

You don't give headers to table columns

Many Excel tools, such as sorting, filtering, smart tables, pivot tables, assume that your data contains column headings. Otherwise, you will either not be able to use them at all, or they will not 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 just one. You will have to constantly correct it. Also, do not hide the rows/columns you do not need inside the table, it is better to delete them.

Multiple tables on one sheet

If these are not tiny tables containing reference books of values, then this is not worth doing.

It will be inconvenient for you to fully work with more than one table per sheet. For example, if one table is on the left and the other is 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 fixing of areas, and also one of the tables will have to be constantly searched and performed unnecessary manipulations in order to place the table cursor on it. Do you need it?

Data of the same type is artificially arranged in different columns

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

It would seem that we have a harmless format for the accumulation of information on the sales of agents and their fines. Such a layout of the table is well perceived by a person visually, since it is compact. However, believe me, it's a real nightmare to try to extract data from such tables and get subtotals (aggregate information).

The fact is that this format contains 2 dimensions: in order to, you must decide on the line, sorting through the branch, group and agent. When you find the right row, then you will have to look for the desired column, since there are a lot of them. And this "two-dimensionality" greatly complicates the work with such a table for standard Excel tools - formulas and pivot tables.

If you build a pivot table, you will find that it is not possible to easily get data by year or quarter, since the indicators are spread across different fields. You don't have one sales volume field that can be manipulated conveniently, but 12 separate fields. You will 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.

Separation of information on different sheets of the book "for convenience"

Another common mistake is, having some standard table format and needing analytics based on this data, to post it on separate sheets excel books. For example, it is common to create separate sheets for each month or year. As a result, the amount of data analysis work is actually 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 a cell comment. Keep in mind that what is in the comments, you can only see (if you find it). It's hard to get it into a cell. I recommend it is better to allocate a separate column for comments.

Formatting mess

Definitely won't add anything good to your table. This looks repulsive to the people who use your spreadsheets. At best, they will not attach importance to this, at worst, they will think that you are not organized and sloppy in business. Strive for the following:

Merging cells

Use cell merging only when you can't do without it. Merged cells make it very difficult to manipulate the ranges they fall within. 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, supplemented at the back by the text constant "RUB." or "USD" entered manually. Especially if this is not a 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 one. Because of this, there will be problems with the formulas.

If your table will be presented through an LCD projector

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

Page mode sheet in Excel

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

More useful information Learn more about Excel at

Errors in Excel occur quite often. You have probably noticed strange values ​​in cells, instead of the expected result, that began with a sign # . This indicates that the formula is returning an error. To get rid of an error, you must correct its cause, and they can be very different.

Opening and closing brackets mismatch

The most common example of errors in Excel formulas is a mismatch between opening and closing brackets. When the user enters a formula, Excel automatically checks its syntax and will not let you complete the input until it has a syntax error.

For example, in the figure above, we deliberately omitted the closing bracket when entering the formula. If you press the key Enter, Excel will issue the following warning:

In some cases, Excel offers its own version of the error correction. You can either agree with Excel or fix the formula yourself. In any case, you should not blindly rely on this correction in any case. For example, in the following figure, Excel gave us the wrong solution.

Cell filled with pound signs

There are times when a cell in Excel is completely filled with hash marks. This means one of two options:

In this case, increasing the column width will no longer help.

Error #DIV/0!

Error #DIV/0! Occurs when Excel divides by zero. This can be either an explicit division by zero or a division by a cell that contains zero or is empty.

Error #N/A

Error #N/A occurs when a value is not available for a formula or function. Here are some error cases #N/A:

Error #NAME?

Error #NAME? occurs when a formula contains a name that Excel does not understand.

AT this example range name is not defined.

Error #BLANK!

Error #EMPTY! occurs when the intersection of two ranges that do not have common points is given.

Error #NUMBER!

Error #NUMBER! occurs when a problem in a formula is related to a value.

Don't forget that Excel supports numeric values ​​from -1E-307 to 1E+307.

1. Another error case #NUMBER! is the use of a function that uses the iteration method when calculating and cannot calculate the result. A prime example of such functions in Excel are BID and VSD.

Error #LINK!

Error #VALUE!

Error #VALUE! one of the most common errors encountered in Excel. It occurs when the value of one of the arguments of a formula or function contains invalid values. The most common error cases #VALUE!.

After entering or correcting a formula, as well as when changing any value of a function, it happens that a formula error appears, and not the required value. Total spreadsheet editor recognizes seven main types of such miscalculations. What errors look like in Excel and how to fix them, we will analyze below.

Below we will present a description of the formulas shown in the picture with detailed information for each error.

1. #CASE!- “divide by 0”, most often occurs when trying to divide by zero. That is, the formula embedded in the cell, performing the division function, stumbles upon a cell where the value is “Empty” with a zero value or. To resolve the issue, check all cells involved in the calculation and correct any invalid values. The second action leading to #DIV/O! - this is the input of incorrect values ​​\u200b\u200bin some functions, such as \u003d AVERAGE () if the calculation in the range of values ​​\u200b\u200bis 0. The same result will provoke blank cells that are accessed by a formula that requires specific data to calculate.
2. #N/A- "there is no data". This is how Excel flags values ​​that are incomprehensible to the formula (function). Entering the wrong numbers into the function will definitely trigger this error. When it appears, make sure that all the input cells are filled in correctly, and especially in those where the same inscription is lit. Often seen when using
3. #NAME? - "invalid name", an indicator of the incorrect name of the formula or some part of it. The problem disappears if you check and correct all the names and names that accompany the calculation algorithm.
4. #EMPTY!– “there is an empty value in the range”, a signal that non-intersecting areas are written somewhere in the calculation or a gap is placed between the specified ranges. Pretty rare mistake. The error entry might look like this:

SUM(G10:G12 I8:J8)

Excel does not recognize such commands.
5. #NUMBER!- an error is caused by a formula containing a number that does not correspond to the boundaries of the indicated range.
6. #LINK!- warns that the cells associated with this formula have disappeared. Check if the cells indicated in the formula were most likely deleted.
7. #VALUE!– Incorrectly selected argument type for the function to work.

8. Bonus, mistake ##### - cell width is not enough to display the whole number

In addition, Excel issues a warning about an invalid formula. The program will try to tell you exactly how to arrange punctuation (for example, brackets). If the proposed option meets your requirements, click "Yes". If the tooltip requires manual adjustment. Then select "No" and rearrange the brackets yourself.

Mistakes in Excel. Using the ISERROR() function for Excel 2003

Well helps to eliminate errors in Excel function. It works by finding errors in the cells, if it finds an error in the formula it returns TRUE and vice versa. In combination with =IF(), it will allow the value to be replaced if an error is found.

Working formula: =IF(ERROR(expression),error,expression).

IF(ISERROR(A1/A2),"";A1/A2)

Explanation: if an error is found while executing A1/A2, an empty ("") will be returned. If everything went correctly (i.e. ISERROR (A1/A2) = FALSE), then A1/A2 is calculated.

Mistakes in Excel.Using IFERROR() for Excel 2007 and later

One of the reasons I quickly switched to Excel 2007 was IFERROR() (the biggest reason is )

The iferror function contains the capabilities of both functions - ISERROR() and IF(), but is available in newer versions of Excel, which is very convenient

The tool is activated as follows: =IFERROR(value; value on error). Instead of “value”, a calculation expression / cell reference is put, and instead of “value on error”, what should be returned if an inaccuracy occurs, for example, if during the calculation of A1 / A2, #CASE! then the formula will look like this:

ISERROR(A1/A2;””)

» download here