Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need help analyzing and computing the data found in the attached spreadsheet. (3.0A1) B D E G H J K C Sample 1 Sample

I need help analyzing and computing the data found in the attached spreadsheet. (3.0A1)

image text in transcribedimage text in transcribedimage text in transcribed
B D E G H J K C Sample 1 Sample 2 Delete this text then place your Descriptive Statistics output in cell D1 Covariance and Coorelation Formulas 63 81 Round all answers in the sheet to 2 decimal places using ROUND or the number formatting tool. Covariance (COVARIANCE.S) #N/A 67 71 30 19 Correlation (CORREL) #N/A 30 40 74 79 Correlation Calculation #N/A 74 74 36 45 Correlation from ToolPak 30 31 Delete this text then place your correlation output here 66 55 28 32 39 26 41 29 57 65 Scatter Plot of Raw Data 51 45 90 80 18 19 70 20 Sample 1 Sample 2 Sample 1 z-score Formulas Sample 2 z-score Formulas 60 z-scores z-scores #N/A #N/A 50 23 #N/A #N/A 40 24 #N/A #N/A 30 25 #N/A #N/A 26 #N/A #N/A 20 #N/A #N/A 10 28 #N/A #N/A #N/A #N/A 0 10 20 30 40 50 60 70 80 #N/A #N/A #N/A #N/A #N/A #N/A Scatter Plot of z-scores #N/A #N/A 1.2 #N/A #N/A #N/A #N/A 1 #N/A #N/A 0.8 0.6 0.4 0.2 0 0.2 0.4 0.6 0.8 1 1.2If you would like to learn more about a specific function, you can always visit the Microsoft Ofce Suppo_rt site and type the function into the search box at the top-right of the page. Searching for "data analysis" will return a guide for using the Data Analysis Tool / XLMiner Analysis ToolPak. a. Use the XLMiner Analysis ToolPak to nd descriptive statistics for Sample 1 and Sample 2. Select "Descriptive Statistics" in the ToolPak, place your cursor in the "Input Range" box, and then select the cell range A1 to B16 in the sheet. Next, place your cursor in the Output Range box and then click cell 01 (orjust type D1). Finally make sure "Grouped By Columns" is selected and all other check-boxes are selected. Click OK. Your descriptive statistics should now fill the shaded region of D1:GlS. Use your output to ll in the blanks below. Sample 1 Mean: (2 decimals) Sample 1 Standard Deviation: (2 decimals) Sample 2 Mean: (2 decimals) Sample 2 Standard Deviation: (2 decimals) b. Use a combination of native Excel functions, constructed formulas, and the XLMiner ToolPak to nd covariance and correlation. In cell J3, find the covariance between Sample 1 and Sample 2 using the COVARIANCES function. (2 decimals) In cell J5, find the correlation between Sample 1 and Sample 2 using the CORREL function. (2 decimals) In cell J7, find the correlation between Sample 1 and Sample 2 algebraically, cov/(sx*sy), by constructing a formula using other cells that are necessary for the calculation. (2 decimals) Use the XLMiner Analysis ToolPak to nd the correlation between Sample 1 and Sample 2. Place your output in cell 110. (2 decimals) c. Calculate zscores using a mix of relative and absolute cell references. In cell A22, insert the formula =ROUND((A2-$E$3)/$E$7,2). Next grab the lower-right corner of A22 and drag down to ll in the remaining green cells of A23 to A36. Note how the formula changes by looking in Column D. Changing a cell from a relative reference such as E3 to an absolute reference such as $E$3 means that cell remains "fixed" as you drag. Therefore the formula you entered into A22 takes each data observation such as A2, A3, A4..., subtracts $E$3 and then divides by $E$7. Since the last two cells have absolute references they will not change as you drag. The ROUND function simply rounds the z-score to two digits. Now find the z-scores for Sample 2 using the same method you learned above by editing the formula to refer to the correct cells for Sample 2. Make sure each z-score is rounded to 2 places. sample 2 z-scores

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Mathematics For Liberal Arts Majors

Authors: Christopher Thomas

1st Edition

0071544305, 9780071544306

More Books

Students also viewed these Mathematics questions

Question

Discuss whether self-actualization should be everyones goal.

Answered: 1 week ago