Errors in Excel are an indispensable companion for everyone who... When an expression in a cell cannot be evaluated, the program displays an error message in the cell. It begins with a "#" followed by the name of the error. There is no need to be afraid of this if you are familiar with Excel functions and know how to follow the simplest logic of mathematical operations - you will easily find and correct the error.
If the cell is completely filled with pound signs (#), this is not an error at all. There is not enough space in the cell to display the result. Increase the cell size or decrease the font size so that the result can be displayed.
Types of errors
If an error does occur, decryption will help in correcting it:
| Error | Description |
| #DIV/0! | An error occurs when trying to divide by zero |
| #NAME? | The program cannot recognize the entered name. For example, you misspelled the function name, or did not enclose the text string in quotes |
| #N/A | Data not available. For example, I didn’t find any meaning |
| #EMPTY! | You requested the intersection of ranges that do not intersect |
| #NUMBER! | The problem is with one of the numeric values used in the formula. For example, you are trying to take the square root of a negative number. In classical mathematics this operation makes no sense |
| #LINK! | The formula contains a link that does not exist. For example, you deleted the cell it refers to |
| #VALUE! | The formula contains invalid components. Often this error occurs when the syntax of formulas is violated. |
When there is an error in a formula in a cell, a marker appears next to it. By clicking on it, you can read help about this error. You can also see the stages of calculation. Select this item, and the program will show a window where the location of the error will be underlined. This The best way determine where the error occurs.
In addition, you can do without correcting such errors, but simply . But it must be appropriate. Errors should only be worked around if they cannot be corrected. Otherwise, the calculation results may be distorted.
Tracing an error through calculation steps Circular links in Excel
Another type of error is a circular reference. It occurs when you reference a cell whose value depends on the one in which you write the formula. For example, in a cage A1 the formula =A2+1 is written, and in A2 write =A1 , a cyclic reference will appear that will be recalculated endlessly. In this case, the program warns about the appearance of a cyclic reference and stops the calculation of “cyclic formulas”. A double-headed arrow appears on the left side of the cells. You will have to correct the error that occurred and repeat the calculation.
Sometimes complex “looping” occurs when a cyclic link is formed with several intermediate formulas.
To track down such errors, run Formulas – Formula Dependencies – Error Checking – Circular References. In the drop-down list, the program displays the addresses of the cells that create an endless loop. All that remains is to correct the formulas in these cells.
Tracking Circular Links In Excel, you can try to calculate the result of looping formulas. To do this, check the box File – Options – Formulas – Enable Iterative Calculations. In the same block you can set maximum amount iterations (calculations) to find balance and acceptable error. In most cases this is not necessary, so I recommend not checking this box. However, when you know that the looping formulas are correct and their calculation will lead to a stable result - why not do it?
That's all about the types of errors in Excel. This short article has given you enough information to deal with the most common errors in Excel by analyzing the return value. But read the extended list of errors in! I’m ready to answer your questions - write in the comments.
In the next article I will tell you. Needless to say that Excel functions are “our everything”?
I think no. Therefore, go ahead and read, this will be the first step into the world of complex formulas with the right results!
When you enter or edit a formula, or when one of the function's input values changes, Excel may show one of the errors instead of the formula value. The program provides seven types of errors. Let's look at their description and solutions.
- #CASE! — this error almost always means that the formula in the cell is trying to divide some value by zero. Most often this happens because another cell that references this one contains a null value or a missing value. You need to check all related cells for such values. This error can also occur when you enter incorrect values in some functions, for example in REST(), when the second argument is 0. Also, the divide by zero error can occur if you leave empty cells for data entry, and some formula requires some data. An error will be displayed #DIV/0!, which can be confusing 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 zero or empty value.
- #N/A- this error stands for unavailable, and this 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 correct this, first check the input cells for errors, especially if they also contain this error.
- #NAME?— this error occurs when you incorrectly specify the name in the formula or mistakenly specify the name of the formula itself. To correct, double-check all the names in the formula.
- #EMPTY!— this error is related to the ranges in the formula. Most often it occurs when the formula specifies two non-overlapping ranges, for example =SUM(C4:C6,A1:C1).
- #NUMBER!— an error occurs when the formula contains incorrect numerical values that are outside the acceptable range.
- #LINK!- the error occurs when cells referenced by this formula have been deleted.
- #VALUE!- in this case we are talking about using the wrong type of argument for the function.
If, when entering a formula, you accidentally placed parentheses incorrectly, Excel will display a warning message on the screen - see fig. 1. In this message you will see Excel's guess as to how they should be arranged. If you confirm this arrangement, click Yes. But often your own intervention is required. For this click No and fix the brackets yourself.
Handling errors using the ERROR() function
You can intercept any errors and process them using the ERROR() function. This function returns true or false depending on whether an error occurs when evaluating its argument. The general formula for interception 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(EORROR(A1/A2);""; A1/A2) . If an error occurs (division by 0), the formula returns an empty string. If no error occurs, the expression A1/A2 itself is returned.
There is another, more convenient function IFERROR(), which combines the two previous functions IF() and ERROR(): IFERROR(value;value on error), where: meaning— expression for calculation, error value— the result returned in case of an error. For our example, it will look like this: =IFERROR(A1/A2;"") .
Making even a small change at work Excel sheet may lead to errors in other cells. For example, you might accidentally enter a value in a cell that previously contained a formula. This simple mistake can have a significant impact on other formulas, and you won't be able to detect it until you make some changes to the worksheet.
Errors in formulas fall into several categories:
Syntax errors: Occurs when the formula syntax is incorrect. For example, a formula has incorrect parentheses, or a function has the wrong number of arguments.
Logical errors: In this case, the formula does not return an error, but has a logical flaw, which causes the calculation to be incorrect.
Invalid link errors: The formula logic is correct, but the formula uses an incorrect cell reference. As a simple example, the range of data to be summed in the SUM formula may not contain all the items you want to sum.
Semantic errors: For example, the name of the function is misspelled, in which case Excel will return the error #NAME?
Errors in array formulas: When you enter an array formula, you must press Ctrl + Sift + Enter when you finish typing. If you don't do this, Excel won't realize it's an array formula and will return an error or incorrect result.
Errors in incomplete calculations: In this case, the formulas are not fully calculated. To make sure that all formulas are recalculated, type Ctrl + Alt + Shift + F9.
The easiest way is to find and correct syntax errors. More often than not, you know when a formula contains a syntax error. For example, Excel will not allow you to enter a formula with inconsistent parentheses. Other situations syntax errors lead to the conclusion following errors in a sheet cell.
Error #DIV/0!
If you create a formula that divides by zero, Excel will return the error #DIV/0!
Since Excel treats an empty cell as zero, dividing by an empty cell will also return an error. This problem often occurs when creating a formula for data that has not yet been entered. The formula in cell D4 has been stretched across the entire range (=C4/B4).
This formula returns the ratio of the values of columns C to B. Since not all data for days was entered, the formula returned the error #DIV/0!
To avoid the error, you can use , to check whether the cells of column B are empty or not:
IF(B4=0;"";C4/B4)
This formula will return a blank value if cell B4 is empty or contains 0, otherwise you will see the counted value.
Another approach is to use the ISERROR function, which checks for an error. The following formula will return an empty string if the expression C4/B4 returns an error:
IFERROR(C4/B4;"")
Error #N/A
The #N/A error occurs when the cell referenced by the formula contains #N/A.
Typically, the #N/A error is returned as a result of running . In the case where no match was found.
To catch the error and display an empty cell, use the =ESND() function.
ESND(VLOOKUP(A1,B1:D30,3,0);"")
Please note that the ESND function is new feature in Excel 2013. For compatibility with previous versions use an analogue of this function:
IF(END(VLOOKUP(A1,B1:D30,3,0));"";VLOOKUP(A1,B1:D30,3,0))
Error #NAME?
Excel may return the error #NAME? in the following cases:
- Formula contains an undefined named range
- The formula contains text that Excel interprets as an undefined named range. For example, a misspelled function name will return the error #NAME?
- The formula contains text not enclosed in quotation marks
- The formula contains a reference to a range that does not have a colon between the cell addresses
- The formula uses a worksheet function that was defined by an add-in, but the add-in was not installed
Error #EMPTY!
Error #EMPTY! occurs when a formula tries to use the intersection of two ranges that do not actually intersect. The intersection operator in Excel is space. The following formula will return #EMPTY! because the ranges do not overlap.
Error #NUMBER!
Error #NUMBER! will be refunded in the following cases:
- A non-numeric value was entered into a formula's numeric argument (for example, $1,000 instead of 1000)
- An invalid argument was entered into the formula (for example, =ROOT(-12))
- A function that uses iteration cannot calculate the result. Examples of functions using iteration: VSD(), BET()
- The formula returns a value that is too large or too small. Excel supports values between -1E-307 and 1E-307.
Error #LINK!
- You deleted a column or row that was referenced by a formula cell. For example, the following formula will return an error if the first row or columns A or B were deleted:
- You deleted the worksheet that was referenced by a formula cell. For example, the following formula will return an error if Sheet1 was removed:
- You copied the formula to a location where the relative reference becomes invalid. For example, if you copy a formula from cell A2 to cell A1, the formula will return a #REF! error because it is trying to reference a cell that does not exist.
- You cut the cell and then paste it into the cell referenced by the formula. In this case, the error #LINK! will be returned.
Error #VALUE!
Error #VALUE! is the most common error and occurs in the following situations:
- The function argument has an incorrect data type or the formula is attempting to perform an operation using incorrect data. For example, when trying to add a numeric value to a text value, the formula will return an error
- Function argument is a range when it should be a single value
- Custom sheet functions are not calculated. To force a recalculation, press Ctrl + Alt + F9
- A custom worksheet function attempts to perform an operation that is not valid. For example, custom function cannot change the Excel environment or make changes to other cells
- You forgot to press Ctrl + Shift + Enter when entering an array formula
After entering or adjusting a formula, as well as when changing any value of a function, it happens that a formula error appears rather than the required value. In total, the spreadsheet editor recognizes seven main types of such incorrect calculations. We'll look at what errors look like in Excel and how to fix them below.
Below we will present a description of the formulas shown in the picture with detailed information on each error.
1. #CASE!– “division by 0”, most often occurs when trying to divide by zero. That is, the formula embedded in the cell, performing the function of division, comes across a cell where zero value or it is “Empty”. 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 entry of incorrect values into some functions, such as =AVERAGE(), if during the calculation the value range contains 0. The same result will be caused by empty cells accessed by a formula that requires specific data for calculation.
2. #N/A- "no data". This is how Excel marks values that are not clear to the formula (function). By entering inappropriate numbers into a function, you are sure to trigger this error. When it appears, make sure that all input cells are filled out correctly, and especially in those where the same inscription is illuminated. Often found when used
3. #NAME? – “invalid name”, an indicator of an incorrect name of the formula or some part of it. The problem disappears if you check and correct all the titles and names accompanying the calculation algorithm.
4. #EMPTY!– “there is an empty value in the range”, a signal that somewhere in the calculation there are non-overlapping areas or a space is inserted between the specified ranges. Quite a rare mistake. An erroneous entry may 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 specified range.
6. #LINK!– warns that cells associated with this formula have disappeared. Check to see if the cells specified in the formula have been deleted.
7. #VALUE!– the type of argument for the function operation is incorrectly selected.
8. Bonus, error ##### — the cell width is not sufficient to display the entire number
Excel also issues a warning about an incorrect formula. The program will try to tell you exactly how to place punctuation (for example, parentheses). If the proposed option meets your requirements, click “Yes”. If a hint requires manual adjustment. Then select “No” and rearrange the brackets yourself.
Errors in Excel. Using the ERROR() function for Excel 2003
The function helps to eliminate errors in Excel well. It works by finding errors in cells; if it finds an error in the formula, it returns TRUE and vice versa. In combination with =IF(), it will allow you to replace the value 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, empty ("") will be returned. If everything went correctly (i.e. ERROR (A1/A2) = FALSE), then A1/A2 is calculated.
Errors in Excel.Using IFERROR() for Excel 2007 and higher
One of the reasons why I quickly switched to Excel 2007 was IFERROR() (the main reason is )
The iferror function contains the capabilities of both functions - ERROR() 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” there is a calculated expression/link to the cell, and instead of “value in case of error” - what should be returned if an inaccuracy occurs, for example, if when calculating A1/A2 #CASE! then the formula will look like this:
IFERROR(A1/A2;””)
» download here
I enter a formula, but instead of the result I see the text #N/A or #####. What does it mean?
This means that there was an error in the source data, the formula itself, formatting, or in another case. The error message contains an indication of where exactly the error occurred. Below we will look at error messages and their most common causes:
Error #####
Most often, this error means that the column is not wide enough to display the contents of the cell. To solve the problem, just increase the column width.
Another reason for the error is that the cell contains a date or time with negative values.
Error #N/A
This error occurs if a function or formula contains a reference to a value (or the value itself) that does not exist. In other words, a required field is not filled in a function or formula, or there is a reference to an empty cell when the cell must contain a value. The most common reasons:
- The function is missing one or more required arguments.
- The VLOOKUP, GLOOKUP, LOOKUP, or MATCH functions specified an incorrect value for the lookup_value argument.
- The VLOOKUP, HLOOKUP, or MATCH functions are used to search an unsorted table.
- In the original table, #N/A or ND() was entered instead of missing data.
Error #DIV/0!
Error #DIV/0! Indicates that the calculation process involves division by zero. The reasons may be the following:
- The formula explicitly specifies division by zero (for example, =10/0).
- In a formula or function, the divisor is a reference to an empty cell or a cell that contains zero.
Error #EMPTY!
This error occurs when two intersecting ranges are specified in the conditions of a function, but they do not actually intersect.
Note: In some tasks, the user needs to perform actions only on those cells that are simultaneously present in two ranges. To do this, cell ranges in a function are separated not by a semicolon, but by a space (which is called the intersection operator).
- If the function must not specify overlapping ranges (for example, when summing), it is likely that a space was used as the range separator rather than a semicolon. Or a space was used when specifying a range, instead of a colon.
- If the function must specify overlapping ranges, it is likely that the ranges do not actually have common cells. You need to check the specified ranges in the function arguments.
Error #NUMBER!
This error occurs if incorrect numeric values are specified in a formula or function. For example:
- In a function that uses a numeric argument, the argument is text or a cell reference with a non-numeric value.
- A function that iterates (a calculation that repeats until a specified condition, such as IRR or BET, is met) cannot calculate a result (for example, the specified condition has not occurred).
- The result of the calculation is a number that is too large or too small to be used in Excel. We remind you that the maximum number in Excel cannot exceed 10307 and be less than minus 10307.
A message like this tells us that the cell reference is invalid. Most often, this may indicate that the cells referenced by the formula have been deleted, or other values have been copied into those cells.
Error #VALUE!
This error occurs when the wrong type of argument or operand is used. For example, a user might get the following wrong:
- Text is entered into the formula instead of a number or logical value (TRUE or FALSE).
- After entering or editing an array formula, press ENTER instead of CTRL+SHIFT+ENTER.
- The reference, formula, or function is specified as an array.
- A range is specified as a function condition that requires a single value.
I understood what the error means. But my formula is very large/works with a large amount of data. How can I find where the mistake was made?
Excel 2007 can point out the error right away, or it can help you review calculations step by step to find the error. To do this, click on the icon that appears next to the cell with the error message. If the formula consists of only one function or the reason for the error is quite simple, the context menu will contain an item Source of error. After clicking on this item, Excel will show you a cell with incorrect data automatically:
If the formula contains several functions, then context menu The Show calculation steps option appears. In the window that opens, you can see which calculation is causing the error.
I have a #NAME error? and I can't find the reason for it.
- Check the spelling of the function name(s). To do this, you can call the Function Wizard (Function Library group of the Formulas tab), and select the desired function from the proposed list.
- Text that was not included in the formula was entered into the formula. double quotes. Let's say the result of the formula should have been a text with a number - for example, Total 200 rubles. When you enter the text Total in a formula, if the text is not enclosed in double quotation marks, Excel interprets the data as a name, which causes an error.
Note: A name is a word, phrase, or other set of characters specified by the user that identifies a cell, range of cells, formula, or constant.
- The formula or function uses a name that has not been defined. Check that the given name exists. On the Formulas tab, in the Named Cells group, select Name Manager and see if the name is in the list. If this name is missing, you can add it by selecting Create Name.
- There was a mistake in spelling the name. To verify that you have entered the correct names in the formula bar, press F3, click the name you want, and then click OK.
- The formula or function uses a reference to another worksheet that is not enclosed in single quotation marks. If the name of another sheet or workbook contains non-letter characters or a space, then the name must be enclosed in apostrophes (").
I have a huge database of clients - buyers. Every day, a column is added to the database indicating the amount of revenue from each customer. If the buyer has not purchased anything, #N/A is placed in the cell. How can I sum a column if there is such data there?
The easiest way to solve this problem is to change the principle of filling the database and not fill in cells for customers who have not made purchases.
But if the main table cannot be changed (for various reasons), you can try to solve this problem using the BDSUMM function.
Let's say the source table looks like this:
By using the BDSUMM function, we can select for summing those cells that do not contain the #N/A value. The BDSUMM function has the following syntax:
BDSUMM(database, field, conditions) , where
- database_is the range of cells with which actions will be performed.
- field is the column from which the values will be summed.
- conditions are a set of conditions that must be true for a cell's value in order for that value to be summed.
That is, in the function we need to set the summation of the first column of our table, provided that the cell value of this column is not equal to #N/A. To do this, first create a condition - in any convenient place on the worksheet we will write the condition #N/A and title this cell in the same way as the column of the database we need.
BDSUMM(C2:C18;1;E2:E3) where
- C2:C18 is our table,
- E2:E3 is our condition.
Please note that database and condition ranges must contain column headers! In this case, the title of the condition must match the title of the column on which the calculations are performed!
Our result is shown below the table:
If our database contains errors different types, it is enough to add them to the conditions (and, accordingly, expand the range of conditions in the formula) in order to get the desired result.
This method can also be used for the functions COUNT, BCOUNT, DMAX, DMIN, BDPRODUCT and the like.
