Using IF statement with AND to extract 3 Solutions

clemkonan

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

hotabae

Board Regular
Joined
Apr 18, 2018
Messages
167
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

Board Regular
Joined
Apr 18, 2018
Messages
167
In any case... If you wish for the results to display as shown here:

First AidMedical AttentionModified Work
YesYesYesReport Required
YesYesNoReport Required
NoYesYesExempt
YesNoYesExempt
YesNoNoExempt
NoYesNoReport Required
NoNoYesReport Required
NoNoNoReport 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
Joined
Jun 21, 2003
Messages
256
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

Board Regular
Joined
Apr 18, 2018
Messages
167
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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,570
Members
410,851
Latest member
glowe2020
Top