Seven common formula errors in Excel usage and their solutions

In our daily use of Excel office software, we may encounter some error messages, such as: #value!, # n/a!, #div/o!, etc. This also makes some friends who only know how to apply the software, often confused, don't know what's wrong or how to deal with it. In fact, there are many reasons for these errors, today's software Direct Marketing network focuses on a few common error messages and their solutions.

One: #value!

When we use Excel, an error value #value! is generated if the wrong parameter or Operation object type is used, or when the formula AutoCorrect function does not correct the formula. The main reasons are as follows: three

1: Text is entered when a number or logical value is needed, and Excel cannot convert the text to the correct data type;

Workaround: Confirm that the formula or function requires the correct operator or parameter, and that the formula refers to a cell that contains a valid number. For example, if cell A1 contains a number and cell A2 contains text, the formula = "A1+A2" returns the error value #value!. You can add these two values with the SUM worksheet function (the SUM function ignores the text:) =sum (A1:A2);

2: Input a cell reference, formula, or function as an array constant;

Workaround: Verify that the array constants are not cell references, formulas, or functions.

3: Assign a numeric range to an operator or function that requires a single numeric value;

Workaround: Change the range of values to a single value. Modify the range of values so that it contains the data row or column in which the formula is located.

Two: #n/A

When no numeric value is available in a function or formula, an error value #n/a is generated.

Workaround: If some cells in the worksheet do not have a value for the time being, enter "#n/a" in those cells, and the formula will not count when the cells are referenced, but instead return to #n/a.

Three: #####!

If the cell contains a number, date, or time that is wider than the cell, or if the cell's datetime formula produces a negative value, a #####! is generated. This looks relatively simple, we should all understand it.

Workaround: If the cell contains a number, date, or time that is wider than the cell, you can modify the column width by dragging the width between the lists. If you are using the 1900 date system, the date and time in Excel must be positive. If the formula is correct, you can also change the format of the cell to a non date and time type to display the value.

Four: #div/o!

When the formula is removed by 0, an error value #div/o! is generated. In the specific operation of the main performance of the following two kinds of reasons.

1: In the formula, the divisor uses a cell reference that points to an empty cell or a cell containing a value of 0 (in Excel, if the Operation object is a blank cell, Excel treats this null value as a 0 value:.

Workaround: Modify the cell reference, or enter a value that is not zero in the cell used as a divisor.

2: The input formula contains a significant divisor of 0, for example: Formula =1/0.

Workaround: Change 0 to a value other than 0.