You use a lot of different functions in your workbooks, but two of the most useful summary functions in Excel 2016 are SUM and AVERAGE.
- [Instructor] You use lots of different functions in your workbooks, but two of the most useful summary functions in Excel 2016 are Sum and Average. They operate as you'd expect, calculating the sum and average of a series of worksheet values, but I'd like to go over each function in detail so you'll have a greater appreciation of how they work. My sample workbook is the SumAvg file, and you can find it in the chapter two folder of the exercise files collection. In this case, I have a list of yearly revenue for the fiscal years 2014 through 2016, and also special revenue for those years.
Let's say that I want to find the total of the values in B2 through B4. Well, there are a couple of ways to do that. The way that I don't recommend is to type in the numbers directly. So if I click cell B5 and then type an equals sign, I can say Sum followed by a left parentheses, then 942 followed by three zeroes, comma, 1325, followed by three zeroes, then a comma, and 1749, followed by three zeroes and a right parentheses.
So what I've done is entered in the three numbers as they are entered in the worksheet, and again, this is the wrong way to do it. I just want to show that you can. Press Enter, and I get the sum. That was horrible, absolutely the wrong way to do it. So I'm going to press Control Z to undo my edit, and now I'm going to enter the sum by selecting the cells B2 through B4. I can also type them in, but I think you get the idea. So in cell B5, I'll type an equal sign, and then the sum function followed by a left parentheses, and I will select cells B2 through B4, type a right parentheses to close out the argument list, press Enter, and there I get my total.
It's the same total as before, but it was much easier and much less prone to mistakes. If I wanted to find the total of all of the revenue, so the yearly revenue and the special revenue, I could use the up arrow to go back to cell B5, press Delete to get rid of the existing formula, and then type equal, sum, left parentheses, then I'll select cells B2 through B4 again, but now I'll hold down the Control key and select cells D2 through D4.
If I look at the formula, I see that B2 colon B4 indicates the range, then there's a comma, and D2 colon D4 indicates the second range. So I'll type a right parentheses and Enter, and there I get my total of all six of those cells. Average works the same way, and if I want to find the average of my special revenue I can click in cell D5, type equal, and then average. Then a left parentheses, I'll select cells D2 through D4, right parentheses, Enter, and there is the average.
Now, it is possible to accidentally include non-numerical values in sum and average formulas. So let's say that I accidentally found the total of cells A2 through A4. These are years, and I specifically formatted them as text rather than numbers, you can see that they start with the letters FY, indicating fiscal year, so that I wouldn't change my results of the sum operation. So if I were to go into cell B5 and type equal, sum followed by a left parentheses, and then A2 through B4 is the range I'll select, then I'll type a right parentheses and Enter, and I get the same result as before when I added just B2 through B4.
So what happens is that Excel ignores any non-numerical values in the sum and just treats them as a zero. So if I were to do equal sum then left parentheses and then select A2 through A4 followed by right parentheses and Enter, I would get a zero. For average, however, you have to have at least one value that is not zero. So if I click in cell D5 and type equal, average, followed by a left parentheses, and then select A2 through B4, and then right parentheses and Enter, then I get the average of those three values.
But if I edit the formula so that it is A2 through A4, which contain the text FY2014, 15, and 16, and press Enter, then I get a divide by zero error. And again, the reason I get a divide by zero error is that there are no numerical values so the average can't find a value, it has to try to divide by zero, and that's why I get the error.
- Using operators
- Use cell references in formulas
- Formulas: SUM, AVERAGE, MIN, MAX, MEDIAN, and MODE
- Counting cells
- Summarizing cells conditionally
- Rounding cell values up and down
- Working with list data
- Finding data using VLOOKUP formulas
- Connecting to an external data source
- Cleaning up imported data
- Validating data using rules
- Tracing precedents and dependents
- Identifying errors
- Finding target values using Goal Seek