Yorkville High School Computer Science

Computer Applications I :: Lessons :: Functions

Average, Maximum, Minimum

Excel and Google Sheets contains hundreds of functions that can perform simple or complex calculations on your data. While it would be impossible to cover all of the functions in one semester, we will cover some of the important ones. In fact, we have already used the Sum function in a number of our projects. This lesson will focus on three more important functions: Average, Max, and Min.

1. Create your spreadsheet like the one below:

Excel Instructions

2. Click in Cell B7.
3. Go to Home>Editing>AutoSum>Average.

Google Sheets Instructions

2. Click in Cell B7.
3. Click on the Functions button on the right and choose "AVERAGE".
4. Highlight Cell B2 through Cell B6.

Both Excel and Google Sheets have an arrow that give you access to some of the most commonly-used functions in the programs. When you choose the Average function a marquee (the marching ants) will appear around the data that Excel thinks you want to find the average on. Google Sheets requires you to select the data yourself. You can change the selection by pulling on the corners of the marquee, but in this case Excel should have it correct. You should see "=AVERAGE(B2:B6)." The colon in between B2 and B6 means through. So what is happening is Excel is finding the average of Cells B2 through B6. Now let's find the highest and lowest GPA for the freshman.

Excel Instructions

4. Click in Cell B8.
5. Go to Home>Editing>AutoSum>Max.
6. Drag the marquee to include Cells B2 through B6.

Google Sheets Instructions

5. Click in Cell B8.
6. Click on the Functions button on the right and choose "MAX"x.
7. Drag the marquee to include Cells B2 through B6.

Note that we had to change the marquee selection for this function because Excel tried to include the average we calculated when determining the maximum. While our result wouldn't have changed it is still important to make sure the correct data is being used in functions. An alternative to dragging the marquee is editing the function in the Formula Bar. You can also type the entire function in the Formula Bar, which is what we will do for the lowest GPA.

8. Click in Cell B9.
9. In the Formula Bar type "=MIN(B2:B6)".

When you type functions directly into the Formula Bar it avoids the problem of Excel misjudging the data you want to use. Sometimes this is a much easier way of using functions, but either way should work. Now we just need to find the average, highest, and lowest GPA for the sophomores. You can copy-and-paste the formulas from the freshman section to do this.

10. Click-and-drag over Cells B7 through B9.
11. Press "Ctrl-C" to copy the cells.
12. Click in Cell E7.
13. Press "Ctrl-V" to paste the formulas.

Finally, a GPA normally has two decimal places so we want to round all our number to two decimal places. You can highlight multiple cells at once, or round them one at a time.

14. Round all numbers using Increase/Decrease Decimal to 2 decimal places.

After rounding, your spreadsheet should look like the one below.



Selecting Data From Disjoint Cells

Sometimes you will find that the data you need for a chart isn't conveniently located in one place. You may need to select two or more cells that are disjoint, or not next to each other. This will be the case with the chart we are going to make for the averages.

15. Click in Cell B7.
16. Hold down the "Ctrl" key.
17. Click on Cell E7.

You should see both cells highlighted now. It is important that you hold down the "Ctrl" key at the correct time. A common mistake is holding down the "Ctrl" key before clicking on this first cell. This causes the cell that was previously selected to also be included in the selection. If you notice when creating a chart that it has one extra data field than this is most likely the problem.

Editing Chart Data

18. Go to Insert>Charts>Pie
19. Choose one of the single pie chart options

The pie chart will be added to your spreadsheet, but you may notice that the legend is not labeled. We want it to say "Freshman" and "Sophomore," but it simply says "1" and "2." We can fix this by right-clicking on the pie chart and editing where it pulls its data from.

20. Right-click on the pie chart
21. Choose Select Data...
22. Click the Edit Button under Horizontal Axis Labels
23. Click on Cell A1 that holds the text "Freshman"
24. Hold down "Ctrl"
25. Click on Cell D1 that holds the text "Sophomore"
26. Press "Enter" to submit the changes
27. Title your chart "Average GPAs"



In addition to changes legend of axis labels you can also use Select Data... to change the data that a chart uses. However, it is much easier to select the data ahead of time. There are cases where you may find it easier to select the data after creating the chart so let's try it out once.



27. Go to Insert>Pie
28. Right-click on the pie chart
29. Choose Select Data...
30. Click the button to the right of Chart Data Range
31. Click on Cell B8
32. Hold down "Ctrl"
33. Click on Cell E8
34. Press "Enter" to submit the changes
35. Click the Edit Button under Horizontal Axis Labels
36. Click on Cell A1 that holds the text "Freshman"
37. Hold down "Ctrl"
38. Click on Cell D1 that holds the text "Sophomore"
39. Press "Enter" to submit the changes
40. Title your chart "Lowest GPAs"

You can use either method of creating charts you prefer, but it is always important to remember how to use the Select Data... option since there will be many situations in which you will find it useful. Let's complete the lesson by creating a chart for the highest GPAs.

41. Create a pie chart for the highest GPAs