nested if ifs

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nested IF / IFS Function

Nested IF / IFS Function

resolvedResolved · Medium Priority · Version 365

Simon has attended:
Excel Intermediate course
Excel Advanced course
Customer Service Excellence course

Nested IF / IFS Function

Hi - I'm trying to compile some KPI reporting but struggling to get the right combination of nested IF functions (and also tried IFS to no avail).

I need to show:
If an order is sent to the warehouse before midday on any given day, the pack slip must be returned on the same day to "PASS", otherwise "FAIL".
If an order is sent after midday on any given day, the pack slip must be returned by the following day to "PASS, otherwise "FAIL".

So far I've got the following:

Column C = sent date
Column D = before midday (Y/N)
Column E = return date

=IF(D2="Y",IF((NETWORKDAYS(E2,C2)-1)=0,"PASS","FAIL"),IF((NETWORKDAYS(E2,C2))=1,"PASS","FAIL"))

Above formula returns "PASS" if dates are equal regardless of whether D = "Y" or "N". What is missing?

Thanks in advance for your feedback,
Simon

RE: Nested IF / IFS Function

Hi Simon,

Thank you for the forum question.

Try:

=IF(AND(D2="Y",(NETWORKDAYS(C2,E2)-1)=0),"PASS",IF(AND((NETWORKDAYS(C2,E2)-1)=1,D2="N"),"PASS","FAIL"))

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector


 

Excel tip:

Seeing named ranges as part of the zoom

If you have large areas of named ranges this works better.

If you zoom down to 39% you will see your named range.

View all Excel hints and tips


Server loaded in 0.11 secs.