Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

How to show 0 instead of an error in a cell with a formula

There are situations when many formulas have been created on sheets in a workbook that perform various tasks. Moreover, the formulas were created a long time ago, perhaps even by you. And the formulas return errors. For example #DIV/0! (#DIV/0!) . This error occurs if division by zero occurs inside the formula: = A1 / B1, where B1 is zero or empty. But there may be other errors (#N/A, #VALUE!, etc.). You can change the formula by adding an error check:

=IF(ISERR(A1 / B1),0, A1 / B1)
arguments:
=IF(EOSH(1 argument), 2 argument, 1 argument)
These formulas will work in any version of Excel. True, the EOS function will not process the #N/A (#N/A) error. To process #N/A in the same way, you need to use the ERROR function:
=IF(ISERROR(A1 / B1),0, A1 / B1)
=IF(ISERROR(A1 / B1),0, A1 / B1)
However, further in the text I will use EOSH (since it is shorter) and besides, it is not always necessary to “not see” the #N/A error.
But for Excel versions 2007 and higher, you can use a slightly more optimized function IFERROR:
=IFERROR(A1 / B1 ;0)
=IFERROR(A1 / B1 ,0)
arguments:
=IFERROR(1 argument; 2 argument)

1 argument: expression to calculate
2nd argument: the value or expression that must be returned to the cell if there is an error in the first argument.

Why is IFERROR better and why do I call it more optimized? Let's look at the first formula in more detail:
=IF(EOSH(A1 / B1),0, A1 / B1)
If we calculate step by step, we will see that first the expression A1 / B1 is calculated (i.e. division). And if its result is an error, then EOSH will return TRUE, which will be passed to IF. And then the IF function will return the value from the second argument 0.
But if the result is not erroneous and ISERR returns FALSE, then the function will re-calculate the previously calculated expression: A1 / B1
With the given formula this does not play a special role. But if a formula like VLOOKUP is used with a lookup of several thousand rows, then calculating it twice can significantly increase the time it takes to recalculate the formulas.
The IFERROR function evaluates the expression once, remembers its result, and if it is incorrect, returns what is written as the second argument. If there is no error, then it returns the stored result of calculating the expression from the first argument. Those. the actual calculation occurs once, which will have virtually no effect on the speed of the overall recalculation of formulas.
Therefore, if you have Excel 2007 and higher and the file will not be used in earlier versions, then it makes sense to use IFERROR.

Why should formulas with errors be corrected at all? This is usually done to display data in reports more aesthetically, especially if the reports are then sent to management.

So, there are formulas on the sheet whose errors need to be processed. If there are one or two similar formulas for correction (or even 10-15), then there are almost no problems replacing them manually. But if there are several dozen, or even hundreds of such formulas, the problem takes on almost universal proportions :-). However, the process can be simplified by writing relatively simple code Visual Basic for Application.
For all versions of Excel:

Sub IfIsErrNull() Const sToReturnVal As String = "0" , vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Then MsgBox "Formula processed"

Sub IfIsErrNull() Const sToReturnVal As String = "0" "if it is necessary to return empty instead of zero "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")" If Left(s, 9)<>"IF(ISERR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err .Number Then MsgBox "Unable to convert formula in cell: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

For versions 2007 and higher

Sub IfErrorNull() Const sToReturnVal As String = "0" "if necessary, return empty instead of zero "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String , ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www.site" Exit Sub End If For Each rc In rr If rc.HasFormula Then s = rc.Formula s = Mid(s, 2) ss = "=" & "IFERROR(" & s & ", " & sToReturnVal & ")" If Left(s, 8)<>"IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "Unable to convert formula in cell: "& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "Formula processed", vbInformation, "www.site" End If End Sub

Sub IfErrorNull() Const sToReturnVal As String = "0" "if it is necessary to return empty instead of zero "Const sToReturnVal As String = """""" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect(Selection, ActiveSheet.UsedRange) If rr Is Nothing Then MsgBox "The selected range contains no data", vbInformation, "www..HasFormula Then s = rc.Formula s = Mid(s, 2) ss = " =" & "IFERROR(" & s & "," & sToReturnVal & ")" If Left(s, 8)<>"IFERROR(" Then If rc.HasArray Then rc.FormulaArray = ss Else rc.Formula = ss End If If Err.Number Then ss = rc.Address rc.Select Exit For End If End If End If Next rc If Err.Number Then MsgBox "The formula in the cell cannot be converted: " & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

How it works
If you are not familiar with macros, then first it is better to read how to create and call them: What is a macro and where to look for it? , because It may happen that you do everything correctly, but forget to enable macros and nothing will work.

Copy the above code and go to the VBA editor( Alt+F11), create a standard module ( Insert -Module) and just paste this code into it. Go to the desired Excel workbook and select all the cells whose formulas need to be converted so that in case of an error they return zero. Press Alt+F8, select the code IfIsErrNull(or IfErrorNull, depending on which one you copied) and press Execute.
An error handling function will be added to all formulas in the selected cells. The given codes also take into account:
-if the formula has already used the IFERROR or IF(EOSH) function, then such a formula is not processed;
-the code will also correctly process array functions;
-you can select non-adjacent cells (via Ctrl).
What is the disadvantage: Complex and long array formulas can cause a code error due to the nature of these formulas and their processing from VBA. In this case, the code will write about the impossibility of continuing work and highlight the problematic cell. Therefore, I strongly recommend making replacements on copies of files.
If the error value needs to be replaced with empty, and not zero, then you need the string

"Const sToReturnVal As String = """"""

Remove apostrophe ( " )

You can also this code called by pressing a button (How to create a button to call a macro on a worksheet) or placed in an add-in (How to create your own add-in?) so that you can call it from any file.

And a small addition: try to use the code thoughtfully. Returning an error is not always a problem. For example, when using VLOOKUP, it is sometimes useful to see which values ​​were not found.
I also want to note that it must be applied to actually working formulas. Because if a formula returns #NAME!(#NAME!), then this means that some argument is written incorrectly in the formula and this is an error in writing the formula, and not an error in the calculation result. It is better to analyze such formulas and find the error in order to avoid logical errors in calculations on the worksheet.

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))

Good day, friends!

In this article we will talk about what types of errors in formulasExcel problems that we encounter when working with Excel spreadsheets. I am more than sure that everyone has seen mistakes, but less know how to get rid of them correctly. Still, this knowledge is important, as it will insure you against typical mistakes or will help you quickly and without panic get rid of or correct received errors in formulasExcel.

We can talk a lot about errors in Excel, but let's look at the most common ones, I'll tell you why and how they happen, as well as how to fix them errors in formulasExcel, to display the data correctly.

Well, here’s what it actually is like errors in formulasExcel:

1. Error #####. This is one of the most common and simplest errors in Excel formulas . It only means one thing: the column width is not wide enough to fully display your data. The solution to this problem is very simple, move the mouse cursor to the column border, and while holding down the left button, enlarge the cell until the data begins to be displayed, or double-clicking on the column border will allow you to click on the widest cell in the column.
2. Error #NAME?. This error (#NAME?) occurs in Excel formulas only when the editor cannot recognize the text in the formula (for example, an error in the name of the function due to a typo =SUM(A1:A4). To correct this errors in formulasExcel, you need to read it carefully and correct the error (A1:A4).
3. Error #VALUE!. This error (#VALUE!) You may have this problem when the formula contains an argument whose type is not suitable for your calculations. For example, a text value =A1+B1+C1 has been inserted into your mathematical formula or formula, where C1 is text. The solution to the problem is simple, use a formula that ignores cells that contain text or just remove given value from cell C1.
4. Error #BUSINESS/0. As you can see from the error that appeared in the formula, you simply multiplied your argument by the number 0, and this cannot be done based on mathematical rules. To correct this error, you must change the number so that it does not equal 0 or change the formula, for example, logical, which will avoid the error. =IF(A2=0;””;A1/A2)
5. Error #LINK!. This is one of the most common and confusing mistakes in Excel functions. When you see this error, it means that the formula is referencing a cell that no longer exists. This is especially problematic when you work with large amounts of data in and big amount. When you edit your tables like this errors in formulasExcel They shouldn’t scare you, they are very easy to fix, you just need to have everything return to its place, or, if necessary, manually rewrite the formula, eliminating the erroneous argument from it.

I hope this article is about what they are errors in formulasExcel and correcting them, became useful to you, and you learned something new and interesting for yourself.

See you in new articles!

“Why is the world so arranged that people who know how to live for pleasure never have money, and those who have money have no idea what it means to “waste their life”?
D.B. Show

If Excel cannot evaluate a worksheet formula or function correctly; it will display the error value - for example, #NAME?, #NUMBER!, #VALUE!, #N/A, #EMPTY!, #LINK! - in the cell where the formula is located. Let's look at the types errors in Excel, their possible reasons, and how to eliminate them.

Error #NAME?

Error #NAME appears when a name that is used in a formula has been removed or was not previously defined.

Causes errors #NAME?:

1. If the formula uses a name that has been removed or is undefined.

Excel Errors - Using a Name in a Formula

Troubleshooting: Define a name. How to do this is described in this.

1. Error in writing the function name:

Errors in Excel - Error in writing the MATCH function

Troubleshooting: Check the spelling of the function.

1. The reference to a range of cells is missing a colon (:).

Errors in Excel - Error in writing a range of cells

Troubleshooting: Correct the formula. In the example above it is =SUM(A1:A3).

1. The formula uses text that is not enclosed in double quotes. Excel gives an error, since it treats such text as a name.

Excel Errors - Error in combining text with a number

Troubleshooting: Enclose the formula text in double quotes.

Excel Errors - Merging Text Correctly

Error #NUMBER!

Error #NUMBER! in Excel is displayed if the formula contains an incorrect number. For example:

1. Use a negative number when a positive value is required.

Errors in Excel - Error in the formula, negative argument value in the SQRT function

Troubleshooting: Check that the arguments entered into the function are correct.

1. The formula returns a number that is too large or too small to be represented in Excel.

Excel Errors - Formula Error Due to Value Too Large

Troubleshooting: Adjust the formula so that the result is a number within Excel's accessible range.

Error #VALUE!

This Excel error occurs when an argument of an invalid value is entered in the formula.

Causes of the #VALUE! error:

1. The formula contains spaces, symbols, or text, but must contain a number. For example:

Errors in Excel - Summing numeric and text values

Troubleshooting: Check if the argument types in the formula are set correctly.

1. A range is entered as the function argument, and the function expects a single value to be entered.

Errors in Excel - The VLOOKUP function uses a range as an argument instead of a single value

Troubleshooting: Provide valid arguments to the function.

1. When you use an array formula, you press Enter and Excel displays an error because it treats it as a normal formula.

Troubleshooting: To complete entering the formula, use the key combination Ctrl+Shift+Enter.

Excel Errors - Using an Array Formula

Error #LINK

Errors in Excel - Error in formula due to deleted column A

Troubleshooting: Change the formula.

Error #DIV/0!

This errorExcel occurs when dividing by zero, that is, when a cell reference that contains a zero value or a reference to an empty cell is used as a divisor.

Errors in Excel - Error #DIV/0!

Troubleshooting: Correct the formula.

Error #N/A

#N/A error in Excel means that the formula uses an unavailable value.

Reasons for error #N/A:

1. When using the VLOOKUP, GLOOKUP, VIEW, MATCH functions, the incorrect search_value argument is used:

Errors in Excel - The value you are looking for is not in the array being viewed

Troubleshooting: Set the correct argument to the value you are looking for.

1. Errors in using the VLOOKUP or GLOOKUP functions.

Troubleshooting: see section dedicated

1. Errors in working with arrays: using inappropriate range sizes. For example, array arguments have smaller size than the resulting array:

Excel Errors - Array Formula Errors

Troubleshooting: Adjust the range of formula references to match rows and columns, or enter an array formula into missing cells.

1. The function is missing one or more required arguments.

Errors in Excel - Errors in formula, missing required argument

Troubleshooting: Enter all required function arguments.

Error #EMPTY!

Error #EMPTY! in Excel occurs when a formula uses non-overlapping ranges.

Errors in Excel - Using non-overlapping ranges in the SUM formula

Troubleshooting: Check the spelling of the formula.

Error ####

Reasons for the error

1. The column width is not sufficient to display the contents of the cell.

Excel Errors - Increasing Column Width to Display Value in Cell

Troubleshooting: Increase the width of the column/columns.

1. The cell contains a formula that returns a negative value when calculating a date or time. Date and time in Excel must be positive values.

Excel Errors - Date and hour differences must not be negative

Troubleshooting: Check the spelling of the formula, the number of days or hours was a positive number.

» will display a special error message. Moreover, each type of error is indicated by its own message, is caused by different reasons and, accordingly, requires in various ways permissions.

##### — What does it mean and how to fix it?

These symbols indicate that the column containing numbers is not wide enough for them, or that the date and time entered into the cells in that column contain negative numbers.
In the first case, it is enough to simply increase the column width or change number format data (for example, reduce the number of decimal places).
In the second case, you need to:

• check the formula if the number of days between two dates is calculated;
• if the formula does not contain errors, then you need to change the cell format and switch, for example, from the “Date and Time” format to the “General” or “Number” format.

#VALUE! — What does it mean and how to fix it?

These messages are about using text instead of a number or a Boolean value (TRUE or FALSE). That is, Excel is such a playboy and cannot convert the given text in a cell into the correct data type.
You must ensure that the formula or function references cells that contain valid values.
For example, if cell A2 contains a number and cell A3 contains text, then cell A1 with the formula =A2+A3 will display #VALUE! .

#DIV/0! — What does it mean and how to fix it?

These messages indicate that a cell is dividing a number by 0 (zero) or references to an empty cell are used.

• In the open worksheet window, select the cell with this error and press F2.
• When the formula or function itself is displayed in a cell, and all cells linked by links to this formula or cell are selected, carefully check the values ​​​​in the selected cells and, if necessary, make adjustments to the formula or change links to empty cells.
• Press Enter or the Enter button on the formula bar.

If an empty cell is used as an operand, it is automatically considered equal to zero.

#NAME? — What does it mean and how to fix it?

These symbols indicate that the formula is using a non-existent name or an incorrect operator.

1 option

If a name is used that has not been defined, then the following must be done:

• In the open worksheet window, go to the Formulas tab and in the Defined Names group, click the Name Manager button.
• In the Name Manager window, see if given name on the list.

If this name is missing, then you need to add it according to the instructions “”.

Option 2

If there is an error in the spelling of a name, you need to check its spelling.

• In the open worksheet window, press F3.
• In the “Insert Name” window, select the desired name from the name list and click the “OK” button.
• Make corrections (if necessary) to the formula that appears in the appropriate cell.
• To pin, press Enter.

Option 3

If a formula uses a misspelled function.
For example, SUM(A1:A10) instead of SUM(A1:A10) .

• In the open worksheet window, select the cell with the misspelled function.
• Expand the “Error Source” button menu next to this cell.
• From the list of commands, select Edit in Formula Bar.
• On the formula bar in the name box, the correct spelling of the required formula will be displayed, according to which you can change the erroneous spelling.
• Save the result by clicking the Enter key.

Option 4

If you enter text into a formula that is not enclosed in double quotes, you must check all text entries in the formula and enclose them in double quotes. Otherwise, Excel will try to recognize the given text as the name of a range of cells, even though it is not intended to do so.

Option 5

If a colon is missing in a reference to a range of cells, then to correct it, you need to check the colon sign in the formula in all such references and correct it as necessary.
For example, SUM(A1 A10) instead of SUM(A1:A10) .

Option 6

#N/A - What does it mean and how to fix it?

These symbols indicate that the desired value is not available for the function or formula.

1 option

If missing data, as well as #N/A or ND() were entered into the formula, then #N/A must be replaced with new data.

The designation #N/A is entered into cells for which data is not yet available.

Option 2

If the LOOKUP, LOOKUP, MATCH, or VLOOKUP functions specify an incorrect value for the “lookup_value” argument (for example, a reference to a range of cells, which is not allowed), then you must accordingly specify a reference only to the desired cell.

Option 3

If the required arguments to a standard worksheet function are not supplied, then you must enter all required corresponding function arguments.

Option 4

If a formula uses a formula that is not available in this moment function, you need to verify that the workbook using the worksheet function is open and that the function is working correctly.

Option 5

If you use the VLOOKUP, GLOOKUP, or MATCH functions to view values ​​in an unsorted table, the default table view information should be in ascending order.
The VLOOKUP and GLOOKUP functions contain an "interval_lookup" argument, which allows you to search for a specific value in an unsorted table. However, to find a specific value, the "interval_lookup" argument must be FALSE.
The MATCH function contains a match_type argument that allows you to sort the data for the search. If the corresponding value cannot be found, then it is recommended to set the “matching_type” argument to 0.

Option 6

If an array formula uses an argument that does not match the range specified in the array formula, you must check the formula's reference range to ensure it matches the number of rows and columns, or enter the array formula into fewer cells.

Option 7

If one or more required arguments to a standard or created worksheet function are not specified, you must check and set all required function arguments.

#LINK! — What does it mean and how to fix it?

1 option

If the cell referenced by the formula has been deleted or this cell the value of the copied cells is placed, you need to change the formula to take into account the new references.

Option 2

If you are using an OLE function that is associated with a program that is not running, you must start the required program.

The OLE (Object Linking and Embedding) interface is supported by many various programs and is used to place a document created in one program into another program. For example, you can insert Word document to an Excel workbook and vice versa.

Option 3

Option 4

If you use a macro that calls a macro function that, under certain options, returns the value #LINK! . You must check the function argument to ensure that it refers to valid cells or ranges of cells.

#NUMBER! — What does it mean and how to fix it?

This message is about using incorrect numeric values ​​in a formula or function.

1 option

If an unacceptable value has been inserted into a function that uses a numeric argument, you must check all of the function's arguments and, if necessary, correct the spelling of all numbers and the format of the corresponding cells.

Option 2

If it is impossible to find a result in a function with iteration (selection of parameters), for example “VSD” or “BET”, then you need to try a different initial approximation or change the number of iterations.

Option 3

If the result of a formula calculation is a number that is too large or, conversely, too small for it to be displayed in Excel, then you need to change the formula and ensure that the result is in the range from 1*10307 to 1*10307.

#EMPTY! — What does it mean and how to fix it?

These messages indicate that there are no shared cells when redirect is specified.
cross section of two regions.

1 option

If an incorrect range operator is used, corrections must be made, namely:

• To indicate a reference to a contiguous range of cells, use a colon (:) as a separator between the starting and ending cells of the range. For example, SUM(C1:C20) .
• To indicate a reference to two disjoint ranges, the union operator is used - a semicolon (;). For example, SUM(C1:C20;D1:D20) .

Option 2

If the specified ranges do not have common cells, then you need to change the links to achieve the desired intersection.

Making even a small change in an Excel worksheet can create 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 may not 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 sum 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 syntax error situations result in the following errors being displayed in the worksheet 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, a 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