Buzz
When entering calculation formulas using Excel functions, errors can sometimes be challenging to locate. This article provides detailed guidance on avoiding formula input errors in Excel.
1. Some Considerations When Inputting Formulas
1.1. Remember to start formulas with the equal sign =
Some newcomers to Excel often forget about the equals sign. When starting a formula, always remember to write the equals sign first; otherwise, the system will interpret it as a character:
1.2. Pay attention to matching opening and closing parentheses.
When using formulas with multiple functions and different arguments, remember the number of opening and closing parentheses. Usually, pairs of parentheses have the same color, so rely on the color to check the number of parentheses.
1.3. Identify the correct parameter corresponding to the data type.
Ensure that data values match the parameter data type. For instance, if the parameter type is character, the referenced data must also be characters. In this example, the SUM function is used. Cell C5 contains a character value, so it cannot be used for the SUM function, resulting in an incorrect outcome.
1.4. For character data types, use double quotation marks.
When the formula uses parameter values as directly input character strings, ensure to use double quotation marks to assign the data type as characters.
1.5. Utilize commas and periods with different versions of Excel.
From Excel 2010 onwards, decimal numbers are formatted using a dot to separate the integer and decimal parts. In Excel 2007 and earlier versions, a comma is used instead of a dot as the decimal separator.
Ensure that data referenced remains intact and is not inadvertently deleted.
After creating a formula and obtaining its value, accidental deletion of the referenced data may lead to formula errors.
Complex formulas may yield errors when data is deleted, while simpler formulas may not produce errors but return incorrect results.
To preserve the value of a formula and remove referenced data, it is advisable to use Paste Value.
Avoid leaving referenced data blank.
For certain computational formulas, leaving referenced data blank, even for just one value in the data range, results in formula errors.
An empty cell within the formula input triggers an error.
Avoid exceeding 64 functions in a single formula.
Limit the use of multiple functions within a single formula. Exceeding 64 functions in one formula will trigger an error message.
If a formula contains too many nested functions, evaluate them individually.
Having an excessive number of functions in one formula requires thorough verification of its accuracy upon input. Avoid verifying all formulas at once, as it may hinder the troubleshooting process.
For example, I want to create an If function to classify items based on frequency: if frequency = 10,2000 => inventory items. If 10,2000 < frequency < 120,000 => Items with average consumption. If frequency > 120,000 => Fast-selling items. To accurately determine inventory items, ensure the formula is entered correctly and press Enter.
Continuing to identify items with average sales:
Similarly, identify each function one by one. If correct, add the next function and verify accuracy.
2. Fixing some errors when entering formulas
2.1. Error Notification: ######
- Due to the narrow column width compared to the data, an error #### is notified.
- Solution: Adjust the column width to fit properly.
2.2. Error Notification: #VALUE!
- This error notification occurs when there are incorrect values inputted into the formula or when there are redundant parameters or a function within the formula contains an error value.
- Solution: Review the formula and the values within the function.
2.3. Error Notification: #NUM!
Occurs due to excessive use of repetitive functions and inability to find a return value.
2.4. Error Notification: #REF!
The data cell referenced in the formula is deleted or the related application cannot be executed.
2.5. Error Notification: #N/A
The return value type and function value are incompatible or there are missing arguments with user-defined functions, or in cases of using an array without consistent addressing of elements.
2.6. Error Notification: #DIV/0
Occurs when the data cell is a divisor with a blank or =0 value, resulting in a division error.
2.7. Error Notification: #NAME!
Occurs when entering an incorrect function name or using unsupported Excel functions, or inputting characters without enclosing them in double quotes, or declaring missing colon : within array references.
Here are some methods for entering formulas and some errors to help you input correctly and troubleshoot your formulas. Wishing you success!
