Excel - Nested if multiple conditions and columns I a have problem that seems so

Question

Excel - Nested if multiple conditions and columns

I a have problem that seems so simple, but I can't wrap my head around it. I have a large database, and i have two separate columns that unfortunately need to stay that way, which together are defining the time of an incident: Column A consists of a number "1" to "12", and Column B consists of cells with either "PM" or "AM". I need a separate column that gives me a "1" for anything that happened between 8 AM and 4 PM and a "0" for all else. Part of the problem are the "12" "PM" rows. That's all I have so far:

=IF(AND(A1>=8, A1<=11),IF(B1="AM",1,0),0)

, which puts out correctly out a "1", if anything happened between 8 AM and 11 AM, but not the rest. I need to combine it with:  

=IF(AND(A1>=1, A1<=4),IF(B1="PM",1,0),0)

and:

=IF(AND(A1=12),IF(B1="PM",1,0),0)

but I have no idea how to combine all of these in one statement at the same time. Can anybody help?

Explanation / Answer

^There are a few approaches; one option is just to add the three calculations together, since they will all equal zero or one:;

=IF(AND(A1>=8, A1<=11),IF(B1="AM",1,0),0) + IF(AND(A1>=1, A1<=4),IF(B1="PM",1,0),0) + IF(AND(A1=12),IF(B1="PM",1,0),0)

Each condition is still being evaluated separately, but your alternate value is zero so they can be added nicely. If this weren't the case, you would then move into some AND and OR statements .
====

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.