excel If and Formula

[Jyotirmaya]

I want a formula that If J1 is less than 1000 and H1>0 and I1>0 then the value should be 1000-J1 else 0. I am trying this formula but getting error.


=IF(J1<1000,(AND(H1>0,I1>0)),(1000-J1),0)

 

[Trebor76]

Try this:

=IF(AND(J1<1000,H1>0,I1>0),1000-J1,0)

Robert

 

[Jyotirmaya]

Try this:

=IF(AND(J1<1000,H1>0,I1>0),1000-J1,0)

Robert

It doesn't work

I tried

=IF(AND(J18<1000,H18>0,I18>0),1000-J18,0)

H18 = 0.0022
I18 = 0.0000
J18 = 29.7 ( value arrived from a formula )
K18 =IF(AND(J18<1000,H18>0,I18>0),1000-J18,0)
the result came 0 instead of 970.3

 

[Trebor76]

It doesn't work

It works exactly how you asked for it As I18 has a zero balance it's returning zero - the False part of the IF statement as you asked for?

If cell I18 has a values that equal or are greater than zero try this:

=IF(AND(J18<1000,H18>0,I18>=0),1000-J18,0)

 

[Jyotirmaya]

thanks

 

[Trebor76]

NP. I'm glad we got there in the end