Instructions: Complete the following questions using Microsoft Excel. You can use Excel at home, work, or the computer labs at Grand View. Make sure you use Excel formulas in your workbook, otherwise I will not be able to follow your work. Email me with any questions.
Due Date: This homework must be emailed to the professor prior to 8p on March 22nd.
1. Copy and paste the data below into an Excel workbook. Descriptions of the variables can be found here. Name this sheet “Data”.
Gender FSIQ VIQ PIQ Weight Height MRI_Count Female 133 132 124 118 64.5 816932 Male 140 150 124 125 72.5 1001121 Male 139 123 150 143 73.3 1038437 Male 133 129 128 172 68.8 965353 Female 137 132 134 147 65.0 951545 Female 99 90 110 146 69.0 928799 Female 138 136 131 138 64.5 991305 Female 92 90 98 175 66.0 854258 Male 89 93 84 134 66.3 904858 Male 133 114 147 172 68.8 955466 Female 132 129 124 118 64.5 833868 Male 141 150 128 151 70.0 1079549 Male 135 129 124 155 69.0 924059 Female 140 120 147 155 70.5 856472 Female 96 100 90 146 66.0 878897 Female 83 71 96 135 68.0 865363 Female 132 132 120 127 68.5 852244 Male 100 96 102 178 73.5 945088 Female 101 112 84 136 66.3 808020 Male 80 77 86 180 70.0 889083 Male 83 83 86 183 73.0 892420 Male 97 107 84 186 76.5 905940 Female 135 129 134 122 62.0 790619 Male 139 145 128 132 68.0 955003 Female 91 86 102 114 63.0 831772 Male 141 145 131 171 72.0 935494 Female 85 90 84 140 68.0 798612 Male 103 96 110 187 77.0 1062462 Female 77 83 72 106 63.0 793549 Female 130 126 124 159 66.5 866662 Female 133 126 132 127 62.5 857782 Male 144 145 137 191 67.0 949589 Male 103 96 110 192 75.5 997925 Male 90 96 86 181 69.0 879987 Female 83 90 81 143 66.5 834344 Female 133 129 128 153 66.5 948066 Male 140 150 124 144 70.5 949395 Female 88 86 94 139 64.5 893983 Male 81 90 74 148 74.0 930016 Male 89 91 89 179 75.5 935863
2. Show, using a PivotChart, the number of males and females in this data set. Display this in a seperate sheet, named “PivotChart”.
3. Create a scatterplot between VIQ and PIQ, set VIQ to the horizontal axis and PIQ on the vertical axis. Put this scatterplot in a new sheet titled “Scatterplot”
4. Create a histogram of weights, the bins should range from 100 to 200 in increments of 10. Place this histogram in a new sheet called “histogram”.
5. Calculate the mean, median, and mode brain size (MRI_Count). Calculate each using the built-in Excel functions. Perform these calculations in the “Data” sheet.
6. Calculate the standard deviation of brain size using the same method we learned in class. Assume this is data consists of the entire population. Perform this calculation in the “Data” sheet.
7. Copy and paste the data below into an Excel worksheet (same workbook as above) and title it “Age”.
Age,Frequency 15,12 16,24 17,41 18,74 19,271 20,278 21,203 22,152 23,167 24,163 25,188 26,186 27,219 28,207 29,233 30,219 31,253 32,241 33,241 34,199 35,229 36,232 37,205 38,274 39,303 40,280 41,269 42,256 43,266 44,286 45,275 46,320 47,291 48,306 49,335 50,358 51,323 52,351 53,356 54,379 55,365 56,389 57,340 58,356 59,356 60,294 61,297 62,288 63,292 64,181 65,153 66,122 67,122 68,96 69,71 70,68 71,49 72,37 73,34 74,31 75,18 76,17 77,18 78,12 79,7 80,12 81,8 82,5 83,3 84,3 85,2 86,2
8. Create a histogram for each age between 14 and 75. (e.g., there should be a bar for showing the frequency of each age). Place this graph in a sheet titled “Age-hist”
9. Calculate mean, median, mode, and standard deviation of age. (This is a tricky question, especially for mean and standard deviation. Refer back to the equations to derive a method to calculate this). Perform these calculations in the “Age” worksheet.
10. Delete any sheets not used in this homework. Please place the worksheets in this order: “Data”, “PivotChart”, “Scatterplot”, “histogram”, “Age”, “Age-hist”.
