Common errors in Excel formula application and their handling

In the process of using Excel to complete a task, the formula is used very much and can solve all kinds of problems. However, this does not mean that the application of the formula will always be smooth, if we use the function and the formula is a little less careful, the formula may return some strange error code, this is not what we want to get results.

When you see these strange error codes, some friends may get flustered or even upset. In fact, any error has its intrinsic reason, below we through the example analysis, and everybody discusses the code that returns the error value according to the formula and the reason, as well as the corresponding processing method, helps the friend to deal with each kind of common mistake easily.

One, #DIV/0! Error

Common Cause: If the formula returns an error value of "#DIV/0!", this is because there is a divisor zero in the formula, or a cell with an empty divisor (Excel also treats blank cells as 0).

Processing method: Change the divisor to a Non-zero value, or use the IF function to control. See the example below for a specific method.

Concrete Example: The worksheet shown in Figure 1, we use formulas to calculate unit prices based on total price and quantity, the formula entered in cell D2 is "=B2/C2", and after you copy the formula to D6 cell, you can see that the "#DIV D5" is returned in D4, D6, and/0! cells. Error values, because they have a divisor of zero or a blank cell.

Assuming that we know that the number of "mouse" is "6", then the error disappears if you enter "6" in cell C4 (Figure 2).

Suppose we don't know the number of "recorder" and "burner" for the time being, and don't want to display error values in D5, D6 cells, then you can use the IF function to control. In cell D2, enter the formula "=if (ISERROR (B2/C2)," ", B2/C2)" and copy to the D6 cell. As you can see, the error values for D5 and D6 disappear because the IF function works. The meaning of the entire formula is: if B2/C2 returns an incorrect value, an empty string is returned, otherwise the calculation is displayed.

Description: Where the function of IsError (value) is to detect whether the value of the parameter value is an error value, and if so, the function returns the value true, otherwise the return value false.

Ii. #N/A error

Common Cause: If the formula returns an error value of "#N/A", this is often because the formula uses the lookup function (VLOOKUP, HLOOKUP, lookup, and so on) and cannot find a matching value.

How to: Check the value being looked up so that it does exist in the first column of the lookup datasheet.

Concrete Example: In the worksheet shown in Figure 4, we want to find the student's English results by entering the number in the A10 cell. The formula in cell B10 is "=vlookup (A10,a2:e6,5,false)", and we entered the number "107" in A10 because of this number, "#N/A" error occurred because there was no matching value in the A2:A6.

If you want to fix this error, you can enter a number in the A10 cell that exists in the A2:A6, such as "102", when the error value is missing (Figure 5).

Description One: The fourth parameter of VLOOKUP in the formula "=vlookup (A10,a2:e6,5,false)", if FALSE, indicates that the lookup_value value must be matched exactly; Indicates that if the value of the exact match lookup_value is not found, the maximum value less than or equal to lookup_value is used.

Description Two: There are some other reasons why there are "#N/a" errors, when the B10 cell with the error value is selected, a smart tag appears, click the tag, and select Help on this error in the pop-up menu (Figure 6) to get a detailed analysis of this error (see Figure 7). With these reasons and solutions suggested, we can gradually fix the error, which applies to other errors as well.

Three, #NAME? Error

Common Cause: If the formula returns an error value of #NAME?, this is often because you use text that is not recognized by Excel in a formula, such as a misspelled name for a function, use a range or cell name that is not defined, and quote text without quotes.

Processing method: According to the specific formula, to analyze the possibility of the error, and to correct it, see the following example.

Example: In the worksheet shown in Figure 8, we want to find the average of the A1:A3 region, the formula in the B4 cell is "=aveage (A1:A3)", and the "#NAME" appears after the carriage return. Error, as shown in Figure 8, because the function "average" was misspelled incorrectly as "Aveage", and Excel was unable to recognize it, so an error occurred. You can correct the error by spelling the function name correctly.

Select C4 Cell, enter the formula "=average (data)", also appear after the "#NAME?" Error (Figure 9). This is because in this formula we use the region name data, but the name is not yet defined, so there is an error.

The correct method is to select the A1:a3 cell range, select the menu name → definition command, open the Define Name dialog box, enter the name "Data" in the text box, and click OK (Figure 10).

When you return to the Excel editing window, you can see that the error is missing (Figure 11).

Select D4 Cell, enter the formula "=if (a1=12, this number is equal to 12, this number is not equal to 12)", after the carriage return appears "#NAME?" Error (for example, 12) because quotation marks are not added when referencing text.

The modified method is to add quotation marks to the referenced text, with special attention to the quotation marks in the English state. The formula is then changed to "=if (a1=12," This number equals 12 "," This number does not equal 12 ")" (Figure 13).

Four, #NUM! Error

Common Cause: If the formula returns an error value of "#NUM!", often because of the following reasons: When a formula requires a numeric parameter, we give it a non-numeric parameter; An invalid argument is given; the value returned by the formula is too large or too small.

Processing method: According to the specific situation of the formula, analyze the possible causes and modify them.

Concrete Example: In the worksheet shown in Figure 14, we require the square root of the number, enter the formula "=SQRT (A2)" In the B2 and copy it to B4 cell, because the number in the A4 is "16" and cannot square the negative, which is an invalid argument, so it appears "#NUM!" Error. The modified method is to change the negative number to a positive number.

V. #VALUE error

Common Cause: If the formula returns an error value of "#VALUE", this is often due to a number of reasons: text-type data is involved in numeric operations, the value type of the function parameter is incorrect; the function argument should be a single value, but it provides an area as an argument; forget to press CTRL + When you enter an array formula Shift+enter key.

How to: Correct the relevant data type or parameter types, provide the correct parameters, and remember to use the Ctrl+shift+enter key to determine when you enter an array formula.

Concrete Example: as shown in Figure 15, the "A2" in cell is a type of text, if the formula "=a2*2" is entered in the B2, the text is involved in the numerical operation, so the error occurs. The correct method is to change the text to a numeric value.

In Figure 16, the formula "=sqrt (A5:A7)" is entered in A8, and for function SQRT, its arguments must be a single argument and cannot be an area, so an error occurs. The correct method is to modify the parameter to a single parameter.

As shown in Figure 17, if you want to use an array formula to find the total value directly, you can enter the formula "{=sum (C3:c7*d3:d7)}" in cell E8, noting that the curly braces are not entered manually, but when the Ctrl+shift+enter key is pressed when the input is complete, automatically added by Excel. If you enter and then use the ENTER key to determine, a "#VALUE" error occurs.

The modified method is: When the E8 cell is selected to activate the formula bar, press the Ctrl+shift+enter key, and you can see that Excel automatically adds curly braces (Figure 18).

Six, #REF! Error

Common Cause: If the formula returns an error value of #REF!, this is often because an invalid cell reference is used in the formula. Typically, these actions cause formulas to reference invalid cells: the cells that are referenced by the formula are deleted, and the formula is copied to the cell that contains the reference itself.

How to: Avoid actions that cause references to be invalid, undo if an error has occurred, and then manipulate the correct method.

Example: In the worksheet in Figure 19, we use formulas to convert numbers representing dates to dates, and in B2 we enter formulas "=date (left (a2,4), MID (a2,5,2), right (a2,2)) and copy to B4 cells."

If you delete the A2:a4 cell, you will receive a "#REF!" Error (Figure 20) because the cells referenced in the formula are deleted.

Do the undo Delete command first, and then copy the B2:b4 cell range to a2:a4, and the "#REF!" appears. Error (Figure 21) because the formula is copied to the cell that contains the reference itself.

Since this is no longer undone, we first remove the data from the A2:A4 and then format the cell as "General" and enter the data shown in Figure 19 in A2:a4.

In order to get good date data, the correct way to do this is to: first copy the B2:B4 to a proper place, such as d2:d4, paste to perform a selective paste, the "value" paste the past. The data in the D2:D4 is then "detached" from column A and column B, and the deletion will not go wrong (see Figure 22).

Note: To get the effect of Figure 22, you need to set the D2:D4 format as "date."

Seven, #NULL! Error

Cause: If the formula returns an error value of "#NULL!", this is often because an incorrect area operator is used or the intersection of a referenced range of cells is empty.

Treatment method: Correct the area operator, and change the reference to intersect.

Example: In the worksheet shown in Figure 23, if you want to sum the A1:A10 and C1:C10 cell ranges, enter the formula "=sum (a1:a10 c1:c10)" In the C11 cell, and the "#NULL!" appears after the carriage return. Error, because the formula refers to two regions that do not intersect, you should use the union operator, which is a comma (,).

The correct method is to add a comma between two discontinuous regions in the formula, and the effect is shown in Figure 24.

This is the way to deal with common errors in Excel formulas. The selected examples in the article are usually the most common situation, please pay attention to experience. The smart tag that is mentioned in Figure 6 to help correct the error is useful, and can be further analyzed if the method you are using is not yet resolved. In short, as long as the idea is correct, patient carefully, Excel formula error will eventually be one by one "annihilation."