Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Office > How to Use Logical Functions in Microsoft Excel 2013

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.
MS Excel 2013 training online
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.
Outlook 2013 training course

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.

Get our complete Microsoft Excel 2013 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

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.

Excel 2013 training courses by Simon Sez IT

 

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!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

MASTER MICROSOFT EXCEL AT SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Use Microsoft PowerPoint Animations and Transitions for Creative Presentations

How to Embed a Video in PowerPoint? 4 Easy Steps

QuickBooks 2020 – Section 4 Transcript

How to Remove Hyperlinks in Excel? 3 Easy Methods

Understanding Access 2013 Relationships between Tables – Part 2

How to Create a Bill in QuickBooks 2018

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)