Common mistakes in using Excel functions

1. #### Width error

Where it happens: When you enter a value that is not wide enough to display.

Common mistakes in using Excel functions Picture 1

Overcome:

Common mistakes in using Excel functions Picture 2

Result:

Common mistakes in using Excel functions Picture 3

2. #VALUE! Error value

Where happens:

1. You enter a string formula while the function requires a number or a logical value.
2. Entering one or editing the components of the formula that press Enter.
3. Enter too many parameters for an operator or a function while they only use one parameter. Example = LEFT (A2: A5)
4. Executes a marco (automated command) involving a function that returns the #VALUE! Error.
5. Appears when in formulas there are calculations that are not of the same data type. For example, the formula = A1 + A2, where A1 has a value of 1, A2 is Tay Ninh.

Fix: To fix, you must edit for the values ​​of operations with the same data type as arithmetic characters or string literals.

3. # DIV / 0! Error divided by 0

Occurrence: Enter the divisor formula as 0. Example = MOD (10.0).

Cause: The divisor in the formula is a reference to a blank cell.

Fix: do not let divided numbers be zeros

4. #NAME! Wrong name

Where happens:

1. You use non-permanent functions in Excel such as EDATE, EOMONTH, NETWORKDAYS, WORKDAYS, . Then you need to go to Tools - Add-in menu. Tick ​​the Analysis ToolPak utility.
2. Enter the wrong name for a function. This case happens when you use Vietnamese keyboard in Telex mode to accidentally make a function name like IF become Ì, VLOOKUP to VLÔKUP.
3. Use illegal characters in formulas.
4. Enter a string in the formula without closing and opening quotation marks. ""
5. No colon: in the cell address range in the formula.

1. Excel does not recognize the name in the formula. For example, the function = SM (BBB), in this function, both the formula name and the array name are referenced.

Fix: you must correct the function name or enter the correct array to reference.

5. # N / A Data error

Where happens:

1. Incompatible return values ​​from [color =] [/ color] lookup functions like VLOOKUP, HLOOKUP, LOOKUP or MATCH.
2. Use HLOOKUP, VLOOKUP, MATCH to return a value in an unsorted table.

Reason:

1. Inconsistent data when you use array addresses in Excel.
2. Forget one or more arguments in custom functions.
3. Using an improperly created function
4. Occurs when the formula refers to a cell in the array but the cell has no value. This error often occurs with search functions like VLOOKUP, HLOOKUP .

Fix: You just need to review the formula and edit the cell that the function refers to.

6. #REF! Wrong reference region

Where it happens: Delete the cells that are referenced by the formula. Or paste the values ​​generated from the formula into the formula reference area itself.

Reason:

1. This is a cell reference error, for example, if you delete a row or column that is used in a formula, the #REF!
2. Link or reference to an application that cannot be run.

7. #NUM! Number data error

Where happens:

1. Using an inappropriate argument in a formula that uses argument as numeric data For example, you enter a negative number formula while it only calculates positive numbers.
2. Using the function repeatedly leads to a function that cannot find the return result.
3. Using a function returns a number that is too big or too small for Excel's calculation capability.

1. The numeric value in the formula is invalid, the number is too large or too small. For example, the calculation = 99 ^ 999 will get the #NUM! Error, you just need to reduce the calculation value again.

Fix: use arguments, functions, and values ​​correctly.

8. #NULL! Empty data error

Where happened

1. Using an inappropriate sequence of operators
2. Use an array without separators.
3. This error occurs when using operators like = SUM (A1: A5 B1: B5), there is no intersection between these two functions,

Fix: using the appropriate sequence of operators, using delimited arrays, you must specify the exact intersection point between the two functions.