PDA

View Full Version : Solved: If Statement, need help



Holland
05-19-2011, 08:55 PM
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
05-20-2011, 12:06 AM
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
05-20-2011, 11:37 AM
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
05-20-2011, 11:52 AM
Try this:

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

Holland
05-20-2011, 12:27 PM
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
05-20-2011, 01:05 PM
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
05-20-2011, 03:31 PM
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
05-23-2011, 06:39 PM
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 (http://www.vbaexpress.com/forum/member.php?u=17332)
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
05-23-2011, 06:41 PM
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
05-24-2011, 01:06 AM
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
05-24-2011, 06:26 PM
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