Excel file is all about formulas but what if the excel formulas are not working. This is really frustrating as this can hinder the entire job. So, troubleshooting an Excel worksheet’s formulas and functions are really important and this is a very difficult job. Well, there are many reasons due to which the Excel while gets corrupted and the Formulas not working error.
Here in this article, we are going to describe the most common mistakes while making formulas in Excel and also how to fix the formula that is not calculating or updating automatically.
Also Read: Easy Ways to Fix Corrupt Excel File Surely
Fix Excel Formulas Not Working Error
1. Enter numbers without any formatting
This is very important to enter number very carefully as a small mistake become a big problem later. So while using Excel formula, avoid adding any decimal separator or any currency sign like $ or €. As you may know that in Excel a simple comma is used for separating function arguments and the dollar sign makes an absolute cell reference.
2. Enter required arguments in an Excel function
Each and every excel function is having one or more required arguments, some functions are enclosed in [square brackets] in the formula’s syntax. So it is essential that the formula must contain required arguments, or else Excel with start displaying “You’ve entered too few arguments for this function” alert.
But if the entered arguments are more that the required that it will display “You’ve entered too many arguments for this function” error message, so check for the required arguments in an Excel function to avoid further errors.
3. Include workbook and worksheet names in single quotes
While referring to other worksheets or workbooks that are having spaces or non-alphabetic characters in their names, and then you should enclose the names in single quotation marks.
For instance Reference to another sheet:
=SUM(‘Jan Sales’!B2:B10)
Reference to another workbook:
=SUM(‘[2015 Sales.xlsx]Jan sales’!B2:B10)
4. Don't include numbers in double quotes
Any value included in the double quotes in Excel formulas is interpreted as a text string.
For instance: If you enter a formula like =IF(A1>0, "1"), Excel treat the number 1 as text, and this is the reason you are not able to utilize the returned 1’s in the calculations. To get rid of this remove the double quotes around “1”: =IF(A1>0, 1).
So, remember whenever you are writing a formula for the numeric value in excel sheet, don’t enclose the number in the double quotes, unless this is treated as a text and the number is not included in the calculation.
5. Separate function arguments with a proper character
Generally, we all have the habit of separating text with commas and we do same in the Excel. But this does not work in everyone Excel. The character that you need to separate arguments depends on the List Separator set in Regional Settings.
In North America and some of the other countries, Comma is the default list separator. However, in European countries, a comma is utilized as a decimal symbol and the list separator is set to a semicolon.
For instance, in North American one should write =IF(A1>0, "OK", "Not OK"), whereas the European users should put the same formula like =IF(A1>0; "OK"; "Not OK").
So if you are getting the error message "We found a problem with this formula..." in your Excel formulas. Then go to Regional Settings (Control Panel > Region and Language > Additional Settings) and check the character that is set as a List Separator there, and utilize the specific character for separating arguments in Excel formulas.
Well, this is all about Excel formulas but if in case your Excel file got corrupted, damaged and the entire data stored in it becomes inaccessible then, in this case, you can make use of the MS Excel Repair Tool. This is best to deal with any sort of corruption errors in Excel file and restore entire data in the preferred location easily.
To Know More About Excel Formula Visit: 9 Top Fixes & Solutions For “Excel Formulas Not Working Error”
0 comments:
Post a Comment