Questions

IF function in Excel 2003 error

+
0 Votes
Locked

IF function in Excel 2003 error

peter_mallory
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
  • +
    0 Votes

    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.

    +
    0 Votes
    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.

    +
    0 Votes
    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")

    +
    0 Votes
    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....

    +
    0 Votes
    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 !!!

    +
    0 Votes
    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 !!

    +
    0 Votes
    mdv3441

    I thought it was IIF not just IF

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

    +
    0 Votes

    ?

    Absolutely

    Not in Excel 2003. What version do you use?

    +
    0 Votes
    pstarr

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

    +
    0 Votes
    user@#

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

    +
    0 Votes
    Absolutely

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

    +
    0 Votes
    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.

    +
    0 Votes
    nealonions

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

    +
    0 Votes
    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.

  • +
    0 Votes

    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.

    +
    0 Votes
    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.

    +
    0 Votes
    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")

    +
    0 Votes
    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....

    +
    0 Votes
    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 !!!

    +
    0 Votes
    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 !!

    +
    0 Votes
    mdv3441

    I thought it was IIF not just IF

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

    +
    0 Votes

    ?

    Absolutely

    Not in Excel 2003. What version do you use?

    +
    0 Votes
    pstarr

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

    +
    0 Votes
    user@#

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

    +
    0 Votes
    Absolutely

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

    +
    0 Votes
    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.

    +
    0 Votes
    nealonions

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

    +
    0 Votes
    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.