nested ifs

[david rose]

Hi all,

I am unfamiliar with nested IF statements.

I want to have a formula evaluate the following:

b1=.005, b2 could =-.004 or b2 could =.004

If b1 is > than 0, then I want to add 1 to b1 and multiply it by 1+b2 if b2 is > 0, or by 1 less the absolute value of b2 if b2 is < 0. so:

1.005*1.004....which is (b1+1)*(b2+1) or 1.005*.996....which is (b1+1)*(1-|b2|).

Thanks for the help
david



Submitted via EggHeadCafe - Software Developer Portal of Choice
Some Advice to n00bs at .NET!
http://www.eggheadcafe.com/tutorial...6d-f998ffc56b0d/some-advice-to-n00bs-at-.aspx

 

[B. R.Ramachandran]

Hi,

Your post says, "If b1 is > than 0, then I want to add 1 to b1 and multiply
it by 1+b2 if b2 is > 0, or by 1 less the absolute value of b2 if b2 is < 0."

You haven't mentioned about what you want when b1 is not greater than zero.

Anyway, from your example, it looks like you don't need any IF statement at
all.

The formula is (1+b1)*(1+b2) regardless of whether b2 is positive or negative.
Please note that when b2 is negative, (1-|b2|) is the same of (1+b2).
For example, if b2 = -0.004, (1-|b2|) =0.996 which is the same as (1+b2).

If this heps, please give a feedback by clicking "Yes".

Regards,
B. R. Ramachandran

 

[Joe User]

I am unfamiliar with nested IF statements.
I want to have a formula evaluate the following: [....]
If b1 is > than 0, then I want to add 1 to b1 and
multiply it by 1+b2 if b2 is > 0, or by 1 less the
absolute value of b2 if b2 is < 0.

As a lesson in nested function calls, this is what you asked for:

=IF(B1>0,(1+B1)*IF(B2>0,1+B2,1-ABS(B2)))

But in this case, your logic is no different than the following:

=IF(B1>0, (1+B1)*(1+B2))

because "1 less the absolute value of b2 if b2 < 0" is simply 1+b2.

In either case, your logic is incomplete. What do you want if B1>0 is
false; that is, B1<0 and B1=0?

Moreover, you neglect to say what you want when when B2=0. It might not be
necessary to handle B2=0 specially, if you want to multiply 1 in that case.

So the following might be sufficient:

=IF(B1<>0, (1+B1)*(1+B2), 0)


----- original message -----