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

VIEWS: 45 PAGES: 8

									                                          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