Viewers: in countries Watching now:
In Excel 2010 Essential Training, Bob Flisser demonstrates the core features and tools in Excel 2010. The course introduces key Excel skills, shows how to utilize these skills with in-depth tutorials on Excel functions and spreadsheet formatting. It also covers prepping documents for printing, working with large worksheets and workbooks, collaborating with others, using Excel as a database, analyzing data, charting, and automating and customizing Excel. Exercise files are included with the course.
Sometimes you want the value that's displayed in a cell to depend on a condition that's happening somewhere else. This is where you use a conditional statement and it's a statement that you can test. The way it works is that you output one result if that statement is true, and you output a different result if that statement is false. To do this, you use the IF function. The IF function gets three arguments. The first argument is a statement that you test. For example, you might say the content of Cell C6 > 500.
But when you make that statement or when you have that condition, well, there are two possibilities. Either it's true or it's false. So the IF function will output one value if that statement is true and it'll output another value if that statement is false. So, the general syntax of the IF function is say =IF and then you make the statement. Then you have the value of true and then you have the value of false. You notice that these three arguments are separated with commas. That's really all there is to it.
So let's take a look. What we have here in this sheet is people selling stuff. We have here our salespeople. We have here what they're selling and the commission rate. What we are going to do is we are going to say if the person sold at least $500, they are going to get a commission rate of 10%. If they sold less than $500, they will get a commission rate of 5%. Over here, you see we have a little table. Now once we stuff that commission rate in Column C, we go to Column D and the formula is already done for you.
We are simply going to multiply the sale amount times the rate and then we have the dollar amount of the commission. So, this will be a little more clear once we start doing this. So, let's go into Cell C6 then we'll say =if, open up the parenthesis. Now, we put in the first argument. The argument is the content of the B6>=500. That's it. Did the person sell at least 500 or not? That's true or false. So, we put in comma. That's the end of the first argument.
So, the second argument is what do we want the value of C6 to be if that condition is true? Well, if that condition is true, it means they sold at least $500. They get the higher commission rate. So, we click that 10%. Now, we are going to Auto Fill this down. So we want to make sure this is an absolute reference. So press the F4 key. That inserts the dollar signs and makes it an absolute reference. So that's what we output if true. Put in a comma. Now, we put in the third, last argument. If that condition is false, it means they did not sell at least $500.
They sold under $500. So they get the lower commission rate of 5%. So just click over here in cell J3 that's a lower commission rate. Again, press the F4 key on the keyboard to make that an absolute reference. If you are familiar with absolute references, you might want to go back and watch the movie when we talk about it. That's it. End the parenthesis, hold Ctrl+ Enter, and there we can see its 10%. Now, put your mouse pointer on that little dot in the lower right corner. When the mouse pointer becomes that crosshair, double-click and we fill down to the bottom and look at that.
Then you can eyeball it. So here is someone who sold about $600 and they got 10%. Here is someone who sold under $500 they get 5%. Now, we can see yes indeed, 10% of $509 was $50.99. So, that works. Now, that's great, but wait, there is more. Because a conditional statement, you can use the IF function to output text as well as a number. So, let's do that here. Let's go into cell E6 and type =if and open up the parenthesis and the condition is going to be same.
So, we are going to say is that amount there, the sale amount, is that great than or equal to 500 comma. Now, if it's true, we want to output a comment. So, open up your double quotation marks and we'll type in "Great job!" That's the end of the comment. So close the quotation mark. That's now the end of that argument. Type in a comma. Now, remember this is not English, this is Excel, so the comma has to go outside the quotation mark not inside the quotation mark.
So, if that condition is false, put in another double quotation mark and we will "Better luck next time." Close the quote, close the parenthesis. Press Ctrl+Enter. Again, put your mouse pointer on that Auto Fill handle. When the mouse pointer becomes a crosshair, double-click and now we have the appropriate comment for each one. The IF function conditional statements are great. So, you can always have the value of a cell depend on something else that's happening in the worksheet or even in another worksheet.
There are currently no FAQs about Excel 2010 Essential Training.
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.