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.
Pages to are hidden for
"Using Microsoft Excel to Compute the Mean, Median, and"Please download to view full document