Using IF statement with AND to extract 3 Solutions

[clemkonan]

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"


Thanks for your time.

 

[hotabae]

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"
Could you please clarify?

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

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

 

[hotabae]

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

[TABLE="width: 390"]
<tbody>[TR]
[TD]First Aid[/TD]
[TD]Medical Attention[/TD]
[TD]Modified Work[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Report Required[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Report Required[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Exempt[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Exempt[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Exempt[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Report Required[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Report Required[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Report Required[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[clemkonan]

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

receives only first aid
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]

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.


=IF(NOT(COUNTIF(F3:H3,"yes")+COUNTIF(F3:H3,"No")=3),"Please Complete Form",IF(OR(AND(F3="Yes",G3="No"),AND(F3="No",G3="Yes",H3="Yes"),AND(F3="No",G3="No",H3="Yes")),"Exempt","Report Required"))


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]

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.