excel If and Formula

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
214
Office Version
  1. 2019
Platform
  1. Windows
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,225,754
Messages
6,186,826
Members
453,377
Latest member
JoyousOne

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
Back
Top