Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
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)
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
168
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
Joined
Jul 23, 2007
Messages
4,735
It doesn't work

It works exactly how you asked for it :confused: 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top