Hey thanks for reading. I need some help coming up with a formula.
Here is a sample set:
A B C D E
1|Task Result Date Critical? Visit #
2|Clean Warehouse No 7/15/2011 No 1
3|Clean Warehouse No 7/16/2011 No 2
4|Clean Warehouse No 7/17/2011 YES 3
So the user supplies the task in A4, let's say Clean Warehouse and then types the Visit # in E4 as 3 and date as +1 from the previous day, and with that user supplied information, I am trying to get Excel to see if that task is "Critical". I am defining critical as any task that is delinquent twice in a row.
For example, in my data set it would take the user provided visit # in E4 which is 3, and check for that task's(clean warehouse) last visit---Clean Warehouse's 2nd visit. If it sees for Clean Warehouse's 2nd visits tasks Result was a No(B3), (If yes, stop) then it checks Clean Warehouses 1st visit and checks to see if the Cleaning Warehouse tasks result was also a No. If both of those results come back No (i.e. no one cleaned the warehouse in two days), Excel puts Yes in D4 signaling that the task Clean Warehouse is "Critical".
Hard to explain, but it's basically checking the last two visits for the same task and if the result of that task for both previous visits are a NO, it will classify the current visit as critical.
Can anyone help create a function? or direct me how to structure a function? I've tried a few nested IF statements but come up short.
Thanks so much for your time.
Please reply with any steps even if you don't know how to accomplish this completely.
Here is a sample set:
A B C D E
1|Task Result Date Critical? Visit #
2|Clean Warehouse No 7/15/2011 No 1
3|Clean Warehouse No 7/16/2011 No 2
4|Clean Warehouse No 7/17/2011 YES 3
So the user supplies the task in A4, let's say Clean Warehouse and then types the Visit # in E4 as 3 and date as +1 from the previous day, and with that user supplied information, I am trying to get Excel to see if that task is "Critical". I am defining critical as any task that is delinquent twice in a row.
For example, in my data set it would take the user provided visit # in E4 which is 3, and check for that task's(clean warehouse) last visit---Clean Warehouse's 2nd visit. If it sees for Clean Warehouse's 2nd visits tasks Result was a No(B3), (If yes, stop) then it checks Clean Warehouses 1st visit and checks to see if the Cleaning Warehouse tasks result was also a No. If both of those results come back No (i.e. no one cleaned the warehouse in two days), Excel puts Yes in D4 signaling that the task Clean Warehouse is "Critical".
Hard to explain, but it's basically checking the last two visits for the same task and if the result of that task for both previous visits are a NO, it will classify the current visit as critical.
Can anyone help create a function? or direct me how to structure a function? I've tried a few nested IF statements but come up short.
Thanks so much for your time.
Please reply with any steps even if you don't know how to accomplish this completely.
