Easy Formula Stumping Me - Easy =IF() statement

S

spcalan

Well-known Member
Joined
Jun 4, 2008
Messages
1,247
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Jonmo1 said:
Try

=IF(AND(H1+5 < A1,COUNTA(I1:K1)=0),"On Track","At Risk")
Click to expand...

Thanks for the fast response.....

I have a ship date ( A2 ) of 1/18/13
I type the release date of 1/1/13 = ON track - correct
I type the kit day ( which is if kit day+4<ship date = on track... ) it says I am at risk...

I think I understand your formula, but i have to validate each date + X days, not count them...
 
Upvote 0
I was going by your final summary...
spcalan said:
<ship
<ship
<ship
<ship ?="" Date="" Track?="" On="">
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..
Click to expand...

But I missed the part of your formulas having "" results from a Vlookup..

So instead of counta..
=IF(AND(H1+5 < A1,I1="",J1="","K1=""),"On Track","At Risk")

In your post you wrote that the vlookups have " "
Is that literally a space character between the 2 quotes?
If so, I'd recommend changing those to "" instead of " "

But if you want to keep it that way, then change the "" in my formula to " "

</ship></ship></ship></ship>
 
Upvote 0
spcalan said:
Thanks for the fast response.....

I have a ship date ( A2 ) of 1/18/13
I type the release date of 1/1/13 = ON track - correct
I type the kit day ( which is if kit day+4<ship risk...

I think I understand your formula, but i have to validate each date + X days, not count them...
Click to expand...

Maybe this will work ?
=IF(AND(K3+2<A3,J3+3<A3,I3+4<A3,H3+5<A3),"On track","RISK")</ship>
 
Upvote 0
spcalan said:
Maybe this will work ?
<a3,j3+3<a3,i3+4<a3,h3+5<a3),"on ship="" track?,?RISK?)<="" track?,?RISK?)<a3,j3+3<a3,i3+4<a3,h3+5<a3),?on="">
=if(and(K2+2<A2,J2+3<A2,I2+4<A2,H2+5<A2,"ON TRACK","RISK")</a3,j3+3<a3,i3+4<a3,h3+5<a3),"on>
Click to expand...
 
Last edited:
Upvote 0
<a3,j3+3<a3,i3+4<a3,h3+5<a3),"on track?,?RISK?)=""> < =IF(AND(K3+2<A3,J3+3<A3,I3+4<A3,H3+5<A3),"On track","RISK") >
This one doesn't work either..
Once 1 is >Ship Date = entire order is late.. even though you might have released it late.. but completed all 5 stages same day.. it says at risk..</a3,j3+3<a3,i3+4<a3,h3+5<a3),"on>
 
Upvote 0
<a3,j3+3<a3,i3+4<a3,h3+5<a3),"on track?,?RISK?)
</a3,j3+3<a3,i3+4<a3,h3+5<a3),"on>
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
NETWORKDAY and WORKDAY formulas
Replies
2
Views
235
Dave Patton
D
A
  • Solved
Using IF STATEMENTS, FORMATS and VALIDATE to track tasks. HOW?
2
Replies
12
Views
825
abuchanan
A
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
458
SaraWitch
SaraWitch
C
  • Locked
  • Question
Earned Value Formula Issue
Replies
1
Views
214
Fluff
Fluff

Forum statistics

Threads
1,214,979
Messages
6,122,552
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top