April 28, 2004

More on Logic Formulas
I covered quite a bit of territory yesterday and thought some revision might be in order.

Okay, let's use IF, AND and OR for some potential real life applications.

I've mentioned that I use Excel for logistics. To calculate shipping time, I could just use a calendar and count the days, but I could also use Excel to do it for me. Let's consider time required for shipments to arrive at two different customer's locations, one is domestic and the other is overseas.

Customer A (Domestic)
Courier = 2 days (Overnight Courier)
Truck = 3 days

Customer B (Overseas)
Air = 3 days
Sea = 10 days

I enter the following information in the appropriate cells.
Customer Name in cell A1
Shipment Mode in cell B1
Shipment Date in cell C1

Here's a possible formula
=IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3))

What does this mean?
If I ship to Customer A and I am shipping by courier, I add 2 days to the shipment date in C1.
If this is not the case, then I move on to the second IF formula which says, if I ship to Customer B and I am shipping by Sea then I add 10 days to the shipment date. The third alternative means that I am neither shipping to Customer A by courier or Customer B by sea, so I add 3 days for either shipment by Truck or Air.

Here's the catch. What happens if I enter Customer A into cell A1 and Sea into cell B1 or Customer B into cell A1 and Courier into cell B1? In this case, the above formula will give me an incorrect answer so here is a solution. (Shipping time for truck or air is the same, so it won?ft matter if you mix them up in this case)

To keep things simple, let's say that I had entered the above formula into cell D1 and the next formula into cell E1.

=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",D1)

This will have the effect of telling me to re-select the Customer Name or Shipment Mode if I make a mistake or telling me the correctly calculated shipping time calculated in cell D1.

Alternatively I could combine both formulas to be entered in cell D1 like this.
=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3)))

Either way will do the job. But what if there are more than 2 customers or other forms of transport? To accommodate these extra criteria, you could just keep making increasingly elaborate formulas but remember there is a limit as to how many times you can use IF, AND or OR functions.

You could try spreading the formula out to 2 or more cells like above in cells D1 and E1, each cell calculating a part of the overall formula.

Or you could use something like a LOOKUP function. This will simplify things a great deal. I'll be looking at LOOKUP the next time I post.

This page is powered by Blogger. Isn't yours?