Creating Totals and Averages with Microsoft Excel 2013
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 cellskeep an eye on the Formula Barit reminds you what the cell contains. If you happen to doubleclick a cell sometimes you'll need to do this to change the formula it displays the formula in a colorcoded 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 B3once 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 expectequal 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. Builtin to Excel is a feature called "Functions" and there are over 400 of them. "Quick capsule description" is a functionis 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 differenceso 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. Doubleclicking 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 6Enter" 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.
Creating simple formulas: Totals and averages provides you with indepth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training
 Subjects:
 Business Education + Elearning
 Software:
 Excel
 Author:
 Dennis Taylor

1m 6s

 Welcome

43s


29m 37s

 What is Excel used for?

1m 49s

 Using the menu system

4m 30s

 The Quick Access Toolbar

4m 41s

 Using the Formula bar

1m 43s

 Using the Status bar

2m 24s

 Using the builtin help

2m 54s

 Creating new files

2m 11s


24m 1s

 Working with dates and times

3m 32s

 Using Undo and Redo

4m 50s

 Adding comments

2m 55s

 Using Save or Save As

3m 57s

30m 7s

 Using SUM and AVERAGE

3m 25s

46m 7s

 Designing borders

3m 26s

 Formatting numbers and dates

4m 31s

 Conditional formatting

4m 21s

 Creating and using tables

9m 59s

20m 40s

17m 51s

30m 30s

 Creating charts

4m 36s

 Exploring chart types

7m 47s

 Formatting charts

5m 42s


12m 49s

23m 0s

20m 25s

23m 50s

 Protecting workbooks

2m 40s

 Sharing workbooks

4m 7s

 Tracking changes

4m 32s

28m 32s

 Sorting data

6m 9s

 Using filters

6m 16s

 Removing duplicate records

2m 38s


35m 2s

 Creating PivotTables

8m 36s

 Manipulating PivotTable data

9m 47s

 Grouping by other factors

2m 33s

 Using PivotCharts

3m 59s


23m 29s

 Using Goal Seek

6m 8s

 Using Solver

6m 34s

 Using Scenario Manager

6m 11s

 Using Data Tables

4m 36s


24m 31s

 Definition and examples

6m 48s

 Creating a simple macro

7m 0s

 Running a macro

10m 43s


29s

 Next steps

29s

Related Courses

Excel 2010 Essential Training6,004,543 Views

Word 2013 Essential Training1,681,273 Views

Access 2013 Essential Training2,706,153 Views

PowerPoint 2013 Essential Training1,667,653 Views

Excel 2013: Pivot Tables in Depth1,331,864 Views

Access 2013 Essential Training2,706,153 Views

Word 2013 Essential Training1,681,273 Views

Excel 2010 Essential Training6,004,543 Views

PowerPoint 2013 Essential Training1,667,653 Views

Foundations of Programming: Fundamentals7,748,469 Views