# Using IF statement with AND to extract 3 Solutions

#### clemkonan

##### Active Member
Ok so I have a Health and Safety utility I am creating to determine if a formal accident report is needed. I have:

F3 = First Aid = yes or No

G3 = Medical Attention = yes or no

H3= Modified work = yes or No

Here is what I want my formula to say:

If F3 = yes and G3= no then return "Exempt"

if F3=yes and H3 =yes and G3= no, then return :"exempt"

IF F3= no and H3=yes and G3 =yes , then return "Exempt"

The alternative case in each instance is "Report Required"

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### hotabae

##### Board Regular
Couple quick questions:

1. Before the user does anything, are F3, G3, and H3 blank cells, or do they have a default value?

2. Maybe there's something I'm not seeing, but It seems like these two lines are the same "Case"

If F3 = yes and G3= no then return "Exempt"

if F3=yes and H3 =yes and G3= no, then return :"exempt"

#### hotabae

##### Board Regular
In any case... If you wish for the results to display as shown here:

 First Aid Medical Attention Modified Work Yes Yes Yes Report Required Yes Yes No Report Required No Yes Yes Exempt Yes No Yes Exempt Yes No No Exempt No Yes No Report Required No No Yes Report Required No No No Report Required

<tbody>
</tbody>

It would be a formula like this:

=IF(OR(AND(F3="Yes",G3="No"),AND(F3="No",G3="Yes",H3="Yes")),"Exempt","Report Required")

Edit: If it isn't the result you are looking for, let me know and I'll try to help.
It was a little confusing since F,G, and H were in different orders some times and the two cases appear to be the same.

Last edited:

#### clemkonan

##### Active Member
This may help
When notice is not required
Employers are not required to report a work-related accident if the worker

receives first aid and requires modified work at regular pay for seven calendar days or less, following the date of accident
does not receive first aid, but requires modified work at regular pay for seven calendar days or less, following the date of accident.

Here are the results:

1 Using the formula with First Aid as yes all others as no it returns "Exempt"
2 With first aid and Modified work as yes again it returns "Exempt" which is correct
3 with modified work as the only yes it returns "Report Required" which in incorrect
Conclusion formula need to fix the last condition by capturing that modified work = yes also required medical attention be set as yew which intuitively makes sense.

Sorry I messed up the sequence all cells are blank until set to yes or no. F3 =First Aid, G3=medical attention H3 =modified work

Thanks again

#### hotabae

##### Board Regular
Ummm, I threw this together fast as I'm headed out the door, but give this a shot and tell me if it does what you want it to do.

It will display "Please Complete Form" if the total of yes/no answers is anything other than 3.

Other than that, I added the condition for "Modified work only"

#### clemkonan

##### Active Member
Thanks again I will have to change the spreadsheet design. Why?

1-receives only first aid ( Exempt)
2-receives first aid and requires modified work at regular pay for seven calendar days or less, following the date of accident ( Exempt)
3-does not receive first aid, but requires modified work at regular pay for seven calendar days or less, following the date of accident.(Exempt"

These 3 conditions are conditional on the duration of modified work work so I have to add a field that captures if modified work is >7 days; if I am evaluating this correctly the formula would default to " report Required" if modified work is more than 7 days otherwise it would return , "Exempt"
I should be able construct that formula.

Thanks much.

Replies
23
Views
1K
Replies
0
Views
455
Replies
3
Views
366
Replies
5
Views
3K