One cannot even think of Excel without formulas. With formulas, calculations are very easy in Excel. But sometimes, the complexity of formulas can cause troubles too. This post highlights some scenarios where your Excel formulas stop functioning properly and the possible ways to resolve the issues like:
- Excel formulas return a wrong value
- Excel formulas fetch old values even after updating new values in the cells
- Excel formulas are not performing calculations
Case 1# Excel formulas return a wrong value
Some possible reasons for Excel returning wrong values are:
1) A fumble in mentioning parenthesis
Needless to say, no Excel functions are complete without parenthesis. You need to open and close each parenthesis carefully, especially if you are dealing with nested functions, as it indicates the order of calculations to be followed. Make sure you have paired every parenthesis appropriately by closing a right parenthesis for every parenthesis starting on the left.
MS Excel displays different colors for every parenthesis to ease pairing for you. Additionally, it shows errors and offers suggestions to make corrections in the formula to create a perfect balance in the formula.
2) No declaration of required arguments
Every Excel function comprises its own set of arguments, while some of them may have optional arguments. The optional arguments are generally enclosed in square brackets []. Make sure that you provide an appropriate number of arguments followed by the syntax of the formula you are using. Both increased and decreased number of arguments will result in an error message.
For a lesser number of arguments: the “You’ve entered too few arguments for this function” message will be displayed.
For exceeded arguments: the “You’ve entered too many arguments for this function” message will be displayed.
3) Nesting too many functions in a formula
When it comes to nesting Excel functions, there are certain limitations that you should remember in order to avoid any hindrances:
For the users of Excel 2016, 2013, 2010, and 2007, you can use 64 nested functions. On the other hand, you can use only 7 nested functions if you are working on Excel 2003.
Make sure you understand these limitations so to perform calculations without any error messages.
4) Over usage of double quotes
While working with Excel formulas, you need to keep one thing in mind that the values enclosed in double quotes are considered as the text string.
If you are using numeric value in any formula like SUMPRODUCT ({0,0,1,0,1}, {75,”100”,125,”125”,150})
The values quoted under the double inverted comma will not be considered as the number. Hence no calculations will be done on them. They will rather be considered as text values. Make sure you remember this rule whenever you write a formula with numerical values.
5) Declaring formatted numbers
Using a numerical value in an Excel formula comes with a set of rules to be followed, in addition to usage of quotation marks. This set of rules include:
- Inappropriate References
Whenever you want to refer to the numerical values in any formula, avoid using any sort of currency symbols or decimal values. The only separator you can use to indicate two values is a comma. Below given images will clearly illustrate the importance of giving appropriate references to the numerical values.
Figure Incorrect Reference
Figure Correct Reference - Incorrect format selection
Incorrect format selection can return wrong results. In the example shown below the column, C shows the values formatted as $ currency, which is why you can get the right amount of total at the end. On the contrary, column D is displaying numerical values but not showing their total. The reason here is cell formatting that has been set to text.
Figure Incorrect Format DeclarationNote: You can either use Ctrl+I or right-click on the desired cell to get the Format Cells option and define the right category of the values you entered or want to enter.
6) Separating function arguments inappropriately
As shown above, in Figure 1, the Excel functions should be carefully used, especially in the declaration of values or separating their arguments. Let’s pick the above example for a clearer understanding of the separation of arguments.
If(C4>0, 1)
The same function argument can be passed in two ways
- If(C4>0, 1) // comma is a separator used in North America and a few other countries.
- If(C4>0; 1)// Semicolon is a separator generally used in European countries.
If you ever encounter an error saying, “We found a problem with this formula…”, you just need to alter your regional settings as Control Panel > Region and Language > Additional Settings to select right character as List Separator to continue working smoothly.
7) Incorrect workbook and worksheet references
It is quite common that you need certain values from another worksheet, workbook, or closed workbook. The rule to provide such references is providing the referenced object’s name under single quotes (‘XYZ’)
- To refer to another sheet: =SUM (‘Sales’17’! B2:B10)
- To refer to another workbook: =SUM (‘[2015 Sales.xlsx] Sales’17’! B2:B10)
- To refer to a closed workbook: =SUM (‘D:\Excel Reports\[Sales.xlsx] Sales’17’! B2:B10)
Case 2# Excel formulas fetch old values even after an update
Whenever your Excel formulas don’t update the values automatically, it could be due to the changed state of the Calculation setting from Automatic to Manual. All you need to do is reset the calculation settings.
For different versions of Excel, you need to follow different path to apply the desired settings:
- In Excel 2003
Go to Tools > Options > Calculation > Calculation > Automatic. - In Excel 2007
Go to Office button > Excel options > Formulas > Workbook Calculation > Automatic. - In Excel 2010, Excel 2013, Excel 2016, and Excel 2019
Browse to File > Options > Formulas, and select Automatic under the Workbook Calculation
But if you want to continue with the manual option, you have to use the following hacks to compel recalculation by Excel program: - For the active sheet, use Shift+F9.
- For an active workbook, use F9.
- For all the active sheets in all the workbooks, use Ctrl+Alt+F9.
- For a single formula, use F2.
Case 3# Excel formulas can’t perform calculations
This sort of issue mainly arises due to three reasons: turning on the Show Formulas mode, changing the category of formula into text, giving extra space, or using apostrophe sign before the equal (=) in a formula.
Their relevant solutions are:
- Either browse to the Show Formulas tab through the Formulas menu or use the shortcut Ctrl+~.
- Change the format of the formula from Text to Number.
- Remove the space or any special character mentioned before the equal sign.
Now that we have discussed all three cases of errors with their solutions, you can resolve your Excel issues without any expert’s help. However, it would be a serious concern if none of the given solutions help you out. It indicates the issue of corruption either in a worksheet or the entire workbook. You should consider investing in a third-party Excel recovery solution in such a case to save your data.
To Sum Up:
Excel has simplified the calculation jobs with its ‘formulas.’ If you encounter any error message while using formulas, you can easily fix them by following the correct format for the formulas. However, if the Excel file itself is corrupt, get a reliable Excel repair solution like Excel repair to retrieve data.
