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