Log In
Register
Home
New Member
October 3, 2022
Offline
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
October 5, 2010
Offline
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
1 Guest(s)
