IF statement help - layering "and's" and "ors"

[D4WNO]

Hi, I hope you can help as it's driving me mad as I'm sure it's really simple to someone else. The excel help isn't pointing out where exactly the issue is and at this point, won't even let me post it.

I have some metrics, and I want my statement to show me the below:

J2 = Last updated date
E2 = Ticket status

1) I want it to look at all tickets with status (E2) of open, pending or on hold to show me if their last updated date is within the last 3 working days (networkdays).
2) If it is open and the date is within the last 3 days, show me "within KPI", otherwise "missed KPI".
3) If the status (E2) is something else other than my options then I guess it'll show me N/A (Unless I can get it to state "closed" or something too?


=IF(AND((NETWORKDAYS(J2,TODAY()>3,OR(E2="Open",E2="Pending",E2="On Hold")),"Missed KPI","Within KPI")

 

[Fluff]

Maybe

=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI","Missed KPI")

 

[D4WNO]

Thank you, this does return a result, but pulls back "missed SLA" for anything that isn't set to open. It's also lost all of the OR statements.

 

[Fluff]

I hadn't seen that you had edited your post, maybe

=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI",IF(OR(E2={"Pending","On hold"}),"Missed KPI","Closed"))

 

[D4WNO]

Oh weird, I don't think I edited.

This almost works, it needs to do the same calc if its open, pending, on hold etc (and "closed" for anything with any other status) - at the moment all the pending and on holds show as missed SLA. The more I stare at it the less it makes sense lol.

 

[Fluff]

at the moment all the pending and on holds show as missed SLA.

That's because you said

If it is open and the date is within the last 3 days, show me "within KPI", otherwise "missed KPI".

Maybe

=IF(OR(E2={"Open","Pending","On hold"}),IF(NETWORKDAYS(J2,TODAY())<3,"Within KPI","Missed KPI"),"Closed")

 

[D4WNO]

Aha, thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.