4

I have these two IF functions that work correctly on their own. I need to combine the two into one cell but can't seem to get them to work. Any help would be appreciated. And thanks in advance.

Screen capture 1
enter image description here

=IF(AND((D5="Intel"),(Z5="Not Attempted")),"Not Attempted")

Screen capture 2
enter image description here

=IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", "Not Attempted")

I've tried the following combos without any luck.

This one results in screen capture 3
enter image description here

=IF(AND((D5="Intel"),(Z5="Not Attempted")),"Not Attempted")=IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", "Not Attempted")

This combo results in a #VALUE! Error in the cell:

=IF(AND((D5="Intel"),(Z5="Not Attempted")),"Not Attempted"),IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", "Not Attempted")

And this screen capture is me manually updating the cells to show what I need them to show, screen capture 4

enter image description here

Improve this question
2

1 Answer 1

Reset to default
1

Those are really long IF if you ask me!

But if I understand what you're trying to do...

It will be either this:

=IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", IF(AND((D5="Intel"),(Z5="Not Attempted")),"Not Attempted"))

Or this:

=IF(AND((D5="Intel"),(Z5="Not Attempted")),"Not Attempted", IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", "Not Attempted"))

It depends on what you want to check for first.

EDIT: After reconsidering the formula, maybe this would be better:

=IF(AND(OR(D5="Pilot", D5="Sensor", D5="Admin"),(Z5="Not Attempted"),OR(C5="E-1",C5="E-2",C5="E-3",C5="E-7",C5="E-8",C5="E-9",C5="O-4",C5="O-5",C5="O-6")),"Not Required", IF(Z5="Not Attempted","Not Attempted"))
Improve this answer
6
  • Wow, thanks for the quick response! Haha yes they are really long but it's what I have to work with...The first one you posted is VERY close and the second one has a few correct. But on the first one if you see on my screen shot I highlighted in yellow should show "Not Attempted" as they are an O-1 Pilot who is not exempt in the rule in the formula and have not completed it in column Z. i16.photobucket.com/albums/b43/bakdawg/Capture7_zpsba4c9714.png
    – Bryce
    Sep 16, 2013 at 12:09
  • @Bryce Oh that makes much more sense now! I admit it didn't make much sense initially haha ^^; Okay, logically, every person who is not exempt and has "Not attempted" should get "Not attempted", right? Currently, the formula only checks for Intel for this particular category. The formula can be made shorter by checking all personnel, not just Intel by removing the AND in the second formula. I added that formula in my answer. See if this works now! ^^
    – Jerry
    Sep 16, 2013 at 12:22
  • 1
    To help clarifiy what I'm trying to accomplish. If their position is Intel they have to have a date in column Z which should show False in column U. If column Z shows not completed then column U needs to show Not Completed
    – Bryce
    Sep 16, 2013 at 12:25
  • The other positions (pilot, sensor, admin) need to show a date in column Z which again should show false in column U. There are several ranks that are exempt (all those shown in the formula) and they should show not required if they have a Not Completed in their respective column Z. And if they aren't exempt based off of rank and have a Not Completed in column Z then column U should also show Not Completed
    – Bryce
    Sep 16, 2013 at 12:26
  • Jerry - I think that works!!! I will try to check it against my entire document but so far it looks good. Thank you so much for the help! -Bryce
    – Bryce
    Sep 16, 2013 at 12:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .