Microsoft Office 2003 basic edition

I am trying to compute a single formula based on the below Create ONE formula in the below grey cells that will evaluate if the given shipments are ontime, given the following 3 parameters and data set:

1) Shipments sold at the STD service level should have a transit time (FRD to DLV) of <= 7 days

2) Shipments sold at the EXP service level should have a transit time (FRD to DLV) of <= 4 days

3) Shipments sold at the PRE service level should have a transit time (FRD to DLV) of <= 1 dayData:

Column A = Shipment#

Row2 4230022001

Row3 4230022002

Row4 4230022003

Row5 4230022004Column B = Service Level

Row2 EXP

Row3 STD

Row4 STD

Row5 PREColumn C=FRD Date

Row2 11/2/2009

Row3 11/3/2009

Row4 11/4/2009

Row5 11/5/2009Column D=DLV Date

Row2 11/5/2009

Row3 11/7/2009

Row4 11/12/2009

Row5 11/6/2009Column E=On Time?

The formula I have is as follows and continues to error out:

=IF(AND(B22="EXP",(E22-C22+1)<="7"),"Yes"," No"),IF(AND(B22="STD",(E22-C22+1)<="4"),"Yes"," No"),IF(AND(B22="PRE",(E22-C22+1)<="1"),"Yes"," No")))

Hi, I presume that this is some sort of assignment. As such I won't provide a solution, but I can point out several issues with the formula you posted:

1. You are testing numeric values against text <="7" .

A value in double quotes is text, not a number.

2. You appear to be calculating the difference between values in columns C and E e.g.,'E22-C22', but there are no dates in column E.

3. You have not created any nesting of the IF statements. The first part of your formula, IF(AND(B22="EXP",(E22-C22+1)<="7"),"Yes"," No") is complete on its own. It will either return "Yes" or "No", and therefore there is no route to the rest of your formula.You need to use IF statements to decide on status first, ending up with three options, and then test for delivery time.

In general terms test IF the delivery is STD, if it is, do the date test, if it isn't test IF it is EXP and so on.HTH

Regards

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History