A. Use the XLMiner Analysis ToolPak to finddescriptive statistics for Sample 1 and Sample 2.Select "Descriptive Statistics" in the ToolPak, placeyour cursor in the "Input Range" box, and thenselect the cell range A1 to B16 in the sheet. Next,place your cursor in the Output Range box andthen click cell D1 (or just type D1). Finally make sure"Grouped By Columns" is selected and all othercheck-boxes are selected. Click OK. Yourdescriptive statistics should now fill the shadedregion of D1:G18. Use your output to fill in theblanks 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 tofind covariance and correlation.In cell J3, find the covariance between Sample 1and Sample 2 using the COVARIANCE.S function.(2 decimals)In cell J5, find the correlation between Sample 1and Sample 2 using the CORREL function.(2 decimals)In cell J7, find the correlation between Sample 1and Sample 2 algebraically, cov/ (sx*sy), byconstructing a formula using other cells that arenecessary for the calculation.(2 decimals)Use the XLMiner Analysis ToolPak to find thecorrelation between Sample 1 and Sample 2. Placeyour output in cell 110.(2 decimals)C. Calculate z-scores using a mix of relative andabsolute cell references. In cell A22, insert theformula =ROUND((A2-$E$3)/$E$7,2). Next grabthe lower-right corner of A22 and drag down to fillin the remaining green cells of A23 to A36. Notehow the formula changes by looking in Column D.Changing a cell from a relative reference such asE3 to an absolute reference such as $E$3 meansthat cell remains "fixed" as you drag. Therefore the formula you entered into A22 takes each dataobservation such as A2, A3, A4..., subtracts $E$3and then divides by $E$7. Since the last two cellshave absolute references they will not change asyou drag. The ROUND function simply rounds the-score to two digits.Now find the z-scores for Sample 2 using the samemethod you learned above by editing the formulato refer to the correct cells for Sample 2. Makesure each z-score is rounded to 2 places.12345678910.11.12.13.14.15.

Excel template - Saved V Search (Option + Q) File Home Insert Draw Page Layout Formulas Data Review View Help Editing Comments Queries Workbook Links IIll Stocks Text to Columns Flash Fill y Remove Duplicates E Data Validation 22 - X V fx A B C E F G H J K L Sample 1 Sample 2 Delete this text then place your Descriptive Statistics output in cell D1 Covariance and Coorelation Formulas 72 74 39 24 Round all answers in the sheet to 2 decimal places using ROUND or the number formatting tool. Covariance (COVARIANCE.S) #N/A 55 65 66 61 Correlation (CORREL) #N/A 52 45 69 65 Correlation Calculation #N/A 38 48 51 63 Correlation from ToolPak 72 87 Delete this text then place your correlation output here 46 47 50 35 72 86 62 69 66 65 Scatter Plot of Raw Data 43 44 100 90 80 20 Sample 1 Sample 2 Sample 1 z-score Formulas Sample 2 z-score Formulas 70 . . z-scores z-scores 60 22 #N/A #N/A 50 23 #N/A #N/A 24 #N/A #N/A 40 25 #N/A #N/A 30 26 #N/A #N/A 20 27 #N/A #N/A 10 28 #N/A #N/A 0 29 #N/A #N/A 0 10 20 30 40 50 60 70 80 30 #N/A #N/A 31 #N/A #N/A 32 #N/A #N/A Scatter Plot of z-scores 33 #NIA #N/A #NIA #NIA 1.2 34 Sheet1 + Give Feedback to Microsoft - 100% +