Summary Statistics Excel TutorialPowerPoint PresentationSlide 3Slide 4Slide 5Slide 61Summary Statistics Excel TutorialUsing Excel to calculate summary statisticsPrepared for SSAC by*David McAvity – The Evergreen State College*© The Washington Center for Improving the Quality of Undergraduate Education. All rights reserved. *2007*Supporting Quantitative concepts and skillsVisual Display of Data – Bar ChartsCore Quantitative concepts and skillsStatistics: Mean, Mode, Median and Standard Deviation2We’ll use this class example to illustrate how to use Excel to calculate a variety of summary statistics and how to plot frequency data in a bar chart Number of Children in the FamilyCreate a worksheet like this one and enter the data as shown. Use the Sum() function to add up the frequency column to find the total number of people in the class. = cell with a number in it= cell with a formula in itHint: to sum a column click in the cell you want the sum to be in (eg B13) and type =Sum(). Inside the parentheses type the cell range (in this case B4:B12). The entry would look like this:Instead of typing a cell range you can also just select the cells you want with the mouse.=Sum(B4:B12)A B1Children in the family23x (children)f (frequency)4 1 95 2 326 3 247 4 58 5 49 6 310 7 011 8 212 9 113 Total 803Graphing the SolutionTo get a visual impression of the data it is useful to plot it. You can create a bar chart or pie chart readily in Excel. Lets create a bar chart.Create the following bar chart using the chart wizard . Make sure you label all the axes.To create a bar chart select the frequency column and click on the chart wizard icon. Choose the column chart type then click next. In the next dialogue box click the series tab and then fill in the correct range of values in the category (X) axis labels. Click next and then enter axis and title labelsA B1Children in the family23x (children)f (frequency)4 1 95 2 326 3 247 4 58 5 49 6 310 7 011 8 212 9 113 Total 80Number of Children in the Family051015202530351 2 3 4 5 6 7 8 9Number of ChildrenFrequency4Now we use Excel to assist with calculating the three measures of central tendency, the mode, median and mean. The mode is the most common value, the median is the middle value of ranked data and the mean is the sum of the values divided by the total number of values:Measures of Central Tendency Write Excel formulas in the peach colored cells in order to help you evaluate the mean, mode and median. Write down or calculate each of those statistics in separate cells of your worksheet. ( You should get a mean of 2.85)A B C D1Children in the family23x (children)f (frequency)f x f4 1 9 9 95 2 32 41 646 3 24 65 727 4 5 70 208 5 4 74 209 6 3 77 1810 7 0 77 011 8 2 79 1612 9 1 80 913 Total 80 228Hint: Need help with entering formulas? Here is an example: If you have a number in Cell B2 and you want to calculate the square of it in Cell C2 then in C2 you type =B2^2. If you have another value in B3 then if you copy cell C2 to C3 the formula will update to reference C3. This is called an relative reference. If you want the formula to always refer to a particular cell (eg B2) use an absolute reference: type =$B$2^2 instead. B C2 4 =B2^2B C2 4 =$B$2^2fxxThis column is the cumulative sum of frequencies (ie a running total). Each entry is the sum of all previous frequencies. You need this to find the median value.5The standard deviation is the measure of the spread of data. It is a measure of the average distance of values from the mean value. More specifically it is the square root of the mean squared deviations from the mean. Its formula is: Where n is the sum of the frequenciesStandard DeviationUse Excel formulas to calculate the last column below. It will be helpful to use an absolute reference to the cell containing the mean value in your formula, so that when you copy the formula to new rows the reference to the mean does not change. Now calculate the standard deviation in a separate cell. (You should get 1.58).nfxxs2)(A B C D E1C h ild r e n in t h e f a m il y23x ( c h ild r e n ) f ( f r e q u e n c y )f x f4 1 9 9 9 3 0 . 8 0 2 55 2 3 2 4 1 6 4 2 3 .1 26 3 2 4 6 5 7 2 0 .5 47 4 5 7 0 2 0 6 . 6 1 2 58 5 4 7 4 2 0 1 8 .4 99 6 3 7 7 1 8 2 9 .7 6 7 51 0 7 0 7 7 0 01 1 8 2 7 9 1 6 5 3 .0 4 51 2 9 1 8 0 9 3 7 .8 2 2 51 3 T o t a l 8 0 2 2 8 2 0 0 .2fxx2)( 6Collect the following data in your lab notebook:• Heart rates: Measure your heart rate in beats per minute. Now, go outside and run around the lecture hall as fast as you can and immediately on return measure your heart rate again. Record both values of the heart rate in the appropriate column of the class spreadsheet.• Reaction time: to be completed with your lab partner -- one partner should hold a meter stick vertically while the second lab partner places her fingers near the lower end of the meter stick in a position ready to catch it, but not touching. After the first partner releases the meter stick the second partner should try to catch it. Measure how far the meter stick falls. Repeat the experiment 10 times. Next the second partner should close her eyes. The first partner should say NOW at the same time as releasing the releasing the meter stick. Measure how far the meter stick falls before catching and repeat 10 times. Now swap roles.• Uncertainty in measurement: half the class will be given meter sticks and the other half will be given 50 m measuring tapes. You will be asked to measure the height of a tall feature near the CAL using your measuring instrument. Enter your data on the class spreadsheet at the front of the class in the appropriate column.In an Excel file create separate labeled spreadsheets for each set of paired data and calculate the mean, mode, median and standard deviation and charts. On each sheet, create a textbox and compare the data sets in pairs using the above statistics and histograms. It is important in your comparison that you interpret the meaning and significance of each statistics as it applies to the data under consideration. Save your Excel file using the naming convention Lastname_Firsname_CAL_Lab_1.xls and copy and paste it into the dropbox on our CAL program …