Monday, May 14, 2012

Excel and You: Embedding, it’s not what you think!


So, last week we discussed if/then statements using two possible outcomes.  We had statements where if it wasn’t one type of outcome, it was another.

But what happens if there are three or more possibilities? 

Let’s look back real quick at how basic if/then’s work, remembering the if, then, else rule.

Your basic if/then formula looks like this:  =if(A1>0, y,z).  To read that out loud, you would say if cell A1 is greater than 0, then y, else z.

Now, how do we manage the situation if there are 3 or more possible outcome conditions?

I’ll grab another example from the sales commissions world.

Say a salesperson got commission based on sales in the current quarter versus sales in the previous quarter.  Sales that decreased got a 5% commission, sales that increased 0-10% got a 10% commission, and sales that increased more than 10% got a 15% commission.

Lets remember last week how we wrote out a sentence to describe that situation using if, then, and else.

If a salesperson’s sales decrease, then they get a 5% commission, but if a salesperson’s sales increase from 0-10%, then they get a 10% increase, else they get a 15% increase.

Now, the key thing to notice in that sentence is the two if’s.  That is going to translate into the embedded if/then statements in our formula.

So, how could we go about figuring this out?

First, we would need two bits of information:  previous and current quarter sales, and it would look something like this:


Now, what would our formula look like?

=IF((C2-B2)/B2<0,C2*0.05,IF((0.1>C2-B2)/B2>0,C2*0.1,C2*0.15))

Now, I know what you are saying: “Holy crap that is gigantic.”

Yes, yes it is.  But don’t get frustrated just yet, let’s walk through this one.

We’ll start at the very beginning(a very good place to start).

=IF((C2-B2)/B2<0  This is our basic if statement. DOOOO notice the double parentheses after the IF.  The first is required to start the if/then statement, but the second is an order of operations requirement in calculating the percent change in sales.  So, what we have there is if Quarter 2 sales minus Quarter 1 sales divided by Quarter 1 sales is less than 0(ie, a decrease).  Essentially, we have the first part of our sentence, the first if. Do NOT forget the comma, and we can move on to our…

C2*0.05  This is our first then. We look back to our if statement, and find if sales decrease, our salesperson get a 5% commission, so we take Quarter 2 sales and multiply them by .05, or 5%.  We put in a comma and go to the…

IF(0.1>(C2-B2)/B2>0  Now we’re really getting somewhere.  Here is our second if statement.  As we know from our original sentence, if sales increase, there are two possible commission levels, so we can’t just use a simple else after our first then, so we go to another if. The very first thing to notice is the LACK of an = in front of the IF.  Don’t use it inside an existing function, you WILL blow the whole thing up.

NEXT, we go to our actual formula.  It may look complex, but just read it left to right, and it makes sense.  If .1 is greater than (C2-B2)/B2 is greater than 0, OR another way to say that is if (C2-B2)/B2 is greater than 0 and less than .1 .  You DO need to keep your function inbetween the greater than/less than signs, since it is the one we are worried about.  If we put our comma after this, we can go on to…

C2*.1  This is our second then, and harks back to the last if statement, so if the increase is between 0 and .1, our sales person gets a 10% commission.  One last comma, and we find…

C2*.15 We FINALLY get to an else, and the end of our formula.  Make sure to close all parentheses, (Excel helpfully color codes them when you are writing a formula, so it’s relatively simple to figure out which aren’t closed), and we have an embedded if/then statement!


Needless to say, embedded if/then’s have a TON of uses, and are fun for all!
 
Next week, conditional formatting!

No comments:

Post a Comment

Sociable