Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
One of Excel's most powerful functions is the IF function. It's almost programming-like in nature. It gives us the ability to come up with alternate answers depending upon the content of certain cells or calculations that we're comparing it with. In column G, we want to establish a shipping cost that's going to be 2% of the total cost except in cases when the total cost reaches 1500. We see in cell C4 a description, "No shipping charge for orders over $1500" so to make this clear, I'm going to make column G wider and also use the Zoom slider bar so that we can see this a bit better.
Ultimately, column G doesn't have to be this wide. The IF function allows us to test a condition and then come up with two different answers in its basic form; "=if", we simply want to check that entry in F7 to see if it's greater than 1500. Greater than 1500, this does not mean if it's equal to 1500, it means greater than and if the person who's designing this says, well, I want 1500 to be the breakpoint, then we want to make sure if it's equal to 1500, that that doesn't get the charge also.
So you have to be clear on what it is you mean, so depending upon on what it is the designer means here, sometimes you'll use this construction, meaning greater than or equal to 1500, sometimes it's simply greater than 1500. Now as soon as we put in the, notice that the wording below this right now logical_test is in bold print, as soon as we put in the comma, the focus shifts to value_if_true, when this is true, we have an answer and it's going to be zero or blank. Let's put in zero here.
If the order's over 1500, there's no shipping charge. When that test fails, in other words when the cost is not over 1500, we're going to put into calculation 2% times the cost. Now, it might seem like a small matter here but 2% of some of these costs is likely to give us portions of pennies as our answer and what we need to do sometimes is round this right at the spot of creation. So the Round function, which is often used by itself, is here used inside of an IF function and it's not always used in the IF function, but here it's going to make some sense.
We want to take this calculation and round it to the nearest two decimal places, comma two. If we were dealing with larger numbers and wanted to round this to the nearest whole dollar, we'd use comma, zero. So here we're using the Round function as one of the answers in our IF function and so that needs to be embedded in parentheses as does the IF function itself, one more, out of parenthesis there. We should be seeing in our example here, 2% of that $1449, almost $30.
And there's our answer, $29. Check this out by dragging it across a few cells, making sure it works okay and we've got some sensible answers. Drag it into cells where we go over 1500. We should expect to see an answer of zero there or a blank as we see it here. Depending upon the number format this might display as a blank or a hyphen, dash as we see it here or possibly a zero but the answer, as we see it, is correct. The formulas in column H that I didn't refer to you earlier are simply taking the total cost and then adding the shipping cost on to it, to get the grand total.
Eventually, we'll just make column G wide enough, Double-Click. So we've seen how to use the IF function here in a very simple straightforward way. If a condition is true, there's one answer, if it's false, there's another answer.
There are currently no FAQs about Excel 2013 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.