The strange sentence with # character in the picture below, I believe you must have encountered it frequently. In fact, these are common error values ​​returned by function formulas in Excel!

Every time they see them, many people may have the following scene in mind——

Today, Xiao E will give you a comprehensive introduction to the causes of these error values, as well as ways to deal with them, to help you easily deal with the error values ​​returned by function formulas in the future!

- 1 -The reason for the wrong value generation

❶ The reason why the "#DIV/0!" error value is generated

As shown in the figure below, when the salesperson calculated "year-on-year", the #DIV/0! error value appeared.

The reason why the "#DIV/0!" error value appears in Excel is that the division operation is used in the formula and the divisor is 0.

For example, in the picture above, when calculating the year-on-year value of "Shyu", since his completion number in the previous period was 0, when using the formula

=(C5-B5)/B5

When calculating year-on-year, the value of cell B5 is 0, so the "#DIV/0!" error value appears.

Among them, DIV is the abbreviation of divisor, and /0 means that the divisor is 0.

❷ Reasons for generating "#NAME?" error value

The '#NAME?' error value occurs when Excel does not recognize text in a formula.

For example, in the picture below, the cell range B2:B9 was originally supposed to be summed, but the SUM function was written as the sume function. Excel could not recognize this function, so the "#NAME?" error value was generated.

A formula was entered in column D and the error value "#NAME?" was generated.

=YoY this month & (C2-B2)/B2

In Excel function formulas, if you want to enter a text value, you need to enclose it in English double quotes. If it is not bracketed, Excel will think that the text is a custom formula name or function name. If it is not found, it will generate a "#NAME?" error value.

❸ The reason why the "#REF!" error value is generated

The #REF! error value is also a common error value returned by a function formula. When the cell reference in the function formula is deleted, a "#REF!" error value will be generated.

Among them, REF in the "#REF!" error value is the English abbreviation of reference (reference). In addition to deleting the cell reference in the original formula, whenever an invalid cell reference is returned in a function formula, "#REF" will be generated. !" error value.

Cells A1:C9 only have 9 rows of data, and the formula here in E2 is

=INDEX(A1:C9,10,1)

The meaning is to return the data of row 10 and column 1 in the cell range A1:C9. Obviously this is a non-existent reference, so the "#REF!" error value is also generated.

❹ Reasons for generating "#NULL!" error value

As shown in the figure below, in order to find the sum of the overlapping parts of two yellow-filled cell areas (i.e. blue-filled cells), you can use the following formula

=SUM(B2:D7 C6:F11)

The space character between B2:D7 and C6:F11 in the formula is a cell range operator, which is used to find the intersection of two cell ranges.

When the two cell ranges do not intersect, the function will generate a "#NULL!" error value. The following formula will generate the "#NULL!" error value.

=SUM(B2:C5 D8:F11)

Because there is no overlapping cell range between the B2:C5 cell range and the D8:F11 cell range.

❺ Reasons for generating "#NUM!" error value

If you enter a numeric parameter that is not supported in the function in Excel, a "#NUM!" error value will be generated.

When using the DEC2BIN function to convert a decimal value to a binary value, the "#NUM!" error value is generated because the second parameter of the DEC2BIN function uses a parameter that exceeds the display range.

❻ Reasons for generating "#N/A" error value

The #N/A error value is also a common error value. If you often use the VLOOKUP function, you will be familiar with it! When the VLOOKUP function cannot find the corresponding information for the value you are looking for, the #N/A error value will be returned.

As shown below, enter the formula in cell G2

=VLOOKUP(F2,$A$1:$C$8,3,0)

There is no "Shuyu" among the salespeople in column A, so the "#N/A" error value is generated in cell G2. Not only the VLOOKUP function, but also the "#N/A" error value will be generated in the lookup reference function if a value being looked up cannot be found.

❼ Reasons for generating "#VALUE!" error value

#VALUE! There are many reasons for generating error values, the most common of which are the following two↓

a. Text participates in numerical operations

When calculating the discounted price in cell C5, due to staff negligence, the text "yuan" was added to the price of 70 in cell A5, turning it into "70 yuan". Excel regarded the data in cell A5 as text, and the text Participates in multiplication operations, resulting in the generation of the "#VALUE!" error value.

b. Entered an array formula and did not press the [Shitf+Ctrl+Enter] key combination to end it.

The following is a classic formula for finding unique values ​​in a single column.

Since the starting formula does not end with the [Shitf+Ctrl+Enter] shortcut key combination, the formula generates the "#VALUE!" error value.

When you use the [Shitf+Ctrl+Enter] shortcut key combination to end the input of the formula, the formula returns the correct value. This is the key point of the array formula.

❽ The reason why the "#####" error value is generated

To be precise, the "#####" error value is not an error value generated by a function formula, it is a display warning in Excel.

When "#####" appears in a cell, there are generally two reasons:

a. When we enter a negative number in a cell and then display the cell format as date or time format, the cell content will display "#####".

The solution to this situation is to change the cell format to regular.

b. When the column width of the cell is not enough to display all the cell contents, the "#####" error value will also appear.

Since the column width of column D is not enough to display the value generated by the formula of D5, "####" is displayed. The correct value can be displayed after double-clicking to adjust the column width.

- 2 - Methods of correcting erroneous values

The above introduces in detail the reasons for the generation of 8 error values ​​in Excel. Next, Xiao E will tell you how to correct these error values.

❶ General principles

All error values ​​have specific reasons for generating them. To avoid generating error values, the first principle is to ensure that the entered function name and function parameters are correct.

For example, the reason why the #NAME? error value is generated is mainly due to the input of a function name or parameter that Excel cannot recognize. At this time, you only need to modify the function name.

Another example is the #REF! error value. You need to be careful not to accidentally delete the referenced cell. In addition, the cell to be referenced must exist. There cannot be a cell range with only 10 rows, and you want to return the 11th row.

❷ When encountering an error value, replace the display principle

Some error values ​​are inevitably generated.

For example, when VLOOKUP cannot find a value, it returns a #N/A error value. Another example is that the divisor when calculating year-on-year calculations is indeed 0.

When encountering these inevitable error values, we can use a universal function IFERROR to display the results as other more meaningful values.

The syntax of the IFERROR function is as follows

=IFERROR(value,value_if_error)

The first parameter value is the formula that returns an error value, and the value_if_error parameter is the return value to be set when the formula returns an error value.

For example, the "#DIV/0!" error value above can be replaced by the following formula

=IFERROR((C5-B5)/B5,"The number completed in the previous period is 0")

Another example is the #N/A error value above, which can be replaced by the following formula:

=IFERROR(VLOOKUP(F2,$A$1:$C$8,3,0),"There is no sales information for this member")

Even if you didn't pay attention when entering the formula, don't panic if you make such an error. I believe that after reading the article, you will already know the causes and solutions of these 8 difficult errors!

Next time, we will be able to deal with these mistakes calmly~