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 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 returns 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 error 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 lesser number of arguments: “You’ve entered too few arguments for this function” message will be displayed.

For exceeded arguments: “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 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 Excel formula comes with a set of rules to be followed, in addition to usage of quotation marks. These set of rules include:

  1. 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 you the importance of giving appropriate references to the numerical values.

  2. To refer another sheet: =SUM (‘Sales’17’! B2:B10)
  3. To refer another workbook: =SUM (‘[2015 Sales.xlsx] Sales’17’! B2:B10)
  4. 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 update

Whenever your Excel formulas don’t update the values automatically, it could be due to the changed state of 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, and Excel 2016
    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 following hacks to compel recalculation by Excel program:
  • For active sheet, use Shift+F9 tab.
  • For active workbook, use F9 tab.
  • For all the active sheets in all the workbooks, use Ctrl+Alt+F9 tab.
  • For a single formula, use F2 tab.

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:

  1. Either browse to the Show Formulas tab through Formulas menu or use shortcut Ctrl+~.
  2. Change the format of the formula from Text to Number.
  3. 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 entire workbook. You should consider investing in a third-party Excel recovery solution in such 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 Kernel for Excel repair to retrieve data.