Answered by:
Microsoft Excel IF statement not working, Excel 2010

For some odd reason, I can not get the =IF function to work in Excel 2010. I have the full version of Microsoft Excel 2010 installed from Microsoft Office Professional Plus 2010.
I originally used the Starter version and it did not work, so I assumed it was because of the limited functionality. Now I've tried on a fully upgraded version, and I still keep getting function error.
This is the function I used:
In cell A1, I put the value of 1.
In cell B2, I put: =IF(A1=1,"true","false") and it told me there was an error.
I then used the function wizard. In the first textbox I put: A1=1 for the logic_test and it came out as TRUE.
In the second textbox I put true for value_if_true and in the third textbox I put false for value_if_false. However, in the function wizard screen, it could not tell me which value A1=1 would give.
I confirmed the wizard function and it produced =IF(A1=1 true false) and it still gave me a Function Error! Even though this function was produced by Excel itself.
I also tried =IF(A1=1 "true" "false"). I've tried it almost every way. I went on my laptop which has a full version of Microsoft Excel 2007 and tried the exact same function there, and it worked. The Wizard showed that Excel can determine if A1=1 is TRUE or FALSE, but for some reason it has an error after that.
Question
Answers

Found the problem.
List Separator (in Regional and Language Options) was set as " ".
For some reason, this created an error. I set it to "," and it works perfectly now.
If anyone else has this error, make sure you check all your regional settings.
 Marked as answer by SenGuyfaux Friday, August 03, 2012 3:53 PM
All replies

In itself, the formula =A1=1 should do what you want, but I am curious why IF doesn't work for you.
What is the decimal separator on your system? Comma or point? If it's the comma, you could try
=IF(A1=1; "True"; "False")
with semicolon ; between the arguments.
Regards, Hans Vogelaar

Hi,
You said that there is a Function Error, and what the error is?
Based on my research, there are many reasons to cause this issue:
 You may have accidentally pressed CTRL+` (back quote symbol, the key below escape key in your keyboard) or activated the “Show Formulas” mode in Excel.
 You may have set the cell formatting to “Text” and then typed the formula in it.
Just select the cell, set its formatting to “General”. Now edit the formula and press enter.
Jaynet Zhang
TechNet Community Support

In itself, the formula =A1=1 should do what you want, but I am curious why IF doesn't work for you.
What is the decimal separator on your system? Comma or point? If it's the comma, you could try
=IF(A1=1; "True"; "False")
with semicolon ; between the arguments.
Regards, Hans Vogelaar
No dice on the =IF(A1=1; "True"; "False") trick. It still gives me an error.
"The formula you typed contains an error.
For information about fixing common formula problems, click Help.
To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
If you are not trying to enter a formula, avoid using an equal sign (=) or a minus sign (), or precede it with a single quotation mark (')."
I've tried the second option, using the Wizard to create the IF function, but it still gives me this error.

Found the problem.
List Separator (in Regional and Language Options) was set as " ".
For some reason, this created an error. I set it to "," and it works perfectly now.
If anyone else has this error, make sure you check all your regional settings.
 Marked as answer by SenGuyfaux Friday, August 03, 2012 3:53 PM


Thank you likewise for advising this fix. I have been having experiencing problems with Vlookup, Mid etc.... formulas after migrating from Windows 7 to Windows 10. I also rechecked using Excel Insert Function which worked perfectly and noticed it was referencing ";" as the list seperator. Formulas are now working
[Search "Region", Click "Additional Settings" to locate "List Seperator"]
 Edited by Patrick Baron77 Saturday, August 08, 2015 5:26 AM