Solved: If Statement, need help

[Holland]

Hi there,
I'd like to multiply the below by "G2" and then add "B2" the adding part is not the issue but can't get the multiplying part right!

Please see example, is this a formula error? Close to giving up, please help.

=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2)) (works fine)
But
=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>0,(G2*K2))+B2 (does not work)

Please example, need to use G2 as a multiplier with K2 but can't get it right.
Please help and thank you,...

[macropod]

Hi Holland,

You can't use K2 as an input in cell K2 - that creates a circular referrence.

It seems to me the first part of your formula could be reduced to:
=(B2>0)*(H2+I2)
It's not clear what the desired result is, but I think you might get it with:
=(B2>0)*(H2+I2)+(G2>0)*(H2+I2)+B2
or:
=(B2>0)*(H2+I2)+(G2>0)*(B2>0)*(H2+I2)+B2

[Holland]

Hi Paul,
Thank you for the quick reply, but formula does not work. The key is to Multiply G2 (Time In Years) with "H2" and "I2" and then add "B2". Should be an If statement, I believe,.


Example:
If "A2" equals 100.00 and "H2 and I2" are both 10% after calculation you will have 120.00 but I want to Multiply "G2" with "H2 and I2" only so when "G2" equals 1 "K2" call will be 120.00 , if "G2" equals 2 "K2" cell will be 140.00, if "G2" equals 3 "K2" cell will be 160.00 and so on, etc,.

Thanks agn,
Holland

[nepotist]

Try this:

IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>0,(G2*B2*(H2+I2)))+B2

[Holland]

Hi Paul,
Really close, see attached K2 should be 120 when G2= 0 or 1, if G2=2, K2 would = 140.00, if G2=3, K2 would = 160.0 etc,..


Thank you for your help and assistance, just needs more tweaking.

Holland

[nepotist]

Well you need to us how do you calculate the Value in K2, if it is based on the same logic as you mentioned in the previous post, then K2 will be 160 when G2 is 2:

=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>1,(G2*B2*(H2+I2)))+B2

If you still want K2 to be 140 when
=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>1,((G2-1)*B2*(H2+I2)))+B2 I am not sure still what is the logic behind the K2,but it does what you have asked for

[macropod]

formula does not work. The key is to Multiply G2 (Time In Years) with "H2" and "I2" and then add "B2". Should be an If statement, I believe,.

Example:
If "A2" equals 100.00 and "H2 and I2" are both 10% after calculation you will have 120.00 but I want to Multiply "G2" with "H2 and I2" only so when "G2" equals 1 "K2" call will be 120.00 , if "G2" equals 2 "K2" cell will be 140.00, if "G2" equals 3 "K2" cell will be 160.00 and so on, etc,.

To get the 'right' formula you need to provide the 'right' specification. Try:
=B2*((H2+I2)*G2+1)

[Holland]

Well you need to us how do you calculate the Value in K2, if it is based on the same logic as you mentioned in the previous post, then K2 will be 160 when G2 is 2:

=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>1,(G2*B2*(H2+I2)))+B2

If you still want K2 to be 140 when
=IF(B2>0,(B2*I2))+IF(B2>0,(B2*H2))+IF(G2>1,((G2-1)*B2*(H2+I2)))+B2 I am not sure still what is the logic behind the K2,but it does what you have asked for

nepotist
Thank you very much, second formula works great and the If Statements account for G2 being "0" which still calculates correctly.

Thank you again,
Holland

[Holland]

To get the 'right' formula you need to provide the 'right' specification. Try:
=B2*((H2+I2)*G2+1)

Hi Paul,
This formula works as well thank you, need the If Statement though to account for the variable if the "0" is used.

Thank you,
Holland

[macropod]

This formula works as well thank you, need the If Statement though to account for the variable if the "0" is used.

No, you don't need an IF test! Try:
=B2*((H2+I2)*MAX(G2,1)+1)

[Holland]

No, you don't need an IF test! Try:
=B2*((H2+I2)*MAX(G2,1)+1)

Paul,
Thank you this works amazing as well,

Thank you agn,
Holland