# 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 belowCreate 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 Data:Column A = Shipment#Row2 4230022001Row3 4230022002Row4 4230022003Row5 4230022004Column B = Service LevelRow2 EXPRow3 STDRow4 STDRow5 PREColumn C=FRD DateRow2 11/2/2009Row3 11/3/2009Row4 11/4/2009Row5 11/5/2009Column D=DLV DateRow2 11/5/2009Row3 11/7/2009Row4 11/12/2009Row5 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")))

See More: If And statement using Excel

#1
November 24, 2009 at 08:47:46
 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.HTHRegards

Report •
Related Solutions