Forum

Notifications
Clear all

Trouble building multiple if formula for tracking shipments

2 Posts
2 Users
0 Reactions
151 Views
(@davel85)
Posts: 1
New Member
Topic starter
 

Hi All,

Im struggling to get my formula correct based on multiple conditions.

Apologies if I dont explain it well but its based on the below criteria

Its basic tracking for shipments but trying to flag "Ageing Shipments" based on multiple criteria

 

What I am trying to do is put some logic in F1 based on multiple criteria to determine if a shipment is ageing

If A1 = Delivered then F1 = Delivered 

If A1 does not equal Delivered and B1 = Air and C1 = China and E1 is greater than 10 then F1 should equal "Ageing Shipment" or If A1 does not equal Delivered and B1 = Air and C1 does not equal China and E1 is greater than 5 then F1 should equal "Ageing Shipment" or If A1 does not equal Delivered and B1 = Sea and E1 is greater than 50 then F1 should equal "Ageing Shipment" or If A1 does not equal Delivered and B1 = Road and E1 is greater than 4 then F1 should equal "Ageing Shipment"

 

Appreciate if anybody could help me get it right as I have tried multiple variations but keep coming up short or with the formula not working as expected

 

Regards

David

 
Posted : 04/10/2022 5:54 am
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

Hi David,

See attached workbook for the formula.  Writing something like this is a bit of a nightmare because Excel's formula editor is pretty rubbish and makes it sooo hard to follow a formula.

I you write it like this it makes it a bit clearer

=

IF(A2="Delivered", "Delivered",

IF(AND(B2="Air",C2="China",E2>10),"Ageing Shipment",

IF(AND(B2="Air",C2<>"China",E2>5),"Ageing Shipment",

IF(AND(B2="Sea",E2>50),"Ageing Shipment",

IF(AND(B2="Road",E2>4),"Ageing Shipment",

"Undefined"
)
)
)
)
)

 

This covers all of the logic you specified, but there are some situations that are not covered.  For example, the 2nd shipment from the Netherlands does not match any of the criteria in the formula.  So the result for this is undefined.  There are 5 rows with undefined results.  How do you want those handled?  Are they to be considered as still running on time?

Regards

Phil

 
Posted : 04/10/2022 7:58 pm
Share: