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! , because there is 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/0 is returned in D4, D5, and D6 cells! "error value 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.