Scribd Logo
Upload

Welcome to Scribd!

  • 31 views

    M 3: M D, M R D A T: Odule Easures of Ispersion Easures of Elationship AND ATA Nalysis OOL

    Uploaded by

    Aadel Sepah
    This document provides instructions for using Excel 2007 to analyze test score data through measures of dispersion, relationships, and the data analysis tool. It explains how to calculate the minimum, maximum, variance, standard deviation, covariance, and correlation coefficient of two sets of test score data to measure their relationship. The data analysis tool allows generating descriptive statistics more quickly by selecting the data, output range, and desired statistics.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    M 3: M D, M R D A T: Odule Easures of Ispersion Easures of Elationship AND ATA Nalysis OOL

    Uploaded by

    Aadel Sepah
    31 views6 pages
    This document provides instructions for using Excel 2007 to analyze test score data through measures of dispersion, relationships, and the data analysis tool. It explains how to calculate the minimum, maximum, variance, standard deviation, covariance, and correlation coefficient of two sets of test score data to measure their relationship. The data analysis tool allows generating descriptive statistics more quickly by selecting the data, output range, and desired statistics.

    Original Description:

    vrka grach

    Original Title

    excelModule_3

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for using Excel 2007 to analyze test score data through measures of dispersion, relationships, and the data analysis tool. It explains how to calculate the minimum, maximum, variance, standard deviation, covariance, and correlation coefficient of two sets of test score data to measure their relationship. The data analysis tool allows generating descriptive statistics more quickly by selecting the data, output range, and desired statistics.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    31 views6 pages

    M 3: M D, M R D A T: Odule Easures of Ispersion Easures of Elationship AND ATA Nalysis OOL

    Uploaded by

    Aadel Sepah
    This document provides instructions for using Excel 2007 to analyze test score data through measures of dispersion, relationships, and the data analysis tool. It explains how to calculate the minimum, maximum, variance, standard deviation, covariance, and correlation coefficient of two sets of test score data to measure their relationship. The data analysis tool allows generating descriptive statistics more quickly by selecting the data, output range, and desired statistics.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 6

    EXCEL 2007 RESOURCES FOR DATA ANALYSIS

    MODULE 3: MEASURES OF DISPERSION, MEASURES OF RELATIONSHIP AND DATA ANALYSIS TOOL


    SCHOOL OF PUBLIC ADMINISTRATION, UNIVERSITY OF VICTORIA

    1. Learning Objectives
    This module will include: Calculation of maximum and minimum value, variance, and standard deviation, Covariance and correlation coefficient, and Data analysis tool. This module uses Test #1 and #2 scores data to demonstrate Excel tools and builds on Module 2.

    2. Formulas
    To begin, enter the data you want to analyze.

    Excel 2007 Module 3

    To keep track of your calculations enter the names of the calculations you will compute. To begin to analyze the data, click on an empty cell. Choose the Formulas ribbon bar and click on Insert Function. A dialogue box will appear.

    3. Measure of Dispersion
    To find the minimum value in a data series use the function =MIN and for the maximum value =MAX. To find the range of the data subtract the minimum value from the maximum. In this example with Test #1 you can use the equation "=B16-B15".

    Excel 2007 Module 3

    The interquartile range for Test #1 scores is obtained by simply subtracting the first quartile from the third quartile (found in Module 2) with the equation "=B14-B12".

    The function for variance is VAR. The standard deviation can be obtained by either using the standard deviation function STDEV or by taking the square root ("SQRT) of the variance.

    Excel 2007 Module 3

    3. Measures of Relationships between Variables


    Now we will move on to measuring the relationship between ratio level variables, in this case, the relationship between Test Scores #1 and Test Scores #2. Two numerical measures for relationships between ratio level variables are covariance and correlation. To calculate the covariance, use the COVAR function. For this function, you need to specify both arrays (ranges of data), first the data for Test #1 Score and then the data for Test #2 Score.

    The result is 24.27. Given that the number is positive we know that the two variables are positively related to each other. Excel only calculates the population covariance (divide by N) whereas we want the sample covariance (divide by N-1). We can easily correct this by multiplying the Covariance by the sample size and dividing by the sample size less one. The equation for this is "=F2*10/9".

    Excel 2007 Module 3

    The corrected covariance is 26.96667. The covariance number is difficult to interpret precisely given that it depends on the units of the variables being examined. To compensate for these failings, we can calculate the correlation coefficient instead. The command for correlation coefficient is "CORREL." In this case the equation is: =CORREL(B2:B11,C2:C11)

    The result is 0.733244. Again, the number is positive and close to 1. This means that the variables are strongly positively correlated. Students who scored high on the first test tended to score high on second test.

    4. Using the "Data Analysis" Tool


    Excel 2007 comes with a data analysis option, which allows you to calculate a variety of descriptive statistics even faster. Choose the Data ribbon and click on data analysis icon on the far right hand side of the screen. In the dialogue box choose Descriptive Statistics, and click 'OK'.

    Excel 2007 Module 3

    A new dialogue box will appear. Enter the range of data you want to analyze. In this example start with Test #1 scores. Choose where you would like the generated data "output" to be placed (the default is on a new worksheet). Check the box for summary statistics and click 'OK'.

    From the statistics generated you can choose what statistics you want to work with.

    Excel 2007 Module 3

    You might also like