# 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)

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### 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)

thanks

#### Trebor76

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

Replies
3
Views
85
Replies
6
Views
64
Replies
10
Views
160
Replies
4
Views
347
Replies
5
Views
211

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back