excel conditional formula problem

[phospher]

Hi,

I am comparing order date column to the receive date column to calc number of days elapsed. But in testing this formula I get a FALSE instead of the number of days, for this particular line 17. Humph.

column I column J Column K
Order date Shipped Date Received Date
2/10/04 2/11/04 2/13/04

the formula in column L is :=IF((+J17-I17)>90,"90",IF((+J17-I17)>8,"Unsatisfactory", IF((+J17-I17)>5,"Adequate",IF((+J17-I17)>1,"Satisfactory",IF((+J17-I17)=0,"A+",IF((+J17-I17)<0,"err"))))))

My purchasing managers shipping criteria is form order date to ship date:

Ship same day = A+
1-4 days Satisfactory
5-7 days Adequate
8-10 days Unsatisfactory

I would appreciate any help.

Thx Phos

 

[Bowers74]

You don't have an option in your formula if it equals 1 (just greater than 1). Change that to Greater than or Equal to:

=IF((+J17-I17)>90,"90",IF((+J17-I17)>8,"Unsatisfactory",IF((+J17-I17)>5,"Adequate",IF((+J17-I17)>=1,"Satisfactory",IF((+J17-I17)=0,"A+",IF((+J17-I17)<0,"err"))))))

That should do it!

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[AnotherHiggins]

Your formula is very close, but you haven't accounted for the event that the shipping takes 1 day! notice your conditions:

=IF((+J17-I17)>90,"90",IF((+J17-I17)>8,"Unsatisfactory", IF((+J17-I17)>5,"Adequate",IF((+J17-I17)>1,"Satisfactory",IF((+J17-I17)=0,"A+",IF((+J17-I17)<0,"err"))))))

You never state "=1", so it is clumped in with ">0". change ">1" to ">=1".

John