# excel If and Formula

#### Jyotirmaya

##### Board Regular
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

##### Well-known Member
Try this:

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

Robert

#### Jyotirmaya

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
NP. I'm glad we got there in the end 