Using IF statement with AND to extract 3 Solutions

clemkonan

Active Member
Joined
Jun 21, 2003
Messages
259
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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"
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,224
Members
453,025
Latest member
Hannah_Pham93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top