Nested IF with different column but same row

[minerva]

Greetings to all Gurus,

I have a question regarding this nested IF function. Is it possible to nest IF function with a different column? For example, normally we would do these as per below:

IF(D12>=2,"0",IF(D12=1,"5",IF(D12=0,"10")))

So I was thinking if the below would be possible:

IF(D12>=2,"0",IF(D12=1,"5",IF(D12=0,"10",IF(E12>=1,"0"))))

Is the Bold function possible? Sorry if this question has been mentioned before as I'm not really sure myself of what keywords to search for this. Do enlighten me. Thanks!

[TMS]

Hi minerva, you can nest/check whatever you wish so long as the logic makes sense.

In this case, if D2 is not, 2, 1, or 0, you check E2 for its value. No problem with that.

I notice though that you don't have a false outcome which means that if none of the conditions are true, the formula will display FALSE.

Regards, TMS

[benishiryo]

welcome to the forum, minerva. yes you can. but question is how does that fit in? the IF formula works from left to right. when D12 is greater or equals to 2, it immediately returns you 0 (point to note: you can remove the double quotes because that's for text. IF(D12>=2,0,...). the IF formula stops right there. only when D12 is NOT greater or equals to 2, not equals to 1 & not equals to 0, it will then test if E12 is greater or equals to 1. so unless you are saying only when D12 is a negative AND E12 is greater or equals to 1, put 0.

or if you have other intentions, you must use the AND or OR. if D12 >=2 AND E12>=1, put 0
=IF(AND(D12>=2,E12>=1),0,IF(D12=1,5,IF(D12=0,10)))

if D12 >=2 OR E12>=1 is:
=IF(OR(D12>=2,E12>=1),0,IF(D12=1,5,IF(D12=0,10)))

[minerva]

Dear Gurus,

Thanks a lot for the fast feedback. What I intended to do was what benishiryo exactly pointed out. I got stuck with function cause I have been putting AND instead of OR as I thought both conditions of the column should be satisfied. Thus, below work perfectly fine:

if D12 >=2 OR E12>=1 is:
=IF(OR(D12>=2,E12>=1),0,IF(D12=1,5,IF(D12=0,10)))

Just for knowledge gaining though, why is it OR and not AND? Anyway, thanks a lot. Really grateful for this.

[benishiryo]

glad to help. =)

the explanation or outcome is what i have told you:
if D12 >=2 AND E12>=1, put 0
if D12 >=2 OR E12>=1, put 0

so the "why" is depending on what you want. AND means all conditions must be met. D12 MUST BE >=2. E12 MUST BE >=1. OR means as long as 1 condition is met, it's fine. that is to say that if D12 & E12 are both 1, the condition of D12 failed but E12 succeeded. so you'll still get 0 as the answer

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[minerva]

It's done. Thanks!

[TMS]

You're welcome. Thanks for the rep.