excel If and Formula

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)
 

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,481
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)
 

Forum statistics

Threads
1,085,565
Messages
5,384,469
Members
401,903
Latest member
MarBer

Some videos you may like

This Week's Hot Topics

Top