Home Insert Page Layout Formulas Data Review View XLSTAT365-Freemium Update Apple ID Settings Some account services will not be View & Cut Calibri (Body) v 12 AA de Wrap Text v General _ AutoSum available until you sign in again. [A Copy Fill Paste Conditional Format Cell Format Sort & Find & Solver Show Format BIUV - v Merge & Center $ ~ % " 00 -20 nsert Delete Formatting as Table Styles Clear v Filter Select ToolPak Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes x View Only Your account standear@udmercy.edu doesn't allow editing on a Mac. To learn more, contact your admin about your Office plan. Your recent changes were saved. Do you want Use Another Account to continue working where you left off? Q6 4 X V fx D G H M N O P Q R S T U V Person Date of Admission GFR Gender Ht (inches) Wt (Ibs) CKD Status Relative Status Lab 5: T-Tests 9,636 4/24/16 103.09 O 62.4 131.89 1 Low The goal of this lab is to demonstrate how to perform an independent samples T-test in Excel. 3084 10/14/16 93.97 58.33 148.98 1 Low The data to the left is the same sample we've used previously. OH 7,125 5/16/16 105.72 68.14 119.71 1 LOW 7,687 4/26/16 49.67 o 66.31 151.98 3A Low Let's say we're interested in testing whether there is a difference in weight between men and women. 1409 3/21/16 57.48 72.93 158.53 3A Low There are multiple ways of testing this in Excel. The first way is more elegant but more difficult. - OH 2044 10/20/16 77.86 63.12 128.46 2 Low This way requires the use of some database functions that fall outside the scope of this course but are shown below if you're interested. 7,245 12/13/16 105.3 64.69 146.67 1 Low You can perform a T-Test like this if it's very important that the dataset remain intact how it is. 7,667 5/16/16 56.25 65.57 165.03 3A Low 6,351 1/11/16 95.81 67.64 113.18 1 Low 5,580 2/29/16 54.85 58.76 151.08 3A Low Gender Count Avg Wt St Dev 3775 12/25/16 67.08 72.4 219.39 2 Low 49 129.80 13.7020 9,410 11/14/16 114.25 64.72 154.31 1 Low Gender Count Avg Wt t Dev 4875 11/4/16 58.92 61.26 112.55 3A Low 51 177.96 26.6825 2896 4/23/16 114.41 65.49 217.18 1 Low 916 4/30/16 24.09 O O O H H HOO HOO H HOP 61.84 116.74 4 Hig Null Hypothesis 3721 11/8/16 45.88 63.01 155.06 3A Low Avg Wt Female = Avg Wt Male 9,672 5/28/16 83.72 66.62 162.92 2 Low 9,210 7/26/16 97.89 66.09 201.29 1 Low Pooled St Dev 4.27 6,264 5/31/16 118.54 56.13 167.69 1 Low T Statistic 11.28 8, 441 8/25/16 79.11 52.78 134.52 2 Low p Value 0.0000 3510 12/29/16 90.3 68.46 121.13 1 Low 8,995 3/4/16 78.07 67.45 124.4 2 Low The second method requires a little more data manipulation but gets us to the answer a little more easily. 3891 5/8/16 93.79 73.38 200.77 1 Low To perform a T-test in this manner, first separate the variable of interest into two arrays that you'd like to test, then use the Excel function TTEST. 4763 10/21/16 55.69 64.6 211.88 3A Low 6,953 2/14/16 25.43 72.42 162.15 4 High Excel Function 0.0000 0 1 8 ,129 4/7/16 47.54 OOHHHH 73.05 165.73 3A Low 131.89 148.98 3987 8/3/16 98.61 66.09 145.79 1 Low 119.71 158.53 Can you perform a test that could be used to test the null hypothesis: 3341 5/11/16 93.53 64.13 108.64 1 Low 151.98 146.67 Avg Ht Female = Avg Ht Male 1394 8/24/16 63.5 66.56 169.02 2 Low 165.03 3174 12/9/16 30.84 70.09 170.62 3B Low 113.18 151.08 We've setup the more elegant test below, but expect you 7,282 10/13/16 66.37 69.85 157.11 2 Low 154.31 219.39 to separate height into 2 arrays and write the excel function to test 5,658 1/31/16 80.03 58.43 216.29 2 Low 112.55 217.18 in the highlighted cell below 2928 8/20/16 23.46 70.6 135.23 4 High 116.74 162.92 2569 4/25/16 66.11 72.23 202.55 2 Low 155.06 201.29 Gender Count Avg Ht St Dev 5,514 7/25/16 47.76 73.34 161.93 3A Low 134.52 167.69 0 49 64.62 2.3093 1609 10/12/16 90.54 64.39 140.8 1 Low 121.13 200.77 Gender Count Avg Ht St Dev 2474 12/2/16 114.4 70.67 187.58 1 Low 124.4 211.88 1 51 69.55 2.9163 9,306 10/13/16 112.51 66. 118.3 1 Low 162.15 OHHOOOHOHHH 145.79 5,717 1/30/16 31.0 54.41 125.2 2 Low 108.64 165.73 Pooled St Dev 0.53 2346 12/20/16 28.58 61.57 118.74 4 High 140.8 169.02 T Statistic 9.34 1453 6/25/16 87.51 73.53 156.03 2 Low 118.3 170.62 P Value 0.0000 5,59 6/12/16 101.65 67.44 218.04 1 Low 125.2 157.11 Excel Function 2656 9/30/16 39.45 62.95 126.3 3B Low 118.74 216.29 4347 1/31/16 8.3 $3.52 108.3 5 High 126.3 135.23 Additional Lab Questions:Home Insert Page Layout Formulas Data Review View XLSTAT365-Freemium Update Apple ID Settings Some account services will not be View & Cut Calibri (Body) v 12 AA ab Wrap Text v General Auto Sum available until you sign in again. [E Copy Fill v Paste BIUV vAv Merge & Center v $ ~ % 9 Conditional Format Cell Insert Delete Format Sort & Clear v Find & Solver Show Format Formatting as Table Styles Filter Select ToolPak Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No View Only Your account standear@udmercy.edu doesn't allow editing on a Mac. To learn more, contact your admin about your Office plan. Use Another Account Q6 + X V fx A B C D E F G H K M N O P Q R S T U V Person Date of Admission GFR Gender Ht (inches) Wt (lbs) CKD Status Relative Status Lab 5: T-Tests 4347 1/31/16 8.3 0 63.52 108.3 5 High 126.3 135.23 Additional Lab Questions: 7,283 9/16/16 65.37 68.63 143.32 2 Low 108.3 202.5 4034 10/2/16 15.06 54.71 197.72 1 Low 115.68 161.93 1. How many men and women were in this sample? 2720 /11/16 85.42 71.06 170.28 2 Low 134.86 187.58 1040 5/27/16 97.62 65.26 115.68 1 Lov 139.2 156.03 12. What was the mean height of each gender? 3598 7/24/16 66.37 62.45 134.86 2 Low 141.01 218.04 7,143 12/11/16 09.6 65.39 205.91 1 Lov 136.46 143.3 3. All things considered, are these means very far apart? 1331 5/25/16 54.31 66.06 176 3A Low 118.67 197.72 2641 1/3/16 6.6 68.29 211.68 2 Low 157.26 170.28 14. Based on your response to #4, would you expect a t-test to have 2049 9/16/16 38.65 57.1 139.2 2 Low 153.47 105.9 sufficient evidence to say these two means are different 5,641 6/19/16 36.1 69.63 180.08 3B Low 123.8 176 7,380 9/23/16 20.46 51.47 141.01 4 High 115.14 211.68 5. What is the two-tailed p-value associated with this test? (round to the closest 100th) 3296 6/9/16 5.8 O 51.32 36.46 4 High 139.86 180.08 8,762 5/22/16 15.33 69.52 136.75 3A Low 108.32 136.75 16. Is there sufficient evidence to suggest that there is a statistically significant difference 2202 7/7/16 95.86 :5.0 118.67 1 Low 144.48 176.78 ibetween the two means in question?. 9,947 8/21/16 99.41 58.98 157.26 1 Lov 115 224.9 1226 11/7/16 59.93 64.95 153.47 3A Low 135.18 151.17 2008 6/2/16 19.32 56.19 123.89 4 High 129.72 217.1 1712 8/28/16 113.19 61.95 115.14 1 Low 124.23 154.51 5,463 12/8/16 3.19 58.79 176.78 2 Low 121.8 15.1 2509 7/22/16 94.62 73.46 224.98 1 Low 140.4 190.57 7,463 7/2/16 85.6 73.22 151.17 2 Low 130.82 158.79 6,890 2/2/16 65.66 73.49 217.15 2 Low 138.48 144.57 4365 12/23/16 51.4 67.74 154.51 2 Low 148.25 140.55 7,141 9/18/16 72.65 66.12 215.14 2 Lov 120.2 201.85 8,196 1/13/16 86 $5.88 139.86 2 Low 113.41 162.56 1625 1/17/16 6.6 63.63 108.32 3B Lov 135.4 158.13 1700 /10/16 107.06 63.35 144.48 1 Low 145.83 208.6 2128 5/6/16 8.2 70. 190.57 3 Low 116.03 170.12 6,099 4/13/16 45.55 61.72 115 3A Low 124.8 153.58 5,980 12/15/16 82.27 65.52 135.18 2 Low 136.31 164.28 9,398 10/8/16 16.76 51.33 129.72 1 Low 217.5 10/10/16 30 64.92 24.23 3A Low 189.8 1876 11/4/16 31.25 67.63 121.82 3B Low 2275 7/27/16 110.3 $7.39 140.4 1 Low 1654 4/2/16 43.53 71.41 158.79 3B Lov 2267 3/20/16 53.15 72.1 144.57 3A Low 1957 9/4/16 76.55 7.0 140.55 2 Lov 198 8/4/16 12.04 1.7 130.82 1 Low 268 12/22/16 94.1 70.0 201.85 1 Low 5,517 4/21/16 53.53 71.1 162.56 3A Low 830 12/12/16 105.51 65.67 138.48 1 Low 1195 3/27/16 51.08 59.48 158.13 3A Low 8,966 3/7/16 43.82 64.91 148.25 3B Low O 6,683 6/19/16 75.39 8.16 120.2 2 Low Lab 5Home Insert Page Layout Formulas Data Review View XLSTAT365-Freemium Update Apple ID Settings Some account services will not be View & Cut Calibri (Body) v 12 A A E = ap Wrap Text v General AutoSum available until you sign in again. [ Copy v Fill v Paste BIUV OvAv Merge & Center v $ ~ % 00 Conditional Format Cell Insert Delete Sort & Find & Solver Show Format Formatting as Table Styles Clear v Filter Select ToolPak Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes N x View Only Your account standear@udmercy.edu doesn't allow editing on a Mac. To learn more, contact your admin about your Office plan. Use Another Account Q6 + XV J C D G H K L M N 0 P Q R S T U 1 Person Date of Admission GFR Gender Ht (inches) Wt (Ibs) CKD Status Relative Status Lab 5: T-Tests 66 7,463 7/2/16 85.6 73.22 151.17 2 Low 130.82 158.79 67 6,890 2/2/16 65.66 73.49 217.15 2 LOW 138.48 144.57 68 4365 12/23/16 61.48 $7.74 154.51 2 Low 148.25 140.55 69 7,141 9/18/16 72.65 66.12 215.14 2 LOW 120.2 201.85 70 8,196 1/13/16 86 O 65.88 139.86 2 Low 113.41 162.56 71 1625 1/17/16 36.63 63.63 108.32 3B Low 135.45 158.13 72 1700 4/10/16 107.06 lo 3.35 144.48 1 Low 145.83 208.6 73 2128 5/6/16 38.29 70.6 190.57 3B Low 116.03 70.12 OP 74 6,095 4/13/16 45.55 61.72 115 3A Low 124.86 153.58 75 5,980 12/15/16 82.27 o 65.52 135.18 2 Low 136.31 164.28 76 9,39 10/8/16 116.76 1.33 129.72 1 Low 217.55 o o 2603 10/10/16 50 64.92 24.23 3A Low 189.88 78 .87 11/4/16 31.25 o 67.63 121.82 3B Low 79 2275 7/27/16 110.3 O 67.39 140.4 1 Low 80 1654 4/2/16 43.53 71.41 158.79 3B Low 2267 8/20/16 53.15 72.1 144.57 3A Low 1957 9/4/16 76.55 67.02 140.55 2 Low 198 8/4/16 12.04 lo 61.77 130.82 1 Low 26 12/22/16 94.1 0.09 201.85 1 Low 85 5,517 4/21/16 53.53 71.13 162.56 3A Low 86 830 12/12/16 105.51 65.67 138.48 1 Low 1195 3/27/16 51.08 69.48 158.13 3A Low 88 8,966 3/7/16 43.82 64.91 148.25 3B Low 89 6,683 6/19/16 75.39 68.16 120.2 2 Low 90 5,487 10/6/16 99 P 72.03 208.6 1 Low 91 4261 10/1/16 80.26 72.28 170.12 2 Low 2117 5/11/16 31.11 61.31 113.41 38 Low o O 93 4819 11/8/16 61.32 68.0 135.45 2 Low 7,794 3/17/16 16.97 71.44 153.58 4 High OH 3954 2/28/16 9.7 65.9 145.83 5 High 6,690 1/20/16 93.14 O 53.49 116.03 1 Low 97 7,725 8/2/16 92.17 73.39 164.28 1 Low 2666 6/9/16 112.95 67.36 217.55 1 Low 99 5,75 10/11/16 82.65 53.29 124.86 2 Low 00 1981 4/16/16 101.03 69.2 189.88 1 Low 101 1601 12/6/16 85.16 66.75 136.31 2 Low 02 103 104 05