Nested IF and OR formulas in excel

user_13978371 0
Viewed 66

I'm trying to get a formula to function in excel (I17) and am missing something, but I can't quite tell where.

Current Formula:

=IF(ISBLANK(H17),”0”,(IF(ISBLANK(J17), IF(OR(H17<G17,H17=G17),"0","1"), IF(OR(H17<J17,H17=J17),"0","1"))))

Essentially, if H17 is blank, then I17 should be 0.

If H17 is not blank, then the formula needs to first look to see if J17 is blank.

If J17 is blank, the formula needs to compare H17 to G17. If H17 is less than or equal to G17, the formula should produce a 0. If H17 is greater than G17, it should produce a 1.

If J17 is not blank, the formula needs to compare H17 to J17. If H17 is less than or equal to J17, the formula should produce a 0. If H17 is greater than J17, it should produce a 1.

if-statement excel
3 Answers

I think you need this:

=IF(ISBLANK(H17),0,IF(ISBLANK(J17),IF(H17<=G17,0,1),IF(H17<=J17,0,1)))

Does your version of Excel support IFS? I think this will do the same thing, not that it's necessarily that much easier to understand:

=IFS(ISBLANK(H18),0,ISBLANK(J18),IF(H18<=G18,0,1),H18<=J18,0,TRUE,1)
user_15719500

You can simplify down to:

=SUM(IF(H1="",0,IF(J1="",H1>G1,H1>J1)))

This will result in either 0, TRUE/FALSE, or TRUE/FALSE. We can convert the boolean responses to 1s and 0s by taking the sum.

user_67064190

Another option:

=(H1<>"")*(((J1<>"")*(H1>J1))+((J1="")*(H1>G1)))
user_48515900
Related