IN EXCEL SPREADSHEET ATTACHED

For the four clusters identified in, find the average and standard deviations of each numerical variable for the schools in each cluster and compare them with the averages and standard deviations for the entire data set. Does the clustering show distinct differences among the clusters?

For the Colleges and Universities data, use XLMiner to find four clusters using each of the other clustering methods; compare the results with

10.8 School Amherst Barnard Bates Berkeley Bowdoin Brown Bryn Mawr Cal Tech Carleton Carnegie Mellon Claremont McKenna Colby Colgate Columbia Cornell Davisdson Duke Georgetown Grinnell Hamilton Harvard Haverford Johns Hopkins Middlebury MIT Mount Holyoke Northwestern Oberlin Occidental Pomona Princeton Rice Smith Stanford Swarthnore U Michigan U of Chicago U of Rochester U Pennsylvania U Va UCLA UNC Vassar Type Median SAT Acceptance Rate Lib Arts 1315 22% Lib Arts 1220 53% Lib Arts 1240 36% University 1176 37% Lib Arts 1300 24% University 1281 24% Lib Arts 1255 56% University 1400 31% Lib Arts 1300 40% University 1225 64% Lib Arts 1260 36% Lib Arts 1200 46% Lib Arts 1258 38% University 1268 29% University 1280 30% Lib Arts 1230 36% University 1310 25% University 1278 24% Lib Arts 1244 67% Lib Arts 1215 38% University 1370 18% Lib Arts 1285 35% University 1290 48% Lib Arts 1255 25% University 1357 30% Lib Arts 1200 61% University 1230 47% Lib Arts 1247 54% Lib Arts 1170 49% Lib Arts 1320 33% University 1340 17% University 1327 24% Lib Arts 1195 57% University 1370 18% Lib Arts 1310 24% University 1195 60% University 1300 45% University 1155 56% University 1280 41% University 1218 37% University 1142 43% University 1109 32% Lib Arts 1287 43% Washington U (MO) Washinton and Lee Wellesley Wesleyan (CT) Williams Yale University Lib Arts Lib Arts Lib Arts Lib Arts University 1225 1234 1250 1290 1336 1350 54% 29% 49% 35% 28% 19% Expenditures/Student $ 26,636 $ 17,653 $ 17,554 $ 23,665 $ 25,703 $ 24,201 $ 18,847 $ 102,262 $ 15,904 $ 33,607 $ 20,377 $ 18,872 $ 17,520 $ 45,879 $ 37,137 $ 17,721 $ 39,504 $ 23,115 $ 22,301 $ 20,722 $ 46,918 $ 19,418 $ 45,460 $ 24,718 $ 56,766 $ 23,358 $ 28,851 $ 23,591 $ 20,192 $ 26,668 $ 48,123 $ 26,730 $ 25,271 $ 61,921 $ 27,487 $ 21,853 $ 38,937 $ 38,597 $ 30,882 $ 19,365 $ 26,859 $ 19,684 $ 20,179 From Example 10.6 Top 10% HS Graduation % 85 93 69 80 58 88 95 68 78 90 80 90 70 84 98 75 75 80 52 77 68 74 52 84 61 85 78 90 85 83 77 89 91 91 79 89 65 73 51 85 90 90 71 87 69 86 65 92 95 86 47 83 77 82 64 77 54 72 79 80 89 93 85 88 65 87 92 88 78 88 71 77 74 73 52 73 87 86 77 88 96 61 82 73 53 84 Cluster Id 1 2 2 3 1 1 2 4 1 2 2 2 2 1 1 1 1 1 2 2 1 1 2 1 1 2 2 2 2 1 1 1 2 1 1 2 2 2 1 1 3 3 2 $ $ $ $ $ $ 39,883 17,998 27,879 19,948 23,772 52,468 71 61 76 73 86 90 76 78 86 91 93 93 2 2 2 1 1 1 10.9 School Amherst Barnard Bates Berkeley Bowdoin Brown Bryn Mawr Cal Tech Carleton Carnegie Mellon Claremont McKenna Colby Colgate Columbia Cornell Davisdson Duke Georgetown Grinnell Hamilton Harvard Haverford Johns Hopkins Middlebury MIT Mount Holyoke Northwestern Oberlin Occidental Pomona Princeton Rice Smith Stanford Swarthnore U Michigan U of Chicago U of Rochester U Pennsylvania U Va UCLA UNC Vassar Washington U (MO) Washinton and Lee Wellesley Wesleyan (CT) Type Median SAT Acceptance Rate Lib Arts 1315 22% Lib Arts 1220 53% Lib Arts 1240 36% University 1176 37% Lib Arts 1300 24% University 1281 24% Lib Arts 1255 56% University 1400 31% Lib Arts 1300 40% University 1225 64% Lib Arts 1260 36% Lib Arts 1200 46% Lib Arts 1258 38% University 1268 29% University 1280 30% Lib Arts 1230 36% University 1310 25% University 1278 24% Lib Arts 1244 67% Lib Arts 1215 38% University 1370 18% Lib Arts 1285 35% University 1290 48% Lib Arts 1255 25% University 1357 30% Lib Arts 1200 61% University 1230 47% Lib Arts 1247 54% Lib Arts 1170 49% Lib Arts 1320 33% University 1340 17% University 1327 24% Lib Arts 1195 57% University 1370 18% Lib Arts 1310 24% University 1195 60% University 1300 45% University 1155 56% University 1280 41% University 1218 37% University 1142 43% University 1109 32% Lib Arts 1287 43% University 1225 54% Lib Arts 1234 29% Lib Arts 1250 49% Lib Arts 1290 35% Williams Yale Lib Arts University 1336 1350 28% 19% Expenditures/Student Top 10% HS Graduation % $ 26,636 85 93 $ 17,653 69 80 $ 17,554 58 88 $ 23,665 95 68 $ 25,703 78 90 $ 24,201 80 90 $ 18,847 70 84 $ 102,262 98 75 $ 15,904 75 80 $ 33,607 52 77 $ 20,377 68 74 $ 18,872 52 84 $ 17,520 61 85 $ 45,879 78 90 $ 37,137 85 83 $ 17,721 77 89 $ 39,504 91 91 $ 23,115 79 89 $ 22,301 65 73 $ 20,722 51 85 $ 46,918 90 90 $ 19,418 71 87 $ 45,460 69 86 $ 24,718 65 92 $ 56,766 95 86 $ 23,358 47 83 $ 28,851 77 82 $ 23,591 64 77 $ 20,192 54 72 $ 26,668 79 80 $ 48,123 89 93 $ 26,730 85 88 $ 25,271 65 87 $ 61,921 92 88 $ 27,487 78 88 $ 21,853 71 77 $ 38,937 74 73 $ 38,597 52 73 $ 30,882 87 86 $ 19,365 77 88 $ 26,859 96 61 $ 19,684 82 73 $ 20,179 53 84 $ 39,883 71 76 $ 17,998 61 78 $ 27,879 76 86 $ 19,948 73 91 Example 10.6: Average Group Linkage Cluster Id 1 2 2 3 1 1 2 4 1 2 2 2 2 1 1 1 1 1 2 2 1 1 2 1 1 2 2 2 2 1 1 1 2 1 1 2 2 2 1 1 3 3 2 2 2 2 1 $ $ 23,772 52,468 86 90 93 93 1 1 Complete Average Single Ward's Linkage Linkage Linkage method Cluster Id Cluster Id Cluster Id Cluster Id Colleges and Universities School Amherst Barnard Bates Berkeley Bowdoin Brown Bryn Mawr Cal Tech Carleton Carnegie Mellon Claremont McKenna Colby Colgate Columbia Cornell Davisdson Duke Georgetown Grinnell Hamilton Harvard Haverford Johns Hopkins Middlebury MIT Mount Holyoke Northwestern Oberlin Occidental Pomona Princeton Rice Smith Stanford Swarthnore U Michigan U of Chicago U of Rochester U Pennsylvania U Va UCLA UNC Vassar Washington U (MO) Washinton and Lee Wellesley Wesleyan (CT) Williams Yale Type Median SAT Acceptance Rate Expenditures/Student Lib Arts 1315 22% $ 26,636 Lib Arts 1220 53% $ 17,653 Lib Arts 1240 36% $ 17,554 University 1176 37% $ 23,665 Lib Arts 1300 24% $ 25,703 University 1281 24% $ 24,201 Lib Arts 1255 56% $ 18,847 University 1400 31% $ 102,262 Lib Arts 1300 40% $ 15,904 University 1225 64% $ 33,607 Lib Arts 1260 36% $ 20,377 Lib Arts 1200 46% $ 18,872 Lib Arts 1258 38% $ 17,520 University 1268 29% $ 45,879 University 1280 30% $ 37,137 Lib Arts 1230 36% $ 17,721 University 1310 25% $ 39,504 University 1278 24% $ 23,115 Lib Arts 1244 67% $ 22,301 Lib Arts 1215 38% $ 20,722 University 1370 18% $ 46,918 Lib Arts 1285 35% $ 19,418 University 1290 48% $ 45,460 Lib Arts 1255 25% $ 24,718 University 1357 30% $ 56,766 Lib Arts 1200 61% $ 23,358 University 1230 47% $ 28,851 Lib Arts 1247 54% $ 23,591 Lib Arts 1170 49% $ 20,192 Lib Arts 1320 33% $ 26,668 University 1340 17% $ 48,123 University 1327 24% $ 26,730 Lib Arts 1195 57% $ 25,271 University 1370 18% $ 61,921 Lib Arts 1310 24% $ 27,487 University 1195 60% $ 21,853 University 1300 45% $ 38,937 University 1155 56% $ 38,597 University 1280 41% $ 30,882 University 1218 37% $ 19,365 University 1142 43% $ 26,859 University 1109 32% $ 19,684 Lib Arts 1287 43% $ 20,179 University 1225 54% $ 39,883 Lib Arts 1234 29% $ 17,998 Lib Arts 1250 49% $ 27,879 Lib Arts 1290 35% $ 19,948 Lib Arts 1336 28% $ 23,772 University 1350 19% $ 52,468 Top 10% HS Graduation % 85 93 69 80 58 88 95 68 78 90 80 90 70 84 98 75 75 80 52 77 68 74 52 84 61 85 78 90 85 83 77 89 91 91 79 89 65 73 51 85 90 90 71 87 69 86 65 92 95 86 47 83 77 82 64 77 54 72 79 80 89 93 85 88 65 87 92 88 78 88 71 77 74 73 52 73 87 86 77 88 96 61 82 73 53 84 71 76 61 78 76 86 73 91 86 93 90 93 Covariance Graduation % (X) Median SAT (Y) X - Mean(X) Y - Mean(Y) 93 1315 9.755 51.898 80 1220 -3.245 -43.102 88 1240 4.755 -23.102 68 1176 -15.245 -87.102 90 1300 6.755 36.898 90 1281 6.755 17.898 84 1255 0.755 -8.102 75 1400 -8.245 136.898 80 1300 -3.245 36.898 77 1225 -6.245 -38.102 74 1260 -9.245 -3.102 84 1200 0.755 -63.102 85 1258 1.755 -5.102 90 1268 6.755 4.898 83 1280 -0.245 16.898 89 1230 5.755 -33.102 91 1310 7.755 46.898 89 1278 5.755 14.898 73 1244 -10.245 -19.102 85 1215 1.755 -48.102 90 1370 6.755 106.898 87 1285 3.755 21.898 86 1290 2.755 26.898 92 1255 8.755 -8.102 86 1357 2.755 93.898 83 1200 -0.245 -63.102 82 1230 -1.245 -33.102 77 1247 -6.245 -16.102 72 1170 -11.245 -93.102 80 1320 -3.245 56.898 93 1340 9.755 76.898 88 1327 4.755 63.898 87 1195 3.755 -68.102 88 1370 4.755 106.898 88 1310 4.755 46.898 77 1195 -6.245 -68.102 73 1300 -10.245 36.898 73 1155 -10.245 -108.102 86 1280 2.755 16.898 88 1218 4.755 -45.102 61 1142 -22.245 -121.102 73 1109 -10.245 -154.102 84 1287 0.755 23.898 76 1225 -7.245 -38.102 78 1234 -5.245 -29.102 86 1250 2.755 -13.102 91 1290 7.755 26.898 93 1336 9.755 72.898 93 1350 9.755 86.898 Mean 83.245 1263.102 Sum Count Covariance COVARIANCE.S (X - Mean(X))(Y-Mean(Y)) 506.2698875469 139.8617242816 -109.8525614327 1327.8617242816 249.2494793836 120.9025406081 -6.1178675552 -1128.7097042899 -119.7301124531 237.9433569346 28.6780508122 -47.6484798001 -8.9546022491 33.0862140775 -4.1382757185 -190.5056226572 363.6984589754 85.739275302 195.6984589754 -84.4239900042 722.1066222407 82.2290712203 74.1066222407 -70.9341940858 258.6984589754 15.4535610162 41.2086630571 100.5556018326 1046.9229487714 -184.6280716368 750.1474385673 303.8413161183 -255.7301124531 508.3107038734 223.0045814244 425.2902957101 -378.0158267389 1107.4943773428 46.5556018326 -214.4648063307 2693.9025406081 1578.7596834652 18.0453977509 276.0453977509 152.6372344856 -36.0974593919 208.5964181591 711.127030404 847.6984589754 12641.7755102041 49 263.3703231293 263.3703231293 Correlation Mean Sample Standard Deviation Graduation % (X) Median SAT (Y) X - Mean(X) Y - Mean(Y) 93 1315 9.755 51.898 80 1220 -3.245 -43.102 88 1240 4.755 -23.102 68 1176 -15.245 -87.102 90 1300 6.755 36.898 90 1281 6.755 17.898 84 1255 0.755 -8.102 75 1400 -8.245 136.898 80 1300 -3.245 36.898 77 1225 -6.245 -38.102 74 1260 -9.245 -3.102 84 1200 0.755 -63.102 85 1258 1.755 -5.102 90 1268 6.755 4.898 83 1280 -0.245 16.898 89 1230 5.755 -33.102 91 1310 7.755 46.898 89 1278 5.755 14.898 73 1244 -10.245 -19.102 85 1215 1.755 -48.102 90 1370 6.755 106.898 87 1285 3.755 21.898 86 1290 2.755 26.898 92 1255 8.755 -8.102 86 1357 2.755 93.898 83 1200 -0.245 -63.102 82 1230 -1.245 -33.102 77 1247 -6.245 -16.102 72 1170 -11.245 -93.102 80 1320 -3.245 56.898 93 1340 9.755 76.898 88 1327 4.755 63.898 87 1195 3.755 -68.102 88 1370 4.755 106.898 88 1310 4.755 46.898 77 1195 -6.245 -68.102 73 1300 -10.245 36.898 73 1155 -10.245 -108.102 86 1280 2.755 16.898 88 1218 4.755 -45.102 61 1142 -22.245 -121.102 73 1109 -10.245 -154.102 84 1287 0.755 23.898 76 1225 -7.245 -38.102 78 1234 -5.245 -29.102 86 1250 2.755 -13.102 91 1290 7.755 26.898 93 1336 9.755 72.898 93 1350 9.755 86.898 83.245 1263.102 Sum 7.449 62.676 Count Sample Covariance Sample Correlation CORREL Function Assuming Population Data: Population Standard Deviation Population Covariance Population Correlation 7.372 257.9954185756 0.5641468267 62.034 (X - Mean(X))(Y-Mean(Y)) 506.2698875469 139.8617242816 -109.8525614327 1327.8617242816 249.2494793836 120.9025406081 -6.1178675552 -1128.7097042899 -119.7301124531 237.9433569346 28.6780508122 -47.6484798001 -8.9546022491 33.0862140775 -4.1382757185 -190.5056226572 363.6984589754 85.739275302 195.6984589754 -84.4239900042 722.1066222407 82.2290712203 74.1066222407 -70.9341940858 258.6984589754 15.4535610162 41.2086630571 100.5556018326 1046.9229487714 -184.6280716368 750.1474385673 303.8413161183 -255.7301124531 508.3107038734 223.0045814244 425.2902957101 -378.0158267389 1107.4943773428 46.5556018326 -214.4648063307 2693.9025406081 1578.7596834652 18.0453977509 276.0453977509 152.6372344856 -36.0974593919 208.5964181591 711.127030404 847.6984589754 12641.7755102041 49 263.3703231293 0.5641468267 0.5641468267