80.2K

Verified Solution

Link Copied!

mple 1 Sample 2 Sample 1 Covariance and Coorelation Formulas Coverano (COVARIANCES) 109.7904762 71 71 COVARIANCES(A2A18B2016) Mean Standard Emo Median Mode Standard Devon ST 000067 Mean 3.460017430 Standard Error 58 Median 55.93333333 4.491750117 56 Correo (CORREL) CORELAZATGE2 B16) 43 51 1340201900andard Devotion 17304004 Correlation Coutin 77 -1.561011511 Kuros 01310 -1069622953 273000327 Correlation from Top letti a Ponce your correo output here 02 70 Range Mrum 53 Memum 35 Monum 75 Memum 75 Court 15 Count 75 Large Scatter Plot of Raw Data ) Confidence 14272017 Confidence Leve Sample 1 Sample 2 Sample 1-core Formulas Sample 2-score Formulas Scatter Plot of 2-scores Sample 1 Standard Deviation: 13.40 (2 decimals) Sample 2 Mean: 55.93 (2 decimals) Sample 2 Standard Deviation: 17.40 (2 decimals) b. Use a combination of native Excel functions, constructed formulas, and the XL Miner ToolPak to find covariance and correlation. In cell 33, find the covariance between Sample 1 and Sample 2 using the COVARIANCE.S function. 199.79 (2 decimals) In cell J5, find the correlation between Sample 1 and Sample 2 using the CORREL function. 0.85 g (2 decimals) In cell 7, 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. 0.86 % (2 decimals) Use the XL Miner Analysis ToolPak to find the correlation between Sample 1 and Sample 2. Place your output in cell 110. 0.86 (2 decimals) c. Calculate Z-scores 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 fill in the remaining green cells of AZ3 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 2-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. 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 1.88 0.95 0.49 -0.23 0.903 mple 1 Sample 2 Sample 1 Covariance and Coorelation Formulas Coverano (COVARIANCES) 109.7904762 71 71 COVARIANCES(A2A18B2016) Mean Standard Emo Median Mode Standard Devon ST 000067 Mean 3.460017430 Standard Error 58 Median 55.93333333 4.491750117 56 Correo (CORREL) CORELAZATGE2 B16) 43 51 1340201900andard Devotion 17304004 Correlation Coutin 77 -1.561011511 Kuros 01310 -1069622953 273000327 Correlation from Top letti a Ponce your correo output here 02 70 Range Mrum 53 Memum 35 Monum 75 Memum 75 Court 15 Count 75 Large Scatter Plot of Raw Data ) Confidence 14272017 Confidence Leve Sample 1 Sample 2 Sample 1-core Formulas Sample 2-score Formulas Scatter Plot of 2-scores Sample 1 Standard Deviation: 13.40 (2 decimals) Sample 2 Mean: 55.93 (2 decimals) Sample 2 Standard Deviation: 17.40 (2 decimals) b. Use a combination of native Excel functions, constructed formulas, and the XL Miner ToolPak to find covariance and correlation. In cell 33, find the covariance between Sample 1 and Sample 2 using the COVARIANCE.S function. 199.79 (2 decimals) In cell J5, find the correlation between Sample 1 and Sample 2 using the CORREL function. 0.85 g (2 decimals) In cell 7, 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. 0.86 % (2 decimals) Use the XL Miner Analysis ToolPak to find the correlation between Sample 1 and Sample 2. Place your output in cell 110. 0.86 (2 decimals) c. Calculate Z-scores 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 fill in the remaining green cells of AZ3 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 2-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. 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 1.88 0.95 0.49 -0.23 0.903

Answer & Explanation Solved by verified expert

See Answer