Easy Formula Stumping Me - Easy =IF() statement

[spcalan]

I have 1 column I want to indicate either "On track" or "At Risk"..

A1 is a ship date
So B2 is the status from above..

H is Release Date
I is Kit Date
J is Start Date
K is Finish Date

So the logic is this :

If Finish Date + 2days <Ship Date = "On Track"
if Start Date + 3 days <Ship Date = "On Track"
If Kit Date + 4 days <Ship Date = "On Track:
if Release Date + 5 days <Ship Date ' On Track"
These 4 stages will always have data ( or " " values ) because of Vlookups.. so no 'blank cells".

So basically...
if the finish date is blank, the start date is blank, the kit date is blank, but the release date +5 days is < ship date = we are still On Track..

Help.

 

[Jonmo1]

How can I paste full formula in post ?

See post #7

 

[spcalan]

More information that might be needed.
This is a linear process.

Must Release First
Then Kit
Then Start
Then Finish

So if finish date is late - then order is late
But if finish date is blank then check start date
if start date is late = order is late
But if start date is blank then check kit date
if kit date is late = order is late
But if kit date is blank then must check release date
if release date is late = late but if release date is blank.. then take today's date + 5 days and check against ship date

Too bad you can't just type in 'English' to get your point across in the formula bar - haha

 

[spcalan]

=IF(AND(K3+2 < A3,J3+3 < A3,I3+4 < A3,H3+5 < A3),"On track","RISK")

 

[Jonmo1]

=IF(AND(K3+2 < A3,J3+3 < A3,I3+4 < A3,H3+5 < A3),"On track","RISK")

Looks good.

 

[spcalan]

it doesn't work though.
This formula gives me the 'At risk" when 1 is late, but the end status would be fine if we rushed through production.
Technically, we could be late in releasing the order, and normally the 4 stages take 1 day each.. but we could do all 4 in 1 day if we expedite it through production.

So it really needs to check the last step ( finish date ) first... then move on..

More information that might be needed.
This is a linear process.

Must Release First
Then Kit
Then Start
Then Finish

So if finish date is late - then order is late
But if finish date is blank then check start date
if start date is late = order is late
But if start date is blank then check kit date
if kit date is late = order is late
But if kit date is blank then must check release date
if release date is late = late but if release date is blank.. then take today's date + 5 days and check against ship date

 

[Jonmo1]

Try

=IF(H3+5 > A3,"Risk",IF(I3+4 > A3,"Risk",IF(J3+3 > A3,"Risk",If(K3+2 > A3,"Risk","On Track"))))

 

[spcalan]

Very nice work !
I like how you started with the first stage to determine pass/fail, then moved down the linear process.

Thanks.!

 

[Jonmo1]

Great, so that is what you're after then?

If so, I honestly don't really like that setup of nested IF's..
Try this instead

=IF(SUMPRODUCT(--(H3:K3+{5,4,3,2}>A3)),"Risk","On Track")

 

[spcalan]

Great, so that is what you're after then?

If so, I honestly don't really like that setup of nested IF's..
Try this instead

=IF(SUMPRODUCT(--(H3:K3+{5,4,3,2}>A3)),"Risk","On Track")

This works just as well \ - difficult to read, but very functional !
I am not to familar with the sumproduct formula.

 

[Jonmo1]

Great.

It basically adds 5 to H3, 4 to I3 3 to J3 and 2 to K3
Then counts how many of those results are > A3
If that count is 0, then the IF is false
If that count is >0, then the IF is true