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 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in 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 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • 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
        • Python Object-Oriented Programming
        • 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
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • 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
  • Get Started
Home > Microsoft Excel > Complex Examples of Formulas in Microsoft Excel 2016

Complex Examples of Formulas in Microsoft Excel 2016

During this Microsoft Excel 2016 training tutorial video, and we’re going to concentrate on a more complex example of the use of formulas.


Facebook Linkedin Twitter

Like what you see? Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Video Transcript

In this section we’re going to continue to look at formulas and functions and we’re going to concentrate on a more complex example of the use of formulas.

I have here a simple list of rooms in a house that’s going to be refurbished. And I have the length and width of each of the rooms in the house. So I have a dining room with dimensions 5 meters by 5.6 meters and so on. Now I’m doing these calculations in meters and square meters but just think of them in any units that you’re familiar with.
Now the first thing I want to do is to work out the floor area of the whole house because I’m going to work out a budget cost for carpeting this house.

So if I wanted, for example, to work out the floor area of the dining room then to get the area of the floor I would multiply the length by the width. So once again let me first of all zoom in and let me click into E5 and type in a formula. So it will be =C5*D5. So there we. There’s my formula for the floor area. So let me tick that and I can see that the floor area of that room is 28 square meters.

Now what I could do is to click into E6 and type in the corresponding formula for the area of the living room floor multiplying C6 by D6. But in fact there is another way of doing this, a much easier way of doing this and that is to use Fill because where cells contain formulas Fill, in this case Fill Down, will actually fill down the formulas and adjust the formula in each cell according to its position. Let me show you what I mean. Let me select from E5 down to E13. Now I’m going to use the Keyboard Shortcut Control-D to do a Fill Down and I have all of my room areas. If I click in the cell E6 look at the formula, C6*D6. If I click in E7, etcetera. So the formula in each case is adapted to suit the position of in this case the floor area cell, the cell in column E, with effectively its row number. So that means I don’t have to type in all of the individual formulas.

Now that’s absolutely fine. It’s a little bit annoying actually. I tend to be a little fussy about these things. I’m going to give all of those floor areas two decimal places so that it looks a lot neater. So having selected all of the cells from E5 to E13, note they contain formulas but I can still format the cells to determine how the values are displayed. So if I go into Format Cells say that these are Numbers and I want two decimal places, click on OK, then I just think that for those floor areas that looks quite a bit neater.

Now what I’m going to do is to work out a budget cost for the flooring. Now the figure that I’m working on is a cost per square meter for flooring of $21.00 per square meter. No idea whether that’s a realistic one, depending on what’s required in this house but it’ll do for our purposes here.

So let me put a heading in here and now what I need to do is to work out the cost of the flooring, for example, in the dining room. Well it’s going to be the cost which is $21, that’s the cost per square meter, times the area of the dining room floor which is E5. Let me enter that and I have a cost of what’ll actually be $588.00. So let me do a Fill Down of this. Don’t forget for Fill Down I can go to the Editing Group and use Fill Down there. And I’m going to format these values as Currency Values. Two decimal places and that’s it.

Want More? Get Started With a Free Excel 2016 Course! Click Here

So that’s the cost, the budget cost of the flooring in each of the rooms.

Now if I click in one of the other cells, say F9 for example, you can see that it’s very successfully put 21*E9 for the cost in that room. One of the issues here is that let’s suppose that I then found that my budget for flooring had to be decreased or increased. So instead of $21 it was going to become $19.50. I’d have to go through and change all of those formulas. Now I could change the first one and do a Fill Down but generally speaking it’s a better idea to treat something like the budget cost per square meter of the flooring as what would often be called a perimeter, an input to the calculations, a separate figure that we can maintain separately, change separately and we could perhaps experiment with different budget costs to look at a different overall cost for the flooring in the house.

So one of the approaches that we could take here would be to put our cost figure in a separate cell altogether. So for instance I could choose cell I5 and type 21 in there and use that as my cost per square meter. Now an advantage of that is that if my cost per square meter changes all I need to do is to change the value in I5.

So first of all let me format that value. Now let me change the formula in F5. So now instead of 21*E5 I’m going to put I5*E5 and some of you may already spot the problem here but let’s carry on. And I have the same total of course, $588.00. Now let me do my Fill Down. What do you think is going to happen? Well in fact what’s going to happen is I’m going to get a nasty surprise because as I do the Fill Down all of the other costs become $00.00. And you can see the reason by looking in, for example, the Formula in F6 because F6 says I6*E6. Excel doesn’t realize that although I want the row numbers in my flooring dimensions to go 5, 6, 7, 8, 9, etcetera my budget cost per square meter in I5, I don’t want that to change. I want them all to refer to I5. Whatever the row number in the calculation column I want always the formula to say I5. So I want to fix that 5.

Now the way that I do that in Excel, go back to that formula again, is that in front of the 5 in I5 I put a dollar symbol. And that makes that reference an Absolute Reference, so the 5 won’t vary. Now in fact I could put a dollar in front of the I as well. It’s not actually necessary in this case but it would just emphasis the fact that in the Formula I5 is a fixed cell. It is always the cell that’s in row 5 and column I. So it’s $I$5. Now let me do my Fill Down and now I’m back to my figures being correct. And if I say looked in cell F11 the formula is $I$5xE11. So that’s how to use an Absolute Cell Reference in Excel 2016.

Now there are a couple of other things that I’d like to demonstrate here. Let’s suppose that I now want to put a note next to $21 to explain what that figure is and I’m going to need a little bit of space. I could make column H wider. What I’m going to do instead is I’m going to Insert an extra column or two here and I’m going to say Merge those three cells. And I’m going to say, let’s make that a little bit wider. Now what you can see here is that the $21 is now in cell K5 but it hasn’t actually affected my budget flooring cost. And if you look in the formula here you’ll see that Excel is intelligent enough to realize that although I’m dealing with the Absolute Reference in the various formulas here for the cell that contains that cost per square meter. So what was I5 is now K5. It’s intelligent enough to realize that I’ve actually moved that cell but it’s still that one that I need to refer to. So you won’t upset it by moving that figure, for instance by inserting columns as we’ve done just here.

Now let me just Right Align that in the cell and I’m going to apply a bit of style to that cell. Let’s go with that one. That’s fine.

And just one other thing I’d like to demonstrate to you. I’m going to click in I8 and I’m going to make a mistake. I’m going to try and put a formula in put I’m going to put it in wrongly. I’m going to for some reason want to work out what twice the floor area of the bathroom is. I don’t know why. But instead of putting for the floor area of the bathroom E8 I’m going to put B8 by mistake. Now B8 of course is the name of the room, the bathroom. One thing to notice here in the Formulas when you refer to a cell is that Excel maintains a color coding system. So you can probably see quite clearly there that B8 in that formula is blue. Well that’s the same blue that’s used on B8 now once I’ve entered its Reference in the formula and you can use that color coding to relate the elements in a formula to the corresponding cells on the worksheet. So in this case the blue that’s used for B8 in the formula is the same as the blue that’s used around the word Bathroom in the cell B8. And if I referenced a number of cells they’d each be given different colors accordingly. That can be a very useful way of identifying which object on a worksheet is being referred to. But in this case I don’t really want to refer to B8. It’s actually a mistake. I just want to show you what happens because the chances are you’ll see this quite a bit. Let me tick that. You get that to Excel users a very well-known piece of bad news, #VALUE!. It basically means you’ve put something illegal. You’re referencing something or trying to do something which is illegal. You can’t multiply the word Bathroom by two in Excel. And that will normally tell you that there’s something wrong with a formula or you’re referencing something that isn’t there or that you can’t do that to or whatever. So in that case you look at the formula, you look and you say okay =2*B8. You look at B8 and you say oh it says Bathroom. That can’t be right. And then you’ll correct your error and you’ll be fine.

Want More? Get Started With a Free Excel 2016 Course! Click Here

So that’s an important thing to recognize as normally indicating that a formula is trying to do something that’s not possible.

Well that’s it on this introduction to Formulas and Functions. I’m going to give you an exercise next in the next section. I’ll see you then.

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!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • 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

START LEARNING Complex Examples of Formulas in Microsoft Excel 2016 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

How to Create an Asana Gantt Chart in 5 Simple Steps

How to Setup a Company File in QuickBooks 2018

How to Recover Unsaved Excel File? – 3 Ways

How to Enable Full Screen in Excel? 3 Simple Ways

Receiving Inventory in QuickBooks 2019

How to Use PowerPoint 2010 Templates

Course Categories

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

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

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