If And statement using Excel

Microsoft Office 2003 basic edition
November 24, 2009 at 08:33:34
Specs: 2003
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 day

Column A = Shipment#
Row2 4230022001
Row3 4230022002
Row4 4230022003
Row5 4230022004

Column B = Service Level
Row2 EXP
Row3 STD
Row4 STD
Row5 PRE

Column C=FRD Date
Row2 11/2/2009
Row3 11/3/2009
Row4 11/4/2009
Row5 11/5/2009

Column D=DLV Date
Row2 11/5/2009
Row3 11/7/2009
Row4 11/12/2009
Row5 11/6/2009

Column 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")))

See More: If And statement using Excel

Report •

November 24, 2009 at 08:47:46

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.



Report •
Related Solutions

Ask Question