I'm too argumentative

[peopleschampion]

If I enter =IF(F9<0,F9,IF((F9-G9)<0,0,(F9-G9))) into Excel 97 the formula works fine. If I
enter =IF((F9-G9)<0,0,(F9-G9),if(F9<0,F9)), which to me looks effectively the same, I get an
error message saying I have entered too many arguments for this function. Have I got the
brackets wrong or is there some other reason why the second version doesn't work?

Bryan

 

[Arvi Laanemets]

Hi

The syntax for IF function is:
=IF(ConditionIsTrue, FirstValue, SecondValue)
i.e. there are 3 arguments for IF function.

Your second formula is:
=IF(ConditionIsTrue, FirstValue, SecondValue, ThirdValue)
where ThirdValue is returned by IF function with omitted 3rd argument (when
condition is false, FALSE is returned):
IF(AnotherConditionIsTrue, ThirdValue)

 

[Guest]

Hi,

The format for an If worksheet function is as follows:

=IF(Logical Test, Value if True, Value if False)

Your formula has attempted to create a third.

Logical Test: (F9-G9)<0
Value if True: 0
Value if False: F9-G9
Invalid Argument: if(F9<0,F9)

The first fomula however appears correct.

Regards,
Jon

 

[Arvi Laanemets]

Hi

Thank you for both rapid responses. I think I may be being a bit dim but on the basis that a
formula can contain 7 nested if statements and I have used only 2 in both my formulas I still
can't follow why formula one is incorrect.

You must put nested IF's into formula as one of existing 3 arguments, not as
additional ones. I.e. like this:
=IF(condition1,Value1,IF(condition2,Value2,IF(condition2,Value3,Value4)))
Here the first argument is condition1, the second argument is condition2,
and third one is a formula
IF(condition2,Value2,IF(condition2,Value3,Value4)), etc.

 

[peopleschampion]

Thank you for both rapid responses. I think I may be being a bit dim but on the basis that a
formula can contain 7 nested if statements and I have used only 2 in both my formulas I still
can't follow why formula one is incorrect.

Bryan

 

[Harald Staff]

Pattern:
=IF(Logical Test, Value if True, Value if False)

Your formula:
=IF((F9-G9)<0,0,(F9-G9),if(F9<0,F9)),

Logical test:
=IF((F9-G9)<0,
Value if True:
0,
Value if False:
(F9-G9),
nonsense argument:
if(F9<0,F9)

HTH. Best wishes Harald

 

[Ron Rosenfeld]

If I enter =IF(F9<0,F9,IF((F9-G9)<0,0,(F9-G9))) into Excel 97 the formula works fine. If I
enter =IF((F9-G9)<0,0,(F9-G9),if(F9<0,F9)), which to me looks effectively the same, I get an
error message saying I have entered too many arguments for this function. Have I got the
brackets wrong or is there some other reason why the second version doesn't work?

Bryan

In the second formula, your final "IF" statement is the 4th argument of the
first IF statement.

Since IF statement can only have three arguments, you get the error message:

=IF(
F9-G9)<0, Argument 1
0, Argument 2
(F9-G9), Argument 3
IF(F9<0, Argument 4 (Illegal)
F9))

--ron

 

[JMay]

It should be mentioned (In Passing) that when using the If() statement..
Argument 3 is Optional.
Jim