Right Wing Nation

Nov 29 2008

04: Conditional Functions

The functions we will cover:

IF
AND
OR
VLOOKUP
HLOOKUP

There are fewer functions this time because many people find these a bit stickier, so to speak. Also, doing my personal system (which I found everybody now uses back at my old department) is a pain in the ass to do on the computer (I use a whiteboard).

Remember this?

Let’s talk about it some more. We’re selling NCAA Championship T-shirts, but since we’re a student group with little capital, we only ordered 1400 initially. We can order in lots of 50, if we need to order more, but we don’t want to get stuck with a lot of T-shirts. If the demand slacks off, we’ll sell the remainder at a discount price, rather than ordering another lot and having to sell of a lot of discount T-shirts.

We’ll keep this simple, so we’re only calculating after all of the demand has been filled, and not in real time.

Still with me? Good.

So we order the shirts, put up our site, and people place orders. When we calculate our revenue, we can’t just multiply the demand by the selling price unless our demand exceeds the number of T-shirts on hand. So we use IF-THEN-ELSE logic (by the way, while math instructors usually say, “Do your work here,” we say, “Put your logic here,” and you’re about to see why).

Here is where giving cells and ranges names come in handy. They make the functions easier to comprehend. Just remember if you use range names, list them on the worksheet!

We use IF-THEN-ELSE logic thousands of times every day, although we usually leave the ELSE part implied: “If it rains, I’ll take my umbrella,” is actually, “IF it rains, THEN take umbrella, ELSE don’t take umbrella.” Calculating the revenue of full-price T-shirts sold is something like this:

IF the demand exceeds the inventory (order), THEN multiply the selling price by the inventory, ELSE multiply the selling price by the demand.

In Excel with named ranges, the function looks like this:

=IF(Demand>Order,Selling_price*Order,Selling_price*Demand)

See? Take it apart:

=IF(condition_is_true,THEN_do_this,ELSE_do_this)

Similarly, we only have to sell at a discount if the inventory (order) exceeds the demand, so to calculate the discount T-shirt revenues, we use:

=IF(Demand>Order,0,Discount_price*(Order-Demand))

See? If the demand is greater than the order, we sell none at a discount, so our discount revenues are 0, ELSE we multiply the discount price by the number remaining after all orders are placed (order - demand).

Once again, it looks like this:

=IF(condition_is_true,THEN_do_this,ELSE_do_this)

Excel conditionals are binary, that is, we can only have one THEN and one ELSE for each IF. That’s it. To get more, we have to embed (or nest) IFs within IFs.

Say you want to assign letter grades. Here’s how:

=IF(score>=90,”A”,IF(score>=80,”B”,IF(score>=70,”C”,IF(score>=60,”D”,”F”))))

Each embedded IF is the ELSE of the previous IF. Count the number of possible results (here, 5). Subtract one. That is the number of IFs you will need.

Note that we do not have to specify between ranges. Those students with scores over 90 have been given As, so the second IF assigns no values to those students, and so forth. There are always a few students who have serious trouble with this.

Sometimes, you have mutiple (complex) conditions, and that’s where it starts to get sticky. Let’s say, for example, that all employees in division 3 who have accumlated more than 12 hours of on the job training have to take a new software seminar. We have a complex condition: Division 3 AND more than 12 hours of on the job training. We want to return a “YES” if the employee must attend, and a “NO” otherwise.

Again with named ranges, we would enter the following in the cell to return a “YES” or a “NO.”

=IF(AND(Divison=3,Training>12),”YES”,”NO”)

Note that the basic syntax is the same:

Let’s change it. Say employees who are in either Division 3 or 4 who have completed more than 12 hours of training must take the software training seminar.

=IF(AND(OR(Division=3,Division=4),Training>12),”YES”,”NO”)

Again, the basic syntax is the same.

Conditionals can easily become very long and complex. So here’s my no-fail system, which I found out on my last trip everybody is now using.

Division 1 sales reps have been working at the company for at least a year, Division 2 for six months to a year, and Division 3 are probationary. Only Division 1 sales reps have been working long enough to qualify for a bonus, and how much their bonus is depends on how much they’ve sold. Reps who have sold over $1000 in the last month get a 0.3% bonus. Reps who have sold over $2000 get a 0.5% bonus, and the remainder get no bonus.

Count the number of possible results and subtract one, then start the template.

IF
THEN
IF
THEN
ELSE

Fill in the template.

IF Division=1 AND Sales>$1000
THEN 0.3% bonus
IF Division=1 AND Sales>$2000
THEN 0.5% bonus
ELSE 0 bonus

Look in the first IF line. Do you see an AND or an OR? If so, circle the condition, cross out the AND or OR and replace it with a comma, then write the AND or OR at the left top of the circle. Place a comma just outside the right side of the circle. If there is more than one AND or OR, repeat until all have been crossed out.

Repeat for every IF line.

Type it in — the system produces all of the syntax for you (each circle is an opening and closing parenthesis pair). Possibly the hardest part of writing or debugging long complex IFs is missing closing parentheses. If you leave one off the end of a function, Excel will provide it. If It’s in the middle, you just have to figure out where it goes. My system provides all of the interior opening and closing parentheses, as well as all the commas and operators. Fail-proof. By the way, I’ve had students who sneered at it because it seems elementary. They always missed more points on the conditionals in projects and on exams than the students who used it. Just sayin.

Lookup functions aren’t really logical conditionals, but I lump them together because they can perform the same functionality. Excel limits the number of IFs you can embed, so if we wanted to assign letter grades including plus and minus grades, we have to use a lookup function.

There are two: VLOOKUP, which looks up data arranged in columns, and HLOOKUP, used with data arraged in rows. Because data are usually arranged in columns, we’ll use VLOOKUP. HLOOKUP works exactly the same way.

We have our grade table entered in Excel. Our grade table is named Grade_Table:

and we also have entered in Excel our student scores:

VLOOKUP will look up the student’s score (first argument) in the array (the second argument, our grade table), and return the column from the array we specify (the third argument, here 2, because the score is in the first column and the letter grade is in the second). VLOOKUP takes a fourth argument: Whether we want the array values treated as a range (TRUE) or an exact match (FALSE). Here, we want the values treated as a range, so we enter:

=VLOOKUP(B2,Grade_Table,2,TRUE)

And when we past the function down the column, we get the grades:

Note that the data we look up (the student’s score) and the first column of the table must be the same data, or Excel will find no match, range or exact. In other words, you can’t have the letter grade in the first column of the table, because you’re looking up the score, and returning the letter grade.

Often, however, we don’t want the values treated as a range, that is, we want an exact match. For example, say the employees in Dept 1 get a 10% bonus, Dept 2 a 20% bonus, and Dept 3 a 15% bonus (yes, we could do this with an IF, but we can also do it with a lookup).

We have our departments and bonuses named Dept_Bonus: