Basic Math 2

Lesson 10: Sum Cells, Averages, & Order

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 10, we continue with basic math in Excel by learning how to add multiple cells together, calculate the average of a group of cells, and discuss the importance of the order of operations in mathematics. I will show you how to sum numbers both by typing and by clicking on cells, and we will walk through what happens if you do not use parentheses in your formulas. We will also talk about how Excel computes formulas according to math rules, and how to correct your formulas to get accurate results.

Navigation

Prev: Lesson 09
Next: Lesson 11

Keywords

TechHelp Excel, Excel basic math, add multiple cells Excel, Excel average formula, Excel order of operations, Excel sum, sum cells Excel, average cells Excel, parentheses in Excel formulas, Excel formula bar, Excel status bar, Excel math operations

Subscribe to Basic Math 2
Get notifications when this page is updated

Transcript

In lesson 10, we are continuing on with basic math and Excel. We will learn how to add multiple cells together, take an average of a group of cells, and we will learn about the order of operations and mathematics.

In the last lesson, we learned how to perform some basic math operations on our data. We only added numbers two at a time, however.

Let's say you want to add more than just two numbers. Let's say we have a column of test grades: 90, 85, 70, 65, and 90 again. Now, if you want to quickly see the sum or average of those numbers, just select those cells, select A1 through A5, and notice down here on the status bar, you can see the average, count, and sum of any numbers that you have selected. That is a neat little feature, but it does not store the value anywhere in our sheet.

Now, how do you get that sum in your spreadsheet? It is as simple as this: In cell A6, I will type in equals (remember you have to start every formula with the equal sign), equals A1 plus A2 plus A3 plus A4 plus A5, and then press Enter. If you click on A6, you will see the formula up here in the formula bar, and you will also see the result here in cell A6.

Now, here is a little shortcut if you do not like all that typing. I will delete A6 with the delete key, and watch this. You can type in equals, and then just click on the cells, click on A1, and then type in the plus, click on A2 plus, click plus, click plus, click. That is a little faster than typing all that out. You can just click on the cell that you want to include in the formula. I will press Enter, and I get the same result.

Now, let's say instead of adding all those numbers up, I want to calculate their average. How do you calculate an average? You add up all the values, and then you divide by the total number of values. In this case, I will add up A1 through A5. That is five values, then I will divide by five. So I should be able to click on A6 and edit my formula. I have got the numbers added up here, and then come to the end and go divided by five. All right, divided by five, and press Enter.

Hold on. 328 is definitely not the average of those five numbers. It should be somewhere in the middle of them. Take a look at the formula again, see if you can figure it out. I have added up A1, A2, A3, A4, and A5, and then divided by five.

What is the problem? We forgot about the basic math order of operations. If you go back to high school, they taught you that the equation goes first (that is raising a number to a power), then comes multiplication and division, then finally comes addition and subtraction. If you want to violate these rules, you have to use parentheses.

For example, if you have a formula like this: 9 plus 5 times 2, the result equals 19, because 5 times 2 is evaluated first, that comes out to 10, and then 9 is added to that result. If you want the 9 plus 5 to be evaluated first, you have to enclose them inside a parenthesis. Now the 9 plus 5 is evaluated first, that is 14, then multiplied by 2, and the result is 28.

As you can see, there is a big difference when you include those parentheses. So in order to fix our math problem here in Excel, all I have to do is put parentheses around the addition problems first, then divide by 5. So I will put an open parenthesis there, and a closed parenthesis right there after the A5. Now when I press Enter, I get the correct value, and 80 looks like it is about the average of those five numbers.

If you want to double check it, just select them, and Excel will tell you on the bottom down here in the status bar, that yes, that is indeed the average.

What happened the first time with the original formula was that Excel followed the order of operations. It divided A5 by 5 first, and then it added the other values in. So Excel was just doing its job, and following proper math rules.

Now with the corrected formula, all five cells are added together first because they are inside parentheses, then that result is divided by 5.

Intro

Quiz

Q1. What is the first step you must do when entering a formula in Excel?
A. Start with the equals sign
B. Start with a plus sign
C. Start with parentheses
D. Start with the word SUM

Q2. How can you quickly view the sum or average of selected cells without entering a formula in a cell?
A. Click the Review tab
B. Look at the status bar at the bottom
C. Use the Insert Function button
D. Save and reopen the sheet

Q3. If you have values in cells A1 through A5, how could you manually add them together in cell A6?
A. =A1+A2+A3+A4+A5
B. =SUM(A1-A5)
C. =A1*A2*A3*A4*A5
D. =SUM(A1/A5)

Q4. What is the shortcut described in the lesson for creating a formula that adds several cells together?
A. Type the cell names manually
B. Drag the fill handle across cells
C. Type equals and then select cells with the mouse, adding plus between each
D. Use the Data Validation feature

Q5. How do you manually calculate an average for five numbers in Excel?
A. Divide the highest value by five
B. Add all five numbers and then divide by five
C. Multiply all the numbers together and divide by five
D. Use the CONCATENATE function

Q6. What is the order of operations in mathematics as described in the lesson?
A. Addition, subtraction, multiplication, division
B. Multiplication, addition, division, subtraction
C. Powers, multiplication/division, addition/subtraction
D. Multiplication, subtraction, division, addition

Q7. What happens if you omit parentheses in the average formula =A1+A2+A3+A4+A5/5?
A. Excel displays an error message
B. Only A5 is divided by 5 before the addition
C. All values are first added and then divided by 5
D. The formula will average the numbers

Q8. How can you ensure that all numbers are added together before dividing by five in a formula?
A. Type the word FIRST in the formula
B. Use SUM instead of PLUS
C. Enclose the addition in parentheses before dividing
D. Ignore the order of operations

Q9. Which of the following correctly calculates the average of values in cells A1 through A5?
A. =(A1+A2+A3+A4+A5)/5
B. =A1+A2+A3+A4+A5/5
C. =A1+A2+A3+A4+A5*5
D. =(A1+A2+A3+A4+A5)*5

Q10. If you want to verify the average or sum quickly, what should you do?
A. Use the MAX function
B. Select the cells and check the status bar
C. Type the results into a new worksheet
D. Use the Insert menu

Answers: 1-A; 2-B; 3-A; 4-C; 5-B; 6-C; 7-B; 8-C; 9-A; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone continues our exploration of basic math operations in Excel. We'll focus on how to add multiple cells together, calculate an average, and discuss a critical concept in mathematics called the order of operations.

In our previous lesson, we learned how to do simple math with Excel, but we only worked with two numbers at a time. Now, let's look at a practical scenario. Imagine you have a list of test scores: 90, 85, 70, 65, and another 90, each in their own cell in a column.

If you want a quick look at totals and averages for these cells, you can simply select them, and Excel will show the average, count, and sum in the status bar at the bottom of the screen. It's a handy feature for quick summaries, but it won't actually record those results in your worksheet.

To store the sum in your spreadsheet, you need to enter a formula. In cell A6, for example, you would start with the equal sign and add each relevant cell together. So, you would enter equals, then A1 plus A2 plus A3 plus A4 plus A5. After pressing Enter, the total of those scores will appear in A6, and you can always see the formula itself in the formula bar above.

To save time, you don't have to type each cell reference. You can use the mouse to click on each cell as you build your formula. Just enter equals, click on the first cell, type plus, then click on the next cell, and so on. This approach minimizes typing and reduces errors.

Now, let's talk about calculating the average for these scores. When you want to find an average, you add all the numbers together and then divide by how many numbers there are. Here, you'd sum A1 through A5, then divide by 5, since there are five values. You can update the formula in A6 to reflect that: just add division by five at the end.

But here's where many people stumble. If your formula reads as A1 plus A2 plus A3 plus A4 plus A5 divided by 5, you might notice that Excel gives a result that does not look like an average at all. That is because of the order of operations. In normal math, multiplication and division are carried out before addition and subtraction unless you specify otherwise with parentheses.

To illustrate, consider the expression 9 plus 5 times 2. Standard math rules dictate that you should multiply before adding, so 5 times 2 equals 10, and then you add 9 to get 19. If you want the addition to happen first, you have to put 9 plus 5 inside parentheses, resulting in 14, and then you multiply by 2 to get 28. This small change completely changes the answer.

So, to make sure your average is calculated properly in Excel, you need to put all your additions inside parentheses first, like this: sum all five cells in parentheses, then divide that sum by five. Now when you press Enter, you'll see the correct average, which should be a value between your lowest and highest scores.

If you want to confirm your answer, just select all five cells again and look at the average displayed in the status bar at the bottom of Excel. That should match your calculation.

What happened in the first formula was that Excel divided the last value by five first, then added the rest, simply following the standard order of math operations. Once you use parentheses, Excel will add up all the cells first and only then divide by the number of items. This ensures your average is accurate.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Adding multiple cells together in Excel
Using the SUM formula to total values
Using cell selection to build formulas
Calculating the average of a group of cells
Editing formulas directly in a cell
Understanding order of operations in math
Using parentheses to control formula calculations
Difference between sum and average in Excel
Using the Excel status bar to view sum and average

Article

In this article, we will continue learning about basic math operations in Excel. You will see how to add up several cells at once, find the average of a group of numbers, and understand how the order of operations affects your calculations. All of this will help you work more efficiently and accurately in your spreadsheets.

When you have a set of numbers in a column, such as test grades like 90, 85, 70, 65, and another 90, you may want to quickly find the total or the average. If you simply want to see the sum or average without recording it in the sheet, you can select the cells, for example A1 through A5. You will notice that Excel displays the sum, average, and count of the selected numbers in the status bar at the bottom of the window. While this is handy for a quick look, it does not store the results in your spreadsheet.

If you want to keep the total in your sheet, you need to enter a formula. To do this, click on cell A6 and type an equal sign to start your formula. Then type A1 plus A2 plus A3 plus A4 plus A5, all together, so your formula looks like =A1+A2+A3+A4+A5. After you press Enter, Excel displays the total in cell A6, and you can also see the formula in the formula bar at the top when you click on cell A6.

There is also a quicker way to build this formula. Instead of typing each cell reference, you can start with an equal sign, then click on the first cell (A1), type the plus sign, click the second cell (A2), type a plus, and so on until you have added all the cells you want. Click Enter, and the formula will add up all the selected values.

To calculate the average of these test grades in Excel, you need to find the total and divide it by the number of grades. In this case, there are five numbers. So, you start by making sure the sum formula is correct, then add division by five at the end. For example, you would take your formula in A6 and change it to =A1+A2+A3+A4+A5/5.

However, if you try this, you might get an unexpected result. The average comes out wrong because Excel follows the order of operations, which is the rule for which math operations are performed first in any calculation. According to order of operations, calculations are done in this order: parentheses first, then exponents, then multiplication and division, and finally addition and subtraction. If you have an equation like 9+5*2, Excel multiplies 5 by 2 first, then adds 9, giving you 19, not 28. To change the order so that 9+5 is calculated first, you use parentheses: (9+5)*2. This is then 14 times 2, which equals 28.

Back in your Excel sheet, you need to use parentheses to make sure that all the numbers are added together before dividing. So, the correct average formula is =(A1+A2+A3+A4+A5)/5. By putting parentheses around the sum, Excel adds the numbers first and then divides by 5, giving you the correct average. When you press Enter, you will see the accurate result, which in this example should be 80.

If you want to double check your result, you can highlight the group of numbers. Excel will show you the average in the status bar at the bottom of the window.

What happened with the first formula, without parentheses, is that Excel divided only the last value (A5) by 5 before adding it to the others. This happened because division comes before addition in the order of operations. By adding parentheses, you ensure that everything inside is summed first, and then the total is divided, so you avoid this mistake.

Remember, whenever your formula involves a mix of different mathematical operations, always consider whether you need parentheses to get the result you expect. This habit will keep your calculations accurate and your spreadsheets reliable.

Primary Topics

Excel formulas, cell references, summing multiple cells, averaging values, order of operations, parentheses in formulas

Secondary Topics

Excel status bar, formula bar