Simple Excel if and Function formula help

[Jyotirmaya]

I want a formula that If sum of H1 & I1 less than or equal to 2 and if I1 is greater than 0 then H1*1000+I1*500 else H1*1000+I1*500

 

[Trebor76]

Both the True and False part of your proposed formula are the same i.e. H1*1000+I1*500? If this is correct you don't need an IF statement?

 

[Jyotirmaya]

I want a formula that If sum of H1 & I1 less than or equal to 2 and if I1 is greater than 0 then H1*1000+I1*500 else H1*1000+I1*500

Yes but if i1 is greater than 0 then h1*1000+i1*500 & if i1 is more than 0 then also h*1000+i1*500

 

[Trebor76]

I still think this is flawed as the calculation will happen regardless of whether the IF statement is True or False but anyway:

=IF(AND(SUM($H$1:$I$1)<=2,I1>0),H1*1000+I1*500,H1*1000+I1*500)

 

[Jyotirmaya]

I still think this is flawed as the calculation will happen regardless of whether the IF statement is True or False but anyway:

=IF(AND(SUM($H$1:$I$1)<=2,I1>0),H1*1000+I1*500,H1*1000+I1*500)

I wanted that if the SUM of H1&I1 less then or equal to 2 the code should work but the code is working even if the area is more than that.

 

[Trebor76]

That's what I was trying to say - regardless of what condition the formula will be returned as both the True and False part of the IF statement are the same

So in words if the sum of cells H1 and I1 and I1 is greater than 0 what do you want to return (True part of IF statement)? If these conditions are not meet what should be returned (False part of IF statement)?

 

[Vikas Kumar]

Give a try by ctrl+shift+enter instead of just enter with same formula.

 

[Jyotirmaya]

That's what I was trying to say - regardless of what condition the formula will be returned as both the True and False part of the IF statement are the same

So in words if the sum of cells H1 and I1 and I1 is greater than 0 what do you want to return (True part of IF statement)? If these conditions are not meet what should be returned (False part of IF statement)?

=IF(AND(SUM($H$1:$I$1)<=2,I1>0),H1*1000+I1*500,H1*1000+I1*500)


If these conditions are not made then I want that If H:I value is more than 2 then it should check How much value is more,
if H column itself is more than or equal to 2 then direct formula will apply that H1*1000
if both H & I column sum value is more than 2 then it will check how much H column value & How much I column value ? for example if H=1.8 & I =1.5 then the Formula should be like 1.8*1000+0.2*500 Because in my data base 2 (1.8+0.2) is the maximum number on which my calculation are dependent.
Similarly If H is having value 1 & i is 2 then the formula will be 1*1000+1*500. H will be the priority in the calculation to achive value 2.
Hence only SUM of $H$1:$I$1 shouldn't be more than 2.
Kindly suggest

 

[admiral100]

Hi,

Please try to explain what results are desired for the following examples:


h = 2, i = 0


i = 2, h = 0


h = 1.8, i = 0


h = 3, i = 3


h = 3, i = 1.5


h = 3, i = 0

 

[Jyotirmaya]

Hi,

Please try to explain what results are desired for the following examples:


h = 2, i = 0


i = 2, h = 0


h = 1.8, i = 0


h = 3, i = 3


h = 3, i = 1.5


h = 3, i = 0

h = 2, i = 0 2*1000


i = 2, h = 0. 2*500


h = 1.8, i = 0. 1.8*1000


h = 3, i = 3. 2*1000


h = 3, i = 1.5. 2*1000


h = 3, i = 0. 2*1000

The value for H column is 1000 & for I column is 500