Excel 2013 Training -- Logical Function - If Statement - Excel Training Tutorial

During this Excel 2013 tutorial, the trainer shows how to use logical function specifically the "if statement" function in Excel 2013.

Stay in touch:

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel -)

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 functions 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.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.

[alieum.kenneh]

This is even better than going to class, It's very easy to understand....Thanks

[eddiemegao]

...just when I needed it...better than my teacher :) ...great stuff!! thanks!

[makiebok]

You are so Helpful!!! Thanks Mr. Simon, , , :)

[prometheus]

This is a great tutorial video...please keep up the great work.

[cyberspook]

easy to listen to, very concise, Excel training at it's best, nice work Simon.

[samiisk-xjzv]

It would be really helpful if you'd put the file in the description so we could learn it practically.

[mahrozsheikh]

This is really a helpful content.Sir Do u provide online classes?

[tauqeer]

rather than using the absolute or relative reference for cells, it could have been better if you create named ranges and then use those in formulas...

[usamahsyed]

if single product code have descriptions more than one e.g (According to this video) product code 10690 has description Laminate Color Or 02 if same code has description Laminate Color Or 02A, would be the formula  ???

[statesman]

I could not get my follow-along if-statement to work. I'm using Excel 2010, but I don't think that makes a difference. I went over it thoroughly several times and would be very appreciative if someone is able to find the error. The following is my code:
If statement:
=IF(VLOOKUP($B$16, Customers!$A$2:$K$5, 5)="yes", VLOOKUP($A20, Table3, 6), VLOOKUP($A20, Table3, 4))

No discount:
VLOOKUP($A20, Table3, 4)

Discount:
VLOOKUP($A20, Table3, 6)

A few key notes:
Account number for the instructor's invoice table is his C17; mine is B16.
His Customer sheet goes A2 to G5, mine is A2 to K5 (I already added state, zip and phone number columns),
The check is in Column 5, which is the E column of the customers table (same as the instructor's).
My catalogue table goes from A2 to F17
Table3 refers to the Catalogue table. My customers table is Table2, but I decided to name it in the formula.

When I am entering different account numbers in B16 of the invoice page, the table beneath does not respond to the changes.

[Ckii]

Adding the VLookup makes this frustrating and annoying for people only trying to understand the if function