IF Function not working

[Jomark]

I cannot get the IF function to work even when copying an example from Help
in Excel 2007. I keep getting a message that there is an error in the formula.

For example if I enter =IF(A2<100,"OK","NotOK") I get an error message.
If I use the function wizard and add commas I receive INValid in red
against the entry fields. If I remove the commas everything seem fine until i
try to enter the formula in the cell and it will not accept it and I get the
same error message that the formula is incorrect.
Any suggestions as to what I am doing wrong or is there a setting incorrect
or is there a problem with my copy of Excel. I have tried a repair but to no
avail

 

[Mike H]

Hi,

There's nothing wrong with the formula, what error message do you get?

Mike

 

[Luke M]

What exactly does the error message say?

The commas are definitely neccesary, they seperate arguements in the
formula. You formula looks correct, I too am not sure why it does not work. I
would break it down, try to troubleshoot what's going on.
First, try a simple
=IF(1=1,TRUE,FALSE)
If ok, then
=IF(1=1,"OK","NotOK")
And finally,
=IF(A2<100,"OK",NotOK")

You should be able to copy the formulas straight from here and paste them
into XL.

 

[Shane Devenshire]

Hi,

=IF(A2<100,"OK","NotOK")

It works just fine on my system.

 

[Reitanos]

Is there an error in A2?

Usually, "invalid" indicates that an address or name is not defined
(ie if you type "a 2" instead of "a2" as a reference).

 

[Pete_UK]

Perhaps you need to use semi colons ( ; ) instead of commas in the
formula. This is usually the case if you are using Excel in
continental Europe and some other countries.

Hope this helps.

Pete

 

[Niek Otten]

Some European countries use the semicolon ; as list separator instead of the
comma.
Try
=IF(A2<100;"OK";"NotOK")

But this confuses me:
<If I use the function wizard and add commas >

You don't enter the commas (or semicolons) in the function wizard; you enter
the arguments in separate boxes.
So it is not clear to me what exactly you are doing.

 

[Jomark]

thanks everyone for your responses.

I have tried using the IF function on my Excel 2003 computer and it works
fine.

I suspect that my Excel 2007 is faulty or corrupt as I also tried the Count
function and it also does not work properly. It seems to partially work as it
does not give the error message.

When using the function wizard, entering the arguments properly without
commas eveything looks fine but when trying to transfer the function into the
cell the commas or other delimiters are not added.

I installed Excel 2007 on my laptop from the same Office upgrade disk and
the same problems also appear there, which is why I suspect the faulty disk.

I have tried a repair and that did not work. The next option would be to
uninstall and reinstall but if the program is faulty this will not fix it.

Otherwise i could try downloading the latest from Microsoft and see if that
works.

whether my existing key will work is of course another matter.

 

[Jomark]

I have isolated the problem after obtaining a replacement disk. the function
worked correctly on one computer but not on the one giving the problems.

However, when running in safe mode, the function(s) work as intended so
there is something in Start up or Services that is interfering with Excel. It
will now be a process of elimination to find out what is causing the problem