In this video from the Excel 2010 Tutorial series, School of Technology Program Coordinator, Justin Denton, teaches us about writing formulas in Microsoft Excel 2010. This tutorial video is a great beginning step to your courses in technology at Rasmussen College.
To get into the formula builder, so let's say I'm on the maximum function right now, I can click on the Formulas tab and see all these really fancy formulas already calculated out for me. So I don't have to actually remember any of them. Before, it wasn't really much of an option in Excel in some of the older versions. But the newer versions have started to put in a little bit of a formula builder.
So you have your pretty default ones where you've got AutoSum which will have your Average, your Count Numbers, your Minimum and Maximum. You've got your Recently Used, HYPERLINK, COUNT, MAX, SUM, IF, PAYMENT, STANDARD DEVIATION. Financial ones, Logical, Text, Date and Time, Look Up References, Math and Trig, lots of different options. That's why there could be a week's worth of class just on how to use every single formula that is in Microsoft Excel.
The main thing is not to remember all of them. You'll remember a few that you've used a few times. But the goal is that if you know that you need to create a formula and there's got be something already built in Excel to do it for you, is just to know that I'll need to go to the Formulas tab, and I need to play around with the Formulas tab a little bit to figure things out.
So if I want to build this one using the Formulas tab and find out the highest cost, I'm going to use the Insert Function option. Because I'm not exactly sure, let's say, what category those may be in. I just know it's going to be something in particular. You can select a category here. I'm just going to use all, because I'm going to search everything. And this gives you the ability to search through every single function that's available. I can scroll through them, or I can click up here and type in a brief description.
Let's do maximum value. I'll hit go. And now it'll automatically try to filter based on what I've just said. I want to find the maximum value, so I'm searching for the function that will provide that. Excel is smart enough to try to figure that out for you. It gave me a list of options that are filtered to my choice. And you'll see there in our category of recommended. Well, this one I can look at, I can now see that DMAX will return the largest number in a field of records in a database.
VALUE will convert text strings to a number. MAXA returns the largest value in a set of values and it does not ignore logical values in text. And MAX returns the largest value in a set of values and ignores logical values in text. Well, this one I just want to use MAX for finding the maximum. So I'll select that formula and hit OK. And you'll see that it already prefills in a default range just because Max Value is right below the column that I'm working with.
But in this turn, I'm not looking for the maximum quantity on hand, I'm looking for the highest cost item. So I'll need to adjust this range a little bit. And in here you have the ability to type over that range. And I can go in here and just say, well, I need to be in C4 and I want to run from C4 all the way down to C9. So I can go over to B14 and type in C9 which will use and give me the correct range of cells.
You can see what on the fly calculation is for this is right below it based on the range I inputted in $20.95 is my highest value in that range. If I look back over at the range that I inputted in under cost $20.95 is my highest cost value. Well, if you happen to have a lot larger range and you don't want to type it in, you can obviously select the range too. So if I delete this all out, as long as I've got my cursor highlighted in one of these given numbers, I can do that highlighting and just highlight the range itself as well.
It does everything no different than just typing it in. Just if you have a larger range or you have to do multiple ranges, then you can build that all in in that manner. Same value $20.95, so it yielded the same value for this, it's just a different way of selecting the cells and inputting the data. After you're done, you can click OK. And you'll see that based on the formula that we created. Hit return. That $20.95 is the highest cost item that I have in stock. Make sense so far? Cool.
So the next one would be what a total order cost would be. Well, I can't calculate my total order cost because I don't have a total cost yet. And to find out what I have for my total cost, I need to be able to calculate my quantity on hand times cost and create a total cost per item. Or let's say, total cost of inventory. And this will give me a total cost for all my inventory of spoons, plates, forks, bowls, tablecloths, and menus.
To do that, we've already done the equal sum formula or even just an equals formula. So typically, how would you calculate your formula for this particular problem?
OK. So I'll do the equal sum.
Parenthesis. B4 times C4.
B4 times C4 end my formula and hit Enter will give me a total cost of $11.40. Has anybody used the Fill Handle before? So you've just calculated that formula. When you go down to the next cell what would you do to calculate that again? Would you typically type in that formula one more time?
I usually click on the cell above it and just drag it down.
OK, very cool. So click on the cell about it, drag down, and go ahead and populate that formula as well as increment those values down to the previous cells or the below cells. So now we've got $1140.00, $83.25, and $480.00, and so forth. So we've built out our entire formulas.
Now, we want to calculate what, instead of our total order cost, our total inventory value. So it will give us an idea of what the total value is of what inventory we currently have on hand. So to do that one, we're back with equal sum again. And we'll just select the range like we have been before. Once we've selected the range, we end the bracket and hit Enter. And now, we know that our total inventory value is roughly $1442.25.