# Using Microsoft Excel to Compute the Mean, Median, and by mro72277

VIEWS: 45 PAGES: 8

• pg 1
```									                                          Applied Math
Excel for Mean & Median
Using Microsoft Excel to Compute the Mean, Median, and 1st and 3rd Quartiles
So far, we have covered how to compute the mean, median, and 1st and 3rd quartiles both by hand
and using the calculator. This handout is intended to show you how to do this using Microsoft
Excel. For our example, we will use the following data set:
2, 5, 6, 7, 8, 20, 21, 19, 15, 22, 24, 9, 11, 10, 15, 22, 21, 25, 32, 33, 1, 5, 39, 22 and 40
Let us assume these are quiz scores for a 40 point quiz. The first thing you need to do is to put
the data into the spreadsheet. Prior to that, it is nice to know what you are working with and why.
So go into Excel and put a header in A1, your name in A2, and a title in B3 (ask me about
inserting comments) just to show what we are working on…

You can make this really pretty if you want to, or just leave it plain for now. Now we need to
enter the scores, so starting with B4 and continuing down the column, enter the scores…
(press enter, or arrow down after each entry)
Applied Math
Excel for Mean & Median

A box that contains the total might be a nice touch, so we label a box in A30 “total” and in B30,
type = sum(B4:B28 then hit enter…
(You can also highlight cells B4 through B28 by clicking, holding and dragging instead of typing
B4:B28)
Highlighted cells are referred to as the array
Applied Math
Excel for Mean & Median

In A30, type a heading for Mean

To compute the mean, type = average(B4:B28 …
Applied Math
Excel for Mean & Median

To compute the median, type = median(B4:B28
Applied Math
Excel for Mean & Median
To compute the 1st quartile, type = quartile(B4:B28, 1
The “scripting” for quartiles calls for “=quartile(array, quartile
Recall: Array is the range of the data items or the highlighted cells

To compute the 3rd quartile, type = quartile(B4:B28, 3

The min & max can be computed by using 0 & 4, respectively following the array. You can also
use this notation with 2 for the quartile to compute the median.

Excel does have a built-in function for =min(array, for the minimum, =max(array, for the
maximum, and =median(array for the median.
Applied Math
Excel for Mean & Median
To widen columns: Place the cursor between columns so that a “cross” with left and right arrows
appears, then click and widen. (Ask for help if you get stuck)

Note: The values for your quartiles will vary slightly from those with a TI-83+ since Excel
includes the median in the “halves” of the data, whereas the TI-83+ does not.

USE THE Print Preview, then PAGE SETUP FEATURE to set up your sheet to fit on 1 page.
Choose LANDSCAPE orientation and FIT TO 1 PAGE (ask for help if needed with this)

The completed project appears below:
Applied Math
Excel for Mean & Median

Use Excel to duplicate this example.
25 points due by EOC
(I will be checking your boxes to see if you let Excel make the computations)
Applied Math
Excel for Mean & Median

If you finish early, play around with formatting, cell borders, fill color, etc.

```
To top