How to Use Logical Functions in Microsoft Excel 2013
In Excel 2013, some brand new logical features are enabling Excel to accurately predict and modify calculations.
By employing variations in the VLOOKUP function, Excel is able to perform such two-step logical functions as determining when to apply a discount to a customer’s invoice and when not to.
The new logical features are handy when creating a list of customers who get discounts and a list of products and services. Excel will handily apply information from one worksheet to another, thereby eliminating the need to do every adjustment by hand.
Watch the free video here, transcripts for the entire video follow:
Learn how to master Microsoft Excel 2013. Get 19 hours of Microsoft Excel 2013 training – click here.
Video transcripts:
Welcome back to our course on Excel 2013. We’ve been looking at a case study, the production of an invoice for a plumbing supplies company as a way of looking at the functions that are available in Excel 2013 in a bit more detail. In this section, we’re going to look at what are called Logical Functions. This is function which will cause us to do one thing in one set of circumstances and different things in different circumstances.
Now in order to look at Logical Functions or an example of the Logical Function, we’re going to look at the table that we placed in the invoice. This was earlier on in the course and we’ve currently got half a dozen items in that table, which are part of this order. Now the items that we’ve got there we’re actually going to delete. So I want to clear all of these cells. I’m just going to select them and the easiest way to clear them is just to click on the Delete key. Now we are instead going to populate the order with items from our latest catalogue. Now the latest catalogue is the second worksheet in this workbook. So if you click on Catalogue, you can see we have product codes, descriptions for each of the products, a quantity or size for each, a list price, and then certain clients can get a discount and the percentage discount is marked here in Column E, and then for customers that get the discount the net price to them is in Column F. Now the feature that we’re going to put into this part of the invoice is that some customers will pay the list price without the discount and some will pay the net price. Now if I click back on to the Customers Worksheet this column, E, in the Customer Worksheet says whether a customer gets the discount or not. So customers 2971 and 2973, Frequent Showers and West Beach Bathrooms get the discount. They have Yes. The other two don’t get the discount. There isn’t any particular reason why two of them do and two of them don’t other than to demonstrate what we’re doing in this section.
So let’s go back to the Catalogue and look again. We’ve got a product code, description, and we’ve got prices. Now let’s go back to the invoice and see how we’re going to put all this together. Now there’s one thing about this table that I’m not entirely happy about and that is that I really ought to have a unit cost there. I’ve got a cost figure. I’ve got an order quantity. I really would like to see a unit cost. So what I’m going to do is to put the selection here in the order quantity column in the table, go to the Insert on the Home tab, and select Insert Table Columns to the left. And on that column the field that I’m going to select, what I’m going to call it is Unit Cost. So when I come to calculate the cost for a particular item in the order it will be order quantity multiplied by unit cost.
Now I should point out that when it comes to adjusting the widths of these columns, they are interconnected with the widths of the columns in the sheet overall. So there’s a certain amount of adjustment needed here to balance up the widths of these columns. But that’s not really the primary purpose of what we’re doing in this section. So if you want to tidy that up, you should know how to do that now. I’ll leave that to you. I’m just going to put that extra column in.
So let’s just take another quick look at the Catalogue. The Catalogue, first column is the product code, second column is the description, third column is the quantity size that goes with the description. So let’s go back here to the invoice. Let’s look. Product code will go in there. That’s what we select. And then we want to AutoFill in what the description is. Now we know that in order to do that we need a Lookup Function, we need a VLOOKUP. So if we click on VLOOKUP, it comes up with that helpful little dialog. The Lookup Value for that row will be B21. The Table array to look at it in is on the Catalogue Sheet, so let’s go to the Catalogue Sheet, and it’s going to be from A to how far down does that go? It’s not a very long Catalogue actually. In reality I’d probably have thousands of rows here, but this only goes as far as that. So that’s the Catalogue. And which Column has the description in it? That was Column 2 in the Catalogue. Click on OK and we get that. Now note at the moment we get N/A’s in there and the N/A’s in there because we don’t currently have anything in the product number. And when Excel looks at the product number, there isn’t one there and therefore it can’t find anything in the table. Now I’ll come back to that point in just a moment but let’s put a real product number in the first row. So back to the Catalogue; let’s put that one in, MAP15001. M-A-P-1-5-0-0-1. Now if I press the Tab key or the Enter key or just click in the next cell watch what happens. We now find the description for that item.
Now before we go any farther let’s just look at this VLOOKUP again. Don’t forget we’ve got to be careful with the VLOOKUP that we put in absolute cell references in the right places. So for instance when it comes to looking in the table in the Catalogue Sheet, we need to make sure that these are absolute references. Now we don’t really have time to go through the next point in this course, but if we set that up as a table, we’d make life a little bit easier for ourselves. But for the moment, all I’ve done is put the dollars in there to make sure that as I use variations on this VLOOKUP at various places in this table, I always look at the Cells A2 to F50 on the Catalogue Sheet to look up the Catalogue. Now the other thing we have to be careful of is that the B21 that’s referenced here for the product code will not be B21 on every row in this table because in the next row, it would be B22, then B23, and B24. So the first part B will not vary, 21 will. Now what we do in that situation to stop Excel from changing this in a way that makes the Formula wrong is to put a dollar just in front of the B, not in front of the 21. So what that says is you’ll always look in Column B but the number part which is 21 on the first row will change in subsequent rows but the B won’t. So, if I say there Yes. I know have a Lookup Function that should work consistently elsewhere in this table.
Now let’s deal with these rows with the N/A’s in them. Generally speaking in this table, I’m not going to have loads of blank rows. I’m only going to have the rows that I need, and therefore the problem whereby I get a formula error when there is no product code, it will not occur. Now I could protect myself from that in a different way by putting a check in and saying well if the product codes blank don’t try to put anything in the description. But that would take a little bit more time to explain so all I’m going to do is just select those few cells there, on the Home tab, Delete, say Delete Table Rows, and it will delete the table rows that are not in use at the moment. Now having worked out how to fill in the description using that VLOOKUP and the VLOOKUP looks at Column 2 on the Catalogue Sheet, let’s copy that and let’s paste it into the unit type column. Now that’s also looking at Column 2 and we know that the units are actually in Column 3. So let’s change that to 3 and now that one gives us the correct units for MAP15001, Catalogue. MAP15001. It’s a 15 kilo, 10 liter pack.
Now in that way, you could go through and complete the rest of this. Now, I’m going to do it, first of all, based on the standard list price of each item. So let’s go and look at Catalogue, the list price is Column 4. So if I go back here, click in that formula, copy it, paste it into there, and then go to that formula and change it to 4, tick that. I find the unit price is 13.8. I can, of course, format that as a Currency if I want to. Supposing I now type in my order quantity of 5 say, the cost column, the last column, should contain what? It should contain quantity which is F21 times price which is E21, tick it, and it comes to $69.00. And if I select that and that, go into Format, Format Cells, Currency, two decimal places, dollar character, OK, there we are. And I can also, of course, format my total as Currency as well; Currency two decimal places. Total cost of my order $69.00.
So let’s now select within that first row of the order and then on the Home tab click on Insert table row below. So I put in a blank line now and I’m now going to basically type in another Catalogue product code. So let’s look at a Catalogue product code. What about MAP20031? MAP20031, Tab, and everything else gets filled in except the quantity, of course, which is my choice in the order. Let’s suppose I want four of those. Press Tab and there you are. You can see my invoice creation working really well.
So that just leaves us with the question of choosing whether the customer pays the list price or the discounted price. And in order to do this we need to build this up in a couple of steps. First of all, don’t forget the customer’s account number is in this Field here which is C17 as we saw before. And also on the Customers Table here whether they’re discounted or not is in Column 5 of that table. Now the way we do this is as follows. If you look at the unit cost cell there, it says equals VLOOKUP and it basically says look up the product code, B21 in this case, in the Catalogue, and there’s the Catalogue, and use Column 4. Now, back to the Catalogue. If I’d wanted to use the discounted price, I would’ve used Column 6. So in that formula instead of a 4, I would’ve put a 6. So, if the customer doesn’t get a discount, that column contains a 4 and if the customer does get a discount the column contains a 6.
Now the way we structure this in Excel 2013 is in the Formulas tab in the Logical Group, again there’s a few functions. The one we’re going to use is the If Function. Now before I use the If Function, I’m just going to copy this VLOOKUP statement here to the Clipboard with a Control-C. I’m actually then going to delete it for the moment and I’m going to choose a Logical If statement. Now the structure of the Logical If statement is that there is a test and then there’s something we do if the test is true and something we do if the test is false. The test we’re going to apply is does this customer get a discount? If the answer if True, then what we’re going to put in here is, let me just paste that now. If they get a discount what we’re going to put in here is that VLOOKUP statement. So we’re going to put in the one with Column 6 at the end. If the test is False, we’re going to put in the 4 statement. So what we need to know now is what is the test? Well, to perform the test what we have to do is to look up C17, that’s the account number on the Customers Sheet and remember the database on the Customers Sheet goes from A2 to G5, and we need to check in Column 5, that’s Column A number 5, whether it says Yes. So let me just type that in here. Test VLOOKUP. What are we looking up? We’re looking up dollar-C, dollar-17. That’s, of course, on the Invoice Sheet. Where are we looking it up? We’re looking it up on Customers. Dollar-A, dollar-2, colon, dollar-G, dollar-5. And which column are we looking in? We’re looking in Column 5. Close brackets and the test is, now to do the test we put an equals sign and then we say if what we see there is the word Yes. So if that Lookup equals Yes, bear in mind in some cases is Yes and in some cases it isn’t. If it’s Yes, use the Column 6 price from the Catalogue. If it’s No, use the Column 4 price from the Catalogue. Let’s click on OK and let’s see how that looks.
Now one thing to bear in mind is that at the moment, the account number 2974, the account number will determine the unit cost here. So it currently says 13.80. Let’s try a different account number. Let’s go to account number 2973, tick that, watch the unit cost on the first row of the table. And there you see this customer gets the lower unit cost, 11.04 instead of the 13.80 we had before. That means 2973 must the discount, 2974 doesn’t. Let’s just check on the Customers Sheet; 2973 Yes, 2973 gets discount, 2974 doesn’t. So that pretty much works. And as you can see, it’s a pretty horrible looking formula now because for one thing it’s a very long formula. But it looks more complicated than it is actually because with an If statement, you have If, then you have brackets; you have three things, what’s the condition, what to do if it’s True, what to do if it isn’t True. Doing it the way we did with the dialog just now I think is a very convenient way of doing it. You may get to the point that you can just type these things in after you’ve done them for awhile, but they do tend to get a bit complicated. But don’t forget that once you’ve got that in place, you’re going to be using it over and over again not only within this specific single order here but also you can use exactly the same principle on every invoice that’s generated using this particular workbook and worksheet. So although it takes a little bit of time to get it setup and tested just think how much time it would save a company in the long run.
So that just leaves me with a little exercise for you to finish off with. This is Example 15. What I’d like you to do is to just add a couple more rows to the order, just use a couple of the other existing items in the Catalogue, choose your own order quantities, make sure that you understand how the If statement is setup, make sure that the workings of the checking the discount are okay and so on. And then I’d like you to extend the Customer Table to include the phone number and make that cell there, D9, work with phone number. You can make up your own phone numbers, of course. My answer to that question will be Example 16. I’ll see you in the next section.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
