Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
If you've ever ordered materials from a supplier, you've probably discovered that they offer a discount if you order larger quantities of the item; for example, if your order company t-shirts, you'll probably pay a lot less per shirt if you order 100 than if you just order 10. Another common scenario is that a salesperson can earn a higher commission if they exceed their sales goal for a month. When you need to determine which of several rules to use when you calculate a result, you can create a formula that includes an If function. An If function checks to see whether a value or a statement of any kind matches a certain criteria.
If it does, it uses one rule. If not, it uses another to find a result for the formula. I'll just jump in and do a quick example. Let's say that I have sales representatives and if they make their sales goal or if they exceed their sales goal, they get a 6% commission. If they exceed it by more than 10%, they get an 8% commission, and if they fall below their goal, they get 5%. Well, let's see how that works out. I'm going to create an If formula, so I'll type =If and then a left parenthesis.
The first thing that I need to enter into this formula is the logical test. This is what Excel is going to be checking the values against. And in this case I want to start out checking to see if the actual sales - and in this row that value is in cell C2 - to check to see if it is greater than the sales goal. That is B2. Then I'll type a comma. The next thing I need to enter is what Excel should do if the value is true. In other words, is the value in C2 greater than the value in B2? If it is, my salesperson will get a 6% commission, and I type that in as C2*.06.
In other words I'm not multiplying the value in C2 - their actual sales - by 6%, or .06. Then I type a comma, and now I need to tell Excel what to do if the formula is false. In other words, what should Excel do if the value in C2 is less than or equal to the value in B2? In that case we're giving them a 5% commission, so the value is C2*.05. That looks good. I'll type a right parenthesis, press Return, and we get the value of $15,000.
Now, that's correct as far as it goes, but remember earlier that I said that if a salesperson exceeded their goal by more than 10% then they would get an 8% commission. That happened in this case; 10% of 210,000 is 21,000. So that means that if the salesperson sold more than 231,000 then they should get an 8% commission. They did, but I only gave them 6%. That means I need to fix the formula. You probably noticed though, that when I was creating the formula, I only had room to enter three things: the logical test; what to do if the test was met, if the value was true; and what to do if the value was false.
There is a way to work with If formulas, where you can nest them, where you can create a second If function within another, and that way you can create multiple tests. So I'll show you how to do that now. Rather than try to edit the formula as it is, I'll just create a new one. So I'll type =If and a left parenthesis. Now, I'm going to create my logical test. So, remember, I'm checking to see if the salesperson exceeded their goal by 10% or more, if they met their goal, or if they fell short of their goal. The problem is that if someone exceeds their goal by more than 10%, then if I create the rule I had before, asking if the value in C2 is greater than the value in B2, then it will always apply a 6% commission.
It doesn't check to see if the salesperson exceeded their goal by more than 10%. That means I need to put my most restrictive condition first. So what I'm going to check for is whether the value in C2 was greater than or equal to the value in B2 multiplied by 110%, which is 1.1. Now, I can say if the value is true then the formula should return C2 - their actual sales - times .08.
Now I type the comma, and I tell Excel what to do if the sales are not greater than 110% of the target. To do that, I'll create a second If function inside the first, type a left parenthesis, and now I test to see if the value is greater than the value in B2, in other words if the salesperson exceeded their goal. So for that I typed the condition I had before: If C2 is greater than B2. If that's true, then I given them a 6% commission which is C2*.06.
If it's not, then I give them a 5% commission. I don't need to create another If statement because I can use the value If false argument or part of the formula to find the value that I want, and that is C2 *.05. Now I type a right parenthesis to close the second If function, but you'll notice that this right parenthesis is green. The first left parenthesis for the formula is black, so I have a black left parenthesis here.
I have green left parenthesis here, a green right parenthesis, and that's one way that Excel helps you balance the parentheses in your formulas. So I see green, and I know that the first one will always be black, so when I type a right parenthesis I have a black parenthesis, a black parenthesis. The green matches the green. I can press Return and get my value, and 250,000 times 8% is indeed 20,000. Now I can copy the formula down for my other sales representatives.
So I will grab the fill handle and drag it down, and because I used relative references in the formulas, Excel copies them down, and it works off of the values in the same row. The If function lets you create versatile formulas to calculate values. When you need to apply different rules depending upon sales or other results, creating a formula that uses an If function will let you calculate the answer you need.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.