For this lesson you need to open the sample file: First Quarter Sales and Profit-5 from your Sample Files folder.
In Lesson 2-3, you used the AutoSum function to add all of the values in a range of cells, but the AutoSum feature isn't only restricted to addition. It's also able to compute averages and maximum/minimum values.
In this lesson we'll use AutoSum to calculate the average sales of the New York branch in the first quarter. So I'll begin by deleting the current values in cells E3 and E4. I'll position the mouse cursor into the centre of cell E3 and then click and drag down to E4 and then press the Delete key on the keyboard. I'll now type into cell E3 the word: Average, and press the Enter key to move down to cell E4.
Now on the Home tab on the Ribbon, in the Editing group, I'm going to hover over the AutoSum button. And notice that the AutoSum button is actually a Split button. It has an AutoSum button on the left and a dropdown arrow on the right hand side.
I'm now going to click that dropdown arrow and you can see that, as well as a Sum, I can also use AutoSum to return an Average a Count, a Maximum, a Minimum, and even more functions from the Excel Function Library.
The function I want is the Average, so I'll click Average. And notice that the function that has appeared in cell E4 isn't =Sum anymore, it's: =Average. So I'll now press the Enter key, and you can see that the average sales for the New York branch in the first quarter were 23,333.
Let's now use the AutoSum to return the Maximum sales value in the first quarter. So I'll click in cell F3 and type "Maximum" into the cell, and press the Enter key to move down to cell F4.
This time I'm going to place a Max function into cell F4. So on the Home tab on the Ribbon, in the Editing group, I'll click the dropdown arrow to the right of AutoSum and select Max.
And notice that we've now got, not =Sum or =Average, but =Max. This is the Max formula, and it's returning the maximum value in the stated range.
But notice that Excel's made a mistake. Its included cells B4 to E4, and it should only be including cells B4 to D4. So I'll use the mouse to select cells B4 to D4, and I've now corrected the formula.
I'll press the Enter key and I can see that the maximum sales in the first quarter for the New York branch were now 29,000. That would be in February.
There's also a way to change the range that the AutoSum Function is referencing, even after you've completed the AutoSum Function. Let's demonstrate that now, by changing the text in cell A9 to simply "Sales", instead of "USA Sales". And then I'll press the Tab key to move to cell B9.
If I look at the Formula Bar, I can see =SUM(B4:B5). And that was correct for the USA Sales.
Cells B4 and B5 contain the New York and Los Angeles sales figure. And 22,000 plus 42,000 is indeed 64,000.
But I now want to reference the sales for every branch. So to change the range I'm going to press the F2 key on the keyboard.
I'll press F2 now. So you can see that a marquee has appeared around cells B4 and B5. And notice that there's a spot on each corner of the marquee. These are called Sizing Handles.
I'm going to carefully hover the mouse cursor over the bottom right sizing handle. And you can see that the cursor shape has changed to a two headed arrow.
I'll now hold down the Mouse button and drag downwards to increase the size of the range to B4 to B8. And you can see that that's been echoed in the SUM Function in cell B9.
So when I release the Mouse button and press the Enter key, I'm getting the correct sales for all branches of 129,000.
All that remains now is to save your work. So I'll click File and Save As, and I'll save with the new name: First Quarter Sales and Profit-6.
I click the Save button and you've now completed Lesson 2-11: Use AutoSum to Quickly Calculate Averages.