From the course: Excel 2013 Essential Training

Creating simple formulas: Totals and averages - Microsoft Excel Tutorial

From the course: Excel 2013 Essential Training

Start my 1-month free trial

Creating simple formulas: Totals and averages

“

In Excel, writing formulas is probably one of the more important things that you do. In fact, in some worksheets they're just loaded with formulas and also with functions as we'll see. In this particular worksheet called "Formulas". It's in the workbook 03-- Creating Formulas and Functions. We need to calculate some answers in row 4. The numbers here are pretty easy, we can do the math in our heads. This is going to be a 20, this is going to be a 30, and so on. We're going to be subtracting these numbers, but what is unusual, at least at first when you work with formulas, is we will not be typing in or using the exact entries 120, 420 or 100. We won't be typing those at all. We do this by location. What we're really about to say is "whatever is here minus whatever is there". By implication we're saying that B4 is equal to the difference of those two cells. So, we don't type the B4, it's implied, we type "equal" to begin a formula in Excel. We can type in the B2 minus B3. That's certainly a common way of entering formulas. There is this color coding that takes place as well. When we press Enter, one of the Arrow Keys or Tab, the formula is complete and we have our answer; but the formula gives us the added advantage of, if either of these two cells changes, then our formula reacts and we get a different answer. In a certain sense, this cell doesn't really contain 20, it contains a formula. When you click on cells--keep an eye on the Formula Bar--it reminds you what the cell contains. If you happen to double-click a cell-- sometimes you'll need to do this to change the formula-- it displays the formula in a color-coded way. Now, if the Overhead number was incorrect and we find out that the Expenses here were really 125, we'll make that change, but we don't have to rewrite our formula, it reacts immediately giving us the correct answer here. I'm going to undo that with Ctrl+Z. Let's talk about two other ways to enter a formula. Once again, beginning with the equal sign, but this time using Arrow Keys to get to the cells we need. I'm going to press the Up Arrow Key twice. Now, press the minus key on the keyboard and then the Up Arrow Key once-- indicate B3--once again, Enter, same formula, same result. It's just a question of saving a little bit of time by not having to type in addresses. A third way might involve two hands, it might sound like it's going to take longer, but it can be pretty fast as well. It begins as you would expect--equal sign-- I'm going to click on B2, then minus on the keyboard, then click cell B3, and then Enter. Here too, same result as before, same formula. In cell H2, we need a total of these six cells right here. If we haven't seen much of Excel formulas or other techniques, we're going to be typing =b2+c2+d2, I'm going stop there. This method will work but I got to put in three more locations. What would happen if this is 12 months of data? Three years of data? This is not the best way, although, it would give us a correct answer ultimately. Let's show a better way. Built-in to Excel is a feature called "Functions" and there are over 400 of them. "Quick capsule description" is a function--is a shortcut for a formula. They can often be a lot more than that. Probably, the most commonly used function by most Excel users is the one called SUM. Functions begin with the equal sign also, so it indicates they are in the family of formulas. SUM, it's followed by parentheses and within the parentheses we're either going to see a range of cells across a row, possibly down the column or possibly across many, many cells. We can even have commas in different cells located in different locations. But in this example here, we simply want to select the six cells to the left-- and you can click and drag rightward or click and drag leftward, doesn't make any difference--so I've got the mouse right here in G2, hold down the Left Mouse button, drag leftward, there we go, let go of the mouse and we're done, except for pressing Enter. We don't need to type that right parenthesis, but it would be there anyway--"Enter". That's a function. That's a correct answer. Double-clicking it simply redisplays it and we can also see it of course in the Formula Bar. Add up all the cells, B2 through G2. Now, for an average here, we need to add up those first six months and divide by six, but we already have the total here, so we don't have to really do the addition here. The average begins with equal and here's the amount that's totaled right there on H2, so you can type it or click on it. Slash for division. You cannot use the slash that goes in the other direction. You'll see the slash on larger keyboards on the number pad, on most standard keyboards it tends to be above the Enter Key. Divide by 6--Enter"-- we've got our average here. Earlier we saw how to use a function here to tabulate totals and before that we saw a simple subtraction of cells right here. Of course, you will see in some formulas a plus sign for addition. The other symbol that we haven't seen just yet is the asterisk which is used for multiplication, but there's no question that formulas are critical to the operation of many, many Excel worksheets.

Contents