Software

Ask something
Question Locked

IF function in Excel 2003 error

0 helpful
15 replies

by peter_mallory

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm experiencing standard MS error message when trying to use the IF function in Excel, even when using the wizard.
Simple example of this error is:
=IF(I3>1 "YES" "NO") where I3 is above 1. the standard error message comes up. Do I need to re-install Excel as as the formula works on other users PCs

15 replies

by Former member

Here is an example: =IF(A10;"100"). Of which comes up as FALSE as a test which should be correct. Look in the help section of excel, it includes a good reference.
I think your formula should look like this:
=IF(I3;"1 "). I hope this helps you.

by stuartbrenton

I'm surprised that it works like that on any user's PC: Excel requires separators between the function's 'arguments' (i.e., those things telling it what to test and the 'what to do if it is true', 'what to do if it is false').
The proper separator is a comma, so your function should read: IF(I3>1,"YES","NO")
which WILL work on any user's computer with any version of Excel.

by erwin.dewolf

In some countries/OS-settings (in Europe ?), the proper separator is a semicolon, so your function should read:
IF(I3>1;"YES";"NO")

by answerman

You are trying to use a separator from an "array formula"... please see the help subject in Excel. Type in "semi-colon" and you'll find the following:

A basic, single-value formula produces a single result from one or more arguments or values; you can enter either a reference to a cell that contains a value or the value itself. In an array formula, where you might usually use a reference to a range of cells, you can instead type the array of values contained within the cells. The array of values you type is called an array constant and generally is used when you do not want to enter each value into a separate cell on the worksheet. To create an array constant, you must do the following:

Enter the values directly into the formula, enclosed in braces ( { } )

Separate values in different columns with commas (,)

Separate values in different rows with semicolons (;)

For example, you can enter {10,20,30,40} in an array formula instead of entering 10, 20, 30, 40 in four cells in one row. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference. To represent the values 10, 20, 30, 40 in one row and 50, 60, 70, 80 in the row immediately below, you would enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.

Hope that helps....

by mdv3441

I thought it was IIF not just IF

=IIF(I3>1,"YES","NO")

by pstarr

It appears that you are missing commas in the formula. Please try this:
=IF(I3>1,"YES","NO")

by Member 32650361

For a simple function like this, you are missng the comma (",") separators. Spaces do not work.

by absolutely

=IF(I3<=1,"NO","YES")
but the commas are necessary for sure!

by peter_mallory

No matter what combination I formula still not working. Also getting error messages with simple LEFT and RIGHT expressions. Think will reinstall app.
thank all for suggestions.

by nealonions

Peter, You don't say exactly what 'standard' error you are getting?

by absolutely

But why not post the text of the other formulas you're trying? Also, what version of Excel are you using? I'm very surprised that adding the necessary commas didn't do the trick for you.

by erwin.dewolf

Answerman,

I assure you that in Belgium (at least on thousands of configurations) we have to use a semicolon in the IF-function in EXCEL 2002 !
Commas don't work !!!

When positioning the mouse over the cell containing "=IF(", EXCEL automatically shows a popup saying:
"IF(logical_test;[value_if_true];[value_if_false)]"

=> WITH SEMICOLONS !!!

by answerman

I scoured the help files in Excel. Anytime you look up and kind of "IF" function, it specifies commas. The ONLY time I am seeing anything described by semi-colons in an IF function is when your calling an array parameter.

I find it incredulous that MS would do something that bizarre. Math is math is math. Numbers are numbers. That is why math (and music) is the universal language. Because I don't have to learn your little way of doing things and you don't have to learn mine.

Also remember, you can embed up to seven IF functions inside of one formula. At any time any of those parameters can point to an array formula, that perhaps calls to another seven embedded functions.... so it all gets real convoluted. But let's keep it simple here...

Your actually saying that Microsoft has two entirely different formula processes to handle mathematical equations, based upon what COUNTRY your from?

Very strange indeed. Perhaps you could copy & paste a piece of a working spreadsheet, showing a formula using an IF function, and calling parameters with semi-colons that ISN'T calling an array? I for one, would really like to see an example of that.

Thanks for the heads-up !!

This discussion is closed to new replies.