Excel and you: If this, then that

So, you’re working on a spreadsheet, say with sales figures for multiple salespeople.  Now, your salespeople get commissions, with a couple of tiers.  You want to know who gets which commission amount, but don’t want to have to manually figure them out.

Say the scale looks like this:

-If they have under $10,000, they get 10% commission.

-If they have more than $10,000, they get 15% commission.

Now, you could sort by sales amount, and manually calculate the commission, but what if you want to use the spreadsheet each quarter, and don’t want to have to change the formula for each person?

That is where if/then statements come into play.

So, we’ll start off with a simple spreadsheet, with staff, sales, and commission as our columns.

Staff and sales are going to have to be hard entered in,  but when we get to the commission, we can write a fun formula.

When I am using if/then statements, I like to think of how the formula would be written in a sentence.  In this case, it would be:

If the sales staff makes less than $10,000 in sales, then they get 10% commission, else 15%.

Notice I bolded IF, THEN, and ELSE.  These are the components of our formula.

IF is <$10,000.

THEN is 10%

ELSE if 15%

Now, we go to the commission column for Sales Person A, and type in our formula, which is as follows:

=if(B2<10000, B2*.1, B2*.15)

Now, let’s break this down. That is saying If cell B2 is less than 10000, then multiply B2 by .1, else multiply B2 by .15

Notice how that is essentially what our original sentence said, just with more math?  Let’s see what happens when I drag it down to all of column C:   

And viola! We have a nifty little formula that will tell us how much commission someone gets, based on a two-tiered system.

But wait! You may say.  I know that some companies have three or more tiered systems!  Can Excel help with that?

And I would reply: Of course it can, and NEXT WEEK, we’ll look at imbedded if/then statements, which are a fun time for all!