DAY 43 CALCULATING CORRELATION COEFFICIENT USING EXCEL PROBLEM

DAY 43 – CALCULATING CORRELATION COEFFICIENT USING EXCEL

PROBLEM General Scenario: Dr. Truong wants to examine the relation between the amount of television that people watch and how happy they are. Ten adults participate in her study. Each participant records the total number of hours of TV that he or she watches for a particular week. In addition, at the end of the week, each participant completes a questionnaire that measures the person’s overall happiness. The questionnaire scores can range from 0 (extremely unhappy) to 10 (extremely happy). The results from Dr. Truong’s experiment are listed below.

TABLE Participant Number Hours of TV Watched Happiness Score

3 EXAMPLES Copy the information into your Excel Spreadsheet

Computing the Mean

COMPUTING MEAN, OR (AVERAGE INEXCEL) Click this On the Formulas tab, click “More Select. Functions” these cells by dragging it This should appear Select “Statistical”, then choose Press“AVERAGE” “Enter” key twice This would be your “mean” Select the cell where you want to put your mean

Computing the Median

COMPUTING MEDIAN Type in “=median(“ Select the numbers by dragging it Press “Enter” key This is your Median Select the cell where you want to put your median

Computing the Mode

COMPUTING MODE Type “=mode(“ Select the numbers by dragging it Press “Enter” This is your mode Select the cell where you want to put your mode

Computing the Correlation Coefficient

GETTING THE CORRELATION COEFFICIENT Compute the Pearson correlation coefficient between hours of TV watched and happiness (round to two decimal places). Type in “=CORREL(“ Select your array 1 , Put a comma “ ” Select your array 2 Press “Enter” There it is, a strong negative correlation coefficient. Don’t forget to round two decimal places. Select the cell

Let’s Create a Scatter Plot

CREATING A SCATTER PLOT ON THE DATA GIVEN Select the cells On Insert Tab, click Scatter Select “Scatter with only Markers”

On Layout Tab, click “Axis Titles” Click the primary horizontal axis title, select “Title Below Axis. ” Type “Hours of TV Watched” Click the primary vertical axis title, select “Vertical Title. ” Type “Happiness Score”

Drawing the Line of Best Fit

DRAW THE LINE OF BEST FIT Make sure you click on the graph Under the Layout Tab Click Tredline, then chose, “Linear Tredine”

THE LINE OF BEST FIT