Notes when typing formulas to avoid typing errors in Excel

When entering the calculation formula using the functions in excel, sometimes you cannot find the error because of error. The following article gives detailed instructions on how to avoid entering corrupted formulas in excel.

1. Some notes when entering formulas

1.1. Remember to enter the formula starting with =

There are some people new to Excel who often forget the equal sign. When you first start a formula, you must write the equal sign first, otherwise the machine will interpret the character:

Notes when typing formulas to avoid typing errors in Excel Picture 1

1.2. Note that opening and closing parentheses must be sufficient

For formulas that use multiple functions with different arguments, you must remember the number of closing and opening brackets. Normally, the opening and closing parentheses are the same color, so please rely on the color to check the number of brackets.

Notes when typing formulas to avoid typing errors in Excel Picture 2

1.3. Define the right parameters for the data types

Note that the data value must match the data type of the parameter. For example, if the parameter type is a character, the data referenced must also be the character. In this example, the Sum function is used. Cell C5 has a value of a character, so it cannot be used for the Sum function -> the result is incorrect.

Notes when typing formulas to avoid typing errors in Excel Picture 3

1.4. For data types, the characters use double quotes

When the formula uses the parameter value as a string of direct input, pay attention to use quotes to assign the data type as characters.

Notes when typing formulas to avoid typing errors in Excel Picture 4

1.5. Use commas and dots with different versions of excel

For example, decimal numbers in Excel 2010 and later use periods to separate integers and decimals. In excel 2007 and lower use the "," Separator between decimal and integer parts.

Notes when typing formulas to avoid typing errors in Excel Picture 5

1.6. Make sure the data referenced is not deleted

Where you have created the formula and already have the value of the formula. But at some point accidentally you accidentally delete the data that the formula refers to => the error formula.

Notes when typing formulas to avoid typing errors in Excel Picture 6

In some cases, the complex formula will report an error, while the simple formula will delete the error without data.

Notes when typing formulas to avoid typing errors in Excel Picture 7

If you want to save the value of the formula and delete the data referenced, you should use Paste Value.

1.7. The referenced data should not be empty

With some formulas that calculate the data referenced to be blank even if only one value in the data area => the error formula.

Example of blank cell data when entering an error formula:

Notes when typing formulas to avoid typing errors in Excel Picture 8

1.8. Do not use more than 64 functions in 1 formula

You should limit the use of multiple functions in the same formula. If you use more than 64 functions in a formula, the machine will give an error.

1.9. If a recipe has too many nested formulas, check them out by reviewing each one

You have too many functions in the same formula, so when entering you should always check the correctness of the formula. Do not enter all formulas before checking so that you can not find the location to fix.

For example, I want to create an If function that determines the type of goods if frequency = 10.2000 => inventory. If 10.2000 If frequency> 120,000 =. Best seller So I perform inventory identification to see if the formula has been entered correctly and press Enter.

Notes when typing formulas to avoid typing errors in Excel Picture 9

Continue to identify Average Sales Items:

Notes when typing formulas to avoid typing errors in Excel Picture 10

Similarly determine each function 1 if it adds the next function and determine the correctness.

2. Fix some errors when entering formulas

2.1. Error message ######

- Because your column width is too small compared to the data, the error message ####.

Notes when typing formulas to avoid typing errors in Excel Picture 11

- Fix: Change the width of the column accordingly.

Notes when typing formulas to avoid typing errors in Excel Picture 12

2.2. Error message #VALUE!

- In case of an error message when the formula has an incorrect input value or an input parameter of a formula or a function in a formula contains error values.

- Fix: Review formulas and values ​​in functions.

2.3. Error message #NUM!

Due to too many repetitive functions, the return value could not be found.

2.4. The #REF! Error message

The data cell referenced in the formula is deleted or cannot be executed by the relevant application.

2.5. Error message # N / A

The return value type and function value do not match or the argument is missing with the self-created function or the case of using an array but is not consistent in how to get the addresses of the elements.

2.6. Error message # DIV / 0

If the data cell is a divisor with a blank value or = 0, then an error is reported because it is a division operation.

2.7. Error message #NAME!

When you enter the wrong function name or use unsupported excel functions, or enter characters but not in quotation marks, declare the missing sign: in the reference array.

Above are some ways to enter formulas and some errors to help you enter and correct errors of your formulas. Good luck!