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