The biggest nightmare that we as professionals who use Microsoft Excel may have is not to be able to use Formulas in our spreadsheets. We have already discussed why Excel Formulas are not working, not updating & calculating or showing formula instead of the result. Today we will look at why Formula is not working correctly and why is it returning error or wrong result. We will summarize the most common mistakes that we make when creating and using Excel Formulas and try to fix them!
Number enclosed with quotes!
If you enter enclose a number with double quotes it will be interpreted as Text by Excel and you won’t get the result needed.
For example, if you use formula like =IF(SUM((A1:A5))>2, “5”), Excel will treat 5 as Text, then you won’t be able to use that 5 in other formulas. To fix this just remove double quotes and type =IF(SUM((A1:A5)>2, 5)
Always enter the full path to other Spreadsheets!
If your formula references other excel workbook, you have to enter entire path to the workbook. The format that you have to use is: [Workbook_name]Sheet_name!Cell_address
for example: =IF(‘P:\Projects\NewYork\Excel\102017\[Floor.xlsx]First’!B12>2, 3 ,4);
If the Spreadsheet is currently open you can just type in the name/sheet/cell address without the path
for example: [Floor.xlsx]First!B12
Use proper character for separating function arguments!
This problem may occur if you are using a new computer because in different regions of the world people are using different character separators. For example, in my country, we are using comma as character separator but in Germany, people are using a semicolon. Here is how a formula will look in the two regions:
If you are using Comma =IF(A1>0, Bad, Good)
If you are using semicolon =IF(A1>0; Bad; Good)
So, if your formulas are not working and you have an error popping up you can go to your Regional Settings and check what character is set as List Separator. Then you can use that character in excel for your formulas.
You can find your Windows Regional Settings under Control panel >> Region and Language >> Additional Settings
Look after the Parentheses!
Arguments of Functions in Excel are entered within the parentheses if you are using complex formulas, you may need to enter more than one set of them. So don’t hurry and take some time to look after opened parentheses and close them. Newer versions of excel pair parentheses with different colors and makes checking them much easier.
Enter all required Arguments!
Excel formulas have one or more required arguments, some of them also have optional arguments, which are enclosed in square brackets [ ]. You need to enter all the required arguments or you will end up with “You’ve entered too few arguments for this function” alert. On the other hand, if you have entered more than allowed arguments you will have this message: “You’ve entered too many arguments for this function”
So don’t forget to look at the formula you are about to use and see how many arguments you need to have in order to use it!
Don’t nest too many formulas!
This was a problem back when we were using excel 2003 because you could nest only up to 7 nested functions in one cell. With the newer versions of Excel, we can nest much more functions in a cell – up to 64!
Don’t use numbers that are formatted as Text!
If your numbers are formatted as Text, Excel formulas won’t work, so make sure all your number cells are formatted as Number or General.
Use numbers without formatting!
When you are using a number in Excel formula, don’t add signs like currency or units for length, area, etc.
We need to remember that in most cases comma is used to separate function’s arguments and dollar sign makes an absolute cell reference.
When you want to have for example cell that shows currency in dollars just go to Format Cells and under Custom make your Cell display a dollar sign after the number.