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:
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.
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.
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.
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.
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.
In some cases, the complex formula will report an error, while the simple formula will delete the error without data.
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:
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.
Continue to identify Average Sales Items:
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 ####.
- Fix: Change the width of the column accordingly.
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!