MGSC 1206 Introduction to Quantitative Methods II Fall 2020

Assignment #4

This assignment focuses on material from the end of Chapter 7 and the beginning of Chapter 9. In Chapter 8, various models for Classification were introduced, but it is not practical to try using any of them manually or in Excel. Nonetheless, the concepts and ideas behind these models are important to understand.

This assignment is based upon the student surveys from fall 2019 and 2020. You looked at this data in Assignment 2. In particular, you looked a pivot tables that involved percentages. This is material that could be interpreted as probabilities. We will revisit these analyses, but manually.

You also looked at average summer earnings and found some differences depending upon where students were from. We will look at this topic again, but using regression analysis with nominal variables. For the regression analysis, missing values interfere with the necessary calculations. Regression analysis does not permit missing values for any variable in the model. For this reason, I have cleaned the file by removing records for students who did not report earning or had earnings of zero. I have also removed or edited some records that had values that appeared to be invalid. The edited filed is called Survey 2019 and 2020 Assignment4.xlxs

You will observe that approximately 80 observations have been deleted because of missing, zero or invalid summer earnings.

In Assignment #2 we observed that summer earnings were lower for international students and highest for Nova Scotian students. Home was a nominal variable with many values. Even reducing it to 3 values (Nova Scotia, Canada, and International) makes it a new challenge to include in a regression model. Two new variables have been created.

Intl = 1 if the student is from outside Canada and 0 otherwise.

Canada = 1 if the student is Canadian but from outside Nova Scotia.

We had also observed that summer earnings appeared to be lower in 2020 than in 2019.

Class01 = 1 if the student is in the 2020 class and 0 if s/he was in the 2019 class.

Often when looking at earnings data, we observe that earnings are lower for females.

Gender01 = 1 if female and 0 if male.

Two numeric variables with few missing values were age and high school average. Older students are likely to have more work experience and thus may have higher earnings. Some may actually be working full time and studying part time.

Does high school average have any relationship with summer earnings? Dont know but chose to look and see.

1. In assignment 3, you chose the first variable by looking at the correlation between the outcome and the input variables. Several of the input variables are binary. Correlation measures linear relationships. Does it make sense to calculate correlations with binary variables? If you recall, our first model for classification had a binary outcome, but we still fit the model. What happens here?
   1. Calculate the correlations among the variables: HSAvg, Age, Intl, Class01, Gender01, Canada, Summer$. Copy and paste the table into your assignment.
   2. Make a scatter chart with Age on the horizontal axis and Summer$ on the vertical. Label the axes and give the chart a title. Copy the chart into your assignment.
   3. Comment on what variables appear to be related to summer earnings and specifically on the scatter chart.
   4. Fit the regression model to predict summer earnings based upon Age. Copy the regression tables into your assignment. (dont copy the residuals, just the tables).
   5. What does the coefficient for Age represent? Explain it in simple words.

2. The model in question 1 is not very good. Let us look at the residuals.
   1. Construct a Box and Whisker chart for the residuals. Copy the chart into your assignment.
   2. Go to the Design tab and select Select Data. Click on Edit for Horizontal Axis Labels. Select the values for Intl. Do not click on the label for Intl, just the values. You should get two Box and Whisker charts, one for 0 (Canadian) and another for 1 (international). Copy the new chart into your assignment. Warning! Excel may change your chart for 2a. If you are cutting and pasting revisions to charts, then when pasting, right click and select the 3^rd choice of pasting a Picture.
   3. The outliers make it difficult to compare the two charts. Click on any outlier and you should get all of them highlighted. Right click and select Format Data Series. Deselect Show outliers. Change the chart title to International =1. Copy the new chart into your assignment.
   4. In 2c you should have seen that summer earnings are lower (on average) for international students. What about the class of 2020 or females. Go back to the Design tab, Select Data and Edit, but change the selection to the values for Class01. Update the chart title to Class 2020 =1. Copy the new chart into your assignment.
   5. You should find that the class of 2020 has marginally lower earnings. If you were to look at Gender you will see a very very small difference as well. International appears to have the greatest impact. Refit your regression model with Age and International as your input (explanatory) variables. Copy the regression tables into your assignment.
   6. You should see a small increase in the R-square. The coefficient for Age will change slightly, but not too much considering its standard error. The intercept has changed to offset the impact of the new variable, International. Try to give an explanation of what the coefficient for International represents.

We could keep going with exploring this model. The standard error for the international coefficient is quite large ($934) suggesting that there is not a lot of precision to the coefficient estimate. However, given the size of the coefficient, we can confidently say that even if our estimate is off by a lot, it still suggests that the variable is important.

If we look at Class01 and Gender being added to the model, you will find that adding Class01 results in a very small improvement, but adding other variables increases the standard error (they do not add any useful information, just unnecessary complexity).

3. In question 6 on Assignment #2, you looked at the incidence of depression in the classes of 2019 and 2020. The frequency table is given below.

	2019	2020	Total
Frequent Depression	37	47	84
Once per Month	31	22	53
Infrequent Depression	66	43	109
Total	134	112	246

With such a small sample, any relative frequency probabilities we might estimate would be subject to a large amount of estimation error. Let us assume that our sample size is much much larger (say 246,000) and we can reasonably calculate probabilities from this table.

1. What is the probability that a randomly selected student experiences frequent depression?
2. What is the probability of randomly selection a student who is in the class of 2019 and experiences frequent depression?
3. What is the probability of randomly selecting a student who is in the class of 2019 or experiences frequent depression?
4. Among the students of the class of 2019, what is the chance that a student experiences frequent depression?
5. Among those students who experience frequent depression, what is the probability that the student is in the class of 2019?
6. Are the events being in the class of 2019 and experiencing frequent depression independent? Justify your answer using probability.

AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert A Calibri 11 A A = ab Wrap Text General WA DX Delete v Paste BIU : y - B Merge & Center $ %, Format Conditional Format as Cell Formatting Table Styles Styles Fil Clipboard Font Alignment Number Cells E H2 =VLOOKUP([@Class],lookup!$J$1:$K$2,2,FALSE) L M N o P G Intl 1 2 3 20 20 4 29 5 32 6 25 7 22 8 19 9 19 10 19 11 19 12 A B D E F Response ID Class Gender Gender Home Home HS Avg - Age 367 2020 Female South Asia 60 312 2020 Male Nova Scot 60 79 2019 Male Africa 60 398 2020 Male Nova Scot 60 208 2019 Female Nova Scot 65 188 2019 Male Caribbean 66 52 2019 Female Caribbean 70 74 2019 Male South Asia 70 105 2019 Male Nova Scot 70 85 2019 Male Nova Scot 70 114 2019 Male Nova Scot 70 386 2020 Male Other 70 129 2019 Female South Asic 70 35 2019 Male Other 70 49 2019 Male Nova Scot 70 138 2019 Male Nova Scot 70 379 2020 Female Nova Scot 70 186 2019 Male Nova Scot 70 198 2019 Female Caribbean 70 154 2019 Female Caribbean 70 146 2019 Male Other 70 102 2019 Male Canada ou 70 353 2020 Male Nova Scot 70 7010 Mala Nous Coat data lookup + 19 19 H K Class01 Gender Canada - Summe 1 1 1 0 400 0 1 0 0 5400 1 0 0 0 7000 0 1 0 0 500 0 0 O 1 0 3000 1 0 0 0 5000 1 0 1 0 1150 1 0 0 0 2500 0 0 0 0 0 3000 0 0 0 0 3500 0 0 0 0 4000 1 1 0 0 4000 1 0 1 0 5636 1 0 0 0 340 0 0 0 0 3200 0 0 0 0 8000 0 1 1 0 8000 0 0 0 0 3000 1 0 1 0 6900 1 0 1 0 20000 1 0 0 0 6000 0 0 0 1 18000 0 1 0 0 4000 2000n 13 14 19 20 15 16 20 17 20 18 20 19 21 20 21 21 21 22 22 23 23 24 26 17 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert Calibri 11 A A = ab Wrap Text General DX Delete Paste . : y E Merge & Center $ %, 8 Format Conditional Format as Cell Formatting Table Styles Styles F Clipboard Font Alignment Number Cells H2 X L M N o P 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Response ID Class Gender 146 2019 Male 102 2019 Male 353 2020 Male 132 2019 Male 11 2019 Female 88 2019 Male 195 2019 Male 192 2019 Male 125 2019 Male 190 2019 Male 324 2020 Male 135 2019 Male 384 2020 Male 140 2019 Male 182 2019 Female 337 2020 Male 4 2019 Male 90 2019 Female 89 2019 Male 189 2019 Male 91 2019 Male 401 2020 Female 370 2020 Female 218 2019 Male 2010 Consola data lookup =VLOOKUP([@Class],lookup!$J$1:$K$2,2,FALSE) Home HS Avg - Age Intl Class01 Gender - Canada - Summe Other 70 22 1 0 0 0 6000 Canada ou 70 23 0 0 0 1 18000 Nova Scot 70 26 0 1 0 0 4000 Nova Scot 70 27 0 0 0 0 0 30000 Nova Scot 29 0 0 1 0 7760 Nova Scot 70 33 0 0 0 0 50000 Nova Scot 74 19 0 0 0 0 8000 Nova Scot 75 19 0 0 0 o 2000 Nova Scot 75 19 0 0 0 0 3800 Canada ou 75 19 0 0 0 1 11000 Canada ou 75 21 1 0 1 500 Africa 75 21 1 0 0 0 8800 Nova Scot 75 21 0 1 0 0 0 17000 Africa 75 22 1 0 0 0 6000 Other 75 22 1 0 1 0 8500 Canada ou 75 22 0 1 0 1 8500 Africa 75 26 1 0 0 0 3500 Nova Scot 75 32 0 0 1 0 6000 Nova Scot 76 32 0 0 0 0 7000 Canada ou 78 19 0 0 0 1 8000 Nova Scot 78 20 0 0 0 0 7950 Nova Scot 78 22 0 1 1 1 0 7200 Africa 79 20 1 1 1 0 3500 Africa 79 22 1 0 0 0 5250 Caribbean + 36 37 38 39 40 41 42 43 44 45 AS AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert Calibri 11 A A = ab Wrap Text General DX Delete Paste BIU : y E Merge & Center .00 Format Conditional Format as Cell Formatting Table Styles Styles Fi Clipboard Font Alignment Number Cells E H2 X =VLOOKUP(I @Class],lookup !$J$1:$K$2,2,FALSE) Intl L M N o P 46 18 47 18 48 19 49 19 19 Class01 - Gender - Canada - Summe 1 0 1 0 2800 1 0 1 0 3000 0 1 1 1 2000 1 0 0 0 4000 1 1 0 0 4080 0 1 0 0 5000 1 0 1 0 2500 0 1 0 1 4000 0 0 0 0 5000 50 51 19 20 52 53 20 54 20 55 20 0 0 1 0 5000 5000 56 20 0 1 1 0 0 57 Home - HS Avg - Age Caribbean 80 Other 80 Canada ou 80 South Asic 80 Caribbean 80 Nova Scot 80 Other 80 Canada ou 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 China 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 China 80 Nova Scot 80 China 80 Africa 80 Nova Scot 80 Nova Scot 80 Africa 80 20 0 0 Response ID Class Gender 32 2019 Female 43 2019 Female 308 2020 Female 171 2019 Male 361 2020 Male 408 2020 Male 47 2019 Female 309 2020 Male 136 2019 Male 184 2019 Female 318 2020 Female 65 2019 Male 349 2020 Male 303 2020 Male 301 2020 Male 321 2020 Male 350 2020 Male 412 2020 Male 10 2019 Female 210 2019 Male 58 2019 Female 405 2020 Male 48 2019 Female 56 2019 Female nnn ramala 0 5500 6000 20 0 1 0 58 59 0 0 0 21 1 1 0 0 0 60 21 0 1 1 61 21 0 0 0 0 1000 2000 5000 5000 10000 62 21 0 0 1 1 63 21 1 0 0 64 21 0 0 1 0 13500 3000 65 1 0 0 22 22 0 0 66 1 0 5000 1 0 67 22 0 1 1 0 0 0 68 23 0 1 30000 3500 4000 69 23 1 0 1 0 70 2-10 26 10nn 1 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11 AA a-A- a Wrap Text EE E Merge & Center DX Delete Paste BIU y .00 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A E F G H 1 J L M N 0 P 1500 70 315 0 0 1 1 25 25 1 1 0 71 200 0 0 1 0 2000 7000 72 148 26 0 0 0 73 354 26 0 0 1 0 0 0 0 8000 20000 74 28 0 0 0 75 80 80 80 80 80 80 80 80 81 81 81 30 0 0 0 0 76 31 0 0 23 155 167 356 39 106 0 0 0 30000 5000 2000 5500 77 40 1 1 1 78 20 0 0 0 0 79 20 1 0 1 0 6000 80 205 21 0 0 1 4000 0 0 81 D Canada ou South Asia Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot China Nova Scot South Asia Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Canada ou Caribbean Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot China 82 22 0 0 0 B 2020 Female 2019 Male 2019 Male 2020 Male 2019 Male 2019 Male 2019 Male 2020 Female 2019 Male 2019 Female 2019 Female 2019 Male 2019 Male 2020 Female 2020 Male 2020 Male 2019 Male 2019 Female 2020 Male 2019 Female 2019 Female 2020 Male 2019 Male 2020 Male 2010 Mold 44 169 394 82 22 0 0 0 83 83 8000 9000 10000 83 26 0 0 0 0 1 1 0 84 374 83 26 1 0 0 19000 6000 330 83 37 0 1 0 85 86 87 219 19 0 0 O 0 84 85 0 1 0 4000 800 17 19 1 0 1 88 85 0 1 0 0 2800 19 19 89 85 0 1 0 352 164 202 390 0 0 3000 3000 90 85 19 0 1 1 0 91 85 19 0 0 0 1 1 0 4000 9655 92 172 85 19 0 0 0 93 385 85 20 1 1 0 0 1000 nd A Moun foot 25 n con AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11AA a-A- a Wrap Text EE E Merge & Center W DX Delete Paste BIU y $ % -9 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X E F G H | J L M N 0 P K 2500 94 85 20 0 0 0 0 0 0 0 20 0 1 1 0 3000 95 96 85 85 85 20 0 1 1 0 3000 142 333 364 396 119 162 34 97 20 1 1 0 0 1 0 0 98 85 1 20 20 99 85 0 0 1 0 0 0 1 0 100 20 0 0 0 85 85 3000 5000 5000 5500 10000 11000 6500 2000 101 81 20 0 0 1 1 102 64 85 20 0 0 1 165 85 20 0 0 1 0 163 85 21 1 0 1 1 0 1 85 21 1 0 4000 103 104 105 106 107 108 109 357 360 B 2019 Male 2020 Female 2020 Female 2020 Female 2019 Female 2019 Female 2019 Male 2019 Female 2019 Female 2019 Female 2019 Female 2020 Female 2020 Female 2020 Male 2019 Male 2020 Male 2020 Female 2020 Male 2019 Male 2019 Male 2019 Male 2020 Female 2019 Female 2019 Male 2010 Cobala lookup D Nova Scot Nova Scot Nova Scot Caribbean Nova Scot Nova Scot Canada ou Nova Scot Canada ou Nova Scot South Asia Other Nova Scot Nova Scot Caribbean Nova Scot Canada ou Other Canada ou Canada ou Nova Scot Nova Scot Caribbean Nova Scot Canada a 85 21 0 1 1 4000 373 85 21 1 0 0 1 6000 4000 197 85 22 0 0 0 0 0 0 1 0 319 85 22 0 1 0 85 22 0 1 1 331 413 4500 7000 1000 23 1 110 111 112 113 1 0 0 0 94 23 0 1 2000 & & & & 214 23 0 0 1 0 0 0 23 0 0 0 199 380 5000 12000 3500 4996 85 24 0 1 1 114 115 116 117 0 1 1 0 29 85 1 1 0 181 86 24 19 10 0 0 0 0 3000 100nn 07 -1 data AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11 AA a-A- a Wrap Text EE E Merge & Center DX Delete v Paste BIU y $ %, -20 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A E F H 1 J L M N o P 118 87 19 0 0 0 0 1 86 86 1 0 10000 300 7000 20 0 0 119 120 358 37 46 1 0 86 0 0 0 20 22 121 86 0 0 0 15000 87 19 1 0 1 7000 88 20 0 1 1 13 376 391 38 108 0 0 0 0 0 88 20 0 122 123 124 125 126 127 128 1 0 2000 6000 10000 2000 20 0 0 0 88 88 88 21 0 0 0 0 21 0 1 0 0 341 348 88 21 1 0 0 129 63 89 19 0 0 1 0 0 0 325 B 2019 Female 2020 Male 2019 Male 2019 Male 2019 Female 2020 Female 2020 Male 2019 Male 2019 Male 2020 Male 2020 Male 2019 Male 2020 Male 2019 Female 2020 Male 2019 Female 2020 Male 2020 Male 2019 Female 2019 Male 2019 Male 2020 Female 2019 Female 2020 Female 20 rossola lookup D Canada ou Nova Scot Nova Scot Nova Scot Caribbean Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Other Nova Scot Nova Scot China Nova Scot Nova Scot Nova Scot Canada OL Nova Scot South Asia Nova Scot Nova Scot Nova Scot Monocoat + 89 18 1 0 3500 7000 4000 3000 8800 2000 5000 20 89 19 0 0 0 0 0 1 399 89 20 1 1 0 59 20 0 0 1 0 89 89 338 20 0 1 130 131 132 133 134 135 136 137 138 139 140 0 0 5000 4500 381 89 21 0 0 0 1 0 180 90 19 0 1 1 4500 5000 50 90 19 0 0 0 0 213 90 19 1 0 0 0 7000 323 90 20 0 1 1 1 1 0 0 120 90 0 1 20 21 0 0 3000 10000 8000 141 388 90 1 1 0 inn 100 data File Home Insert Page Layout Formulas Data Review View Help X Insert Calibri 11 AA == Wrap Text General La DX Delete E Paste BIU a-A- EE 3 Merge & Center 00 .000 Format Y Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A D E F G I J K L M N O 392 90 21 0 0 10000 142 143 1 0 0 1 1 90 22 1 1 2100 124 112 389 0 0 0 144 90 22 0 0 0 0 1 4000 145 90 22 0 1 1 1 146 393 90 23 0 1 1 0 147 90 33 0 0 1 0 148 183 147 75 91 18 1 0 0 1 0 91 19 0 0 0 0 149 150 91 21 0 0 1 0 151 91 28 0 0 1 0 177 27 320 407 152 92 19 1 1 1 0 Nova Scot Other Nova Scot Canada ou Nova Scot Nova Scot Other Nova Scot Nova Scot Nova Scot South Asia Nova Scot Nova Scot South Asia Nova Scot Caribbean Nova Scot Nova Scot China Caribbean Nova Scot Nova Scot Nova Scot 8000 2000 20000 5000 7000 6000 10000 1500 5000 7000 2800 5540 1500 13000 9000 92 19 0 1 0 B 2020 Female 2019 Female 2019 Female 2020 Female 2020 Female 2019 Female 2019 Female 2019 Male 2019 Female 2019 Female 2020 Female 2020 Male 2020 Female 2019 Female 2020 Male 2019 Female 2020 Male 2020 Female 2019 Female 2019 Female 2019 Female 2020 Male 2019 Female 2020 Male non Mala 153 154 0 302 92 19 0 0 1 0 1 1 1 155 92 20 1 0 156 92 0 1 0 0 20 24 157 92 1 0 1 0 217 406 99 359 335 201 24 0 1 0 0 92 92 93 25 0 1 1 158 159 160 161 0 19 1 0 1 0 600 93 19 1 0 1 0 179 55 346 2700 11000 19 0 0 1 162 163 164 0 93 93 93 20 0 1 0 0 400 41 0 0 0 9408 21 19 1 0 310 Nova Scot 94 0 1 0 165 100 8000 nnnn 39 Monocoat MA -1 File Home Insert Page Layout Formulas Data Review View Help Calibri 11 ' ' General a Wrap Text + 53Merge & Center Paste A Y $ -% - Conditional Format as Formatting Table Sty Styles Clipboard Font Alignment Number M48 for A H j K L M 163 1 0 0 400 346 41 164 0 1 0 9408 310 0 0 332 20 1 1 0 0 0 0 8000 9000 4000 0 165 166 167 168 169 170 171 1 115 314 185 387 1 0 1 0 1 1 1 B 2020 Male 2019 Female 2020 Male 2020 Male 2019 Male 2020 Female 2019 Female 2020 Male 2020 Female 2019 Male 2019 Female 2019 Male 2019 Female 2020 Male 2020 Female 2019 Male 2019 Female 2019 Female 366 D E F Nova Scot 93 20 0 Nova Scot 93 21 0 Nova Scot 94 19 0 Nova Scot 94 0 Nova Scot 94 21 0 Other 95 19 1 Canada ou 95 22 0 Canada ou 95 23 0 Nova Scot 96 20 0 Nova Scot 96 20 0 Nova Scot 96 21 0 Canada ou 96 0 China 99 23 1 Nova Scot 82 24 0 Nova Scot 82 55 0 Nova Scot 82 20 0 Other 82 22 1 Canada ou 82 300 0 1 1 0 500 10000 23000 10000 15000 5000 8000 172 0 153 45 70 0 1 o o 173 0 174 21 0 0 1 175 137 0 1 0 200 176 1 0 0 1500 382 334 177 1 1 0 14000 0 0 178 191 0 0 0 6000 166 1 3000 179 180 O o 0 1 127 1 10000 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri 11 A A == General a Wrap Text EE E Merge & Center WE DX Delete Paste BIU MA y $ %) Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells F8 A B E F H K L M N O P J 2019 1 Male 0 0 0 0 1 0 2020 1 2 Female 3 1 0 1 D Nova Scot Canada ou Caribbean Africa South Asia China Other G Nova Scot Canada ou Caribbean Africa South Asia China Other 4 1 0 1 0 5 6 1 0 7 1 0 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 data lookup + AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert A Calibri 11 A A = ab Wrap Text General WA DX Delete v Paste BIU : y - B Merge & Center $ %, Format Conditional Format as Cell Formatting Table Styles Styles Fil Clipboard Font Alignment Number Cells E H2 =VLOOKUP([@Class],lookup!$J$1:$K$2,2,FALSE) L M N o P G Intl 1 2 3 20 20 4 29 5 32 6 25 7 22 8 19 9 19 10 19 11 19 12 A B D E F Response ID Class Gender Gender Home Home HS Avg - Age 367 2020 Female South Asia 60 312 2020 Male Nova Scot 60 79 2019 Male Africa 60 398 2020 Male Nova Scot 60 208 2019 Female Nova Scot 65 188 2019 Male Caribbean 66 52 2019 Female Caribbean 70 74 2019 Male South Asia 70 105 2019 Male Nova Scot 70 85 2019 Male Nova Scot 70 114 2019 Male Nova Scot 70 386 2020 Male Other 70 129 2019 Female South Asic 70 35 2019 Male Other 70 49 2019 Male Nova Scot 70 138 2019 Male Nova Scot 70 379 2020 Female Nova Scot 70 186 2019 Male Nova Scot 70 198 2019 Female Caribbean 70 154 2019 Female Caribbean 70 146 2019 Male Other 70 102 2019 Male Canada ou 70 353 2020 Male Nova Scot 70 7010 Mala Nous Coat data lookup + 19 19 H K Class01 Gender Canada - Summe 1 1 1 0 400 0 1 0 0 5400 1 0 0 0 7000 0 1 0 0 500 0 0 O 1 0 3000 1 0 0 0 5000 1 0 1 0 1150 1 0 0 0 2500 0 0 0 0 0 3000 0 0 0 0 3500 0 0 0 0 4000 1 1 0 0 4000 1 0 1 0 5636 1 0 0 0 340 0 0 0 0 3200 0 0 0 0 8000 0 1 1 0 8000 0 0 0 0 3000 1 0 1 0 6900 1 0 1 0 20000 1 0 0 0 6000 0 0 0 1 18000 0 1 0 0 4000 2000n 13 14 19 20 15 16 20 17 20 18 20 19 21 20 21 21 21 22 22 23 23 24 26 17 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert Calibri 11 A A = ab Wrap Text General DX Delete Paste . : y E Merge & Center $ %, 8 Format Conditional Format as Cell Formatting Table Styles Styles F Clipboard Font Alignment Number Cells H2 X L M N o P 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Response ID Class Gender 146 2019 Male 102 2019 Male 353 2020 Male 132 2019 Male 11 2019 Female 88 2019 Male 195 2019 Male 192 2019 Male 125 2019 Male 190 2019 Male 324 2020 Male 135 2019 Male 384 2020 Male 140 2019 Male 182 2019 Female 337 2020 Male 4 2019 Male 90 2019 Female 89 2019 Male 189 2019 Male 91 2019 Male 401 2020 Female 370 2020 Female 218 2019 Male 2010 Consola data lookup =VLOOKUP([@Class],lookup!$J$1:$K$2,2,FALSE) Home HS Avg - Age Intl Class01 Gender - Canada - Summe Other 70 22 1 0 0 0 6000 Canada ou 70 23 0 0 0 1 18000 Nova Scot 70 26 0 1 0 0 4000 Nova Scot 70 27 0 0 0 0 0 30000 Nova Scot 29 0 0 1 0 7760 Nova Scot 70 33 0 0 0 0 50000 Nova Scot 74 19 0 0 0 0 8000 Nova Scot 75 19 0 0 0 o 2000 Nova Scot 75 19 0 0 0 0 3800 Canada ou 75 19 0 0 0 1 11000 Canada ou 75 21 1 0 1 500 Africa 75 21 1 0 0 0 8800 Nova Scot 75 21 0 1 0 0 0 17000 Africa 75 22 1 0 0 0 6000 Other 75 22 1 0 1 0 8500 Canada ou 75 22 0 1 0 1 8500 Africa 75 26 1 0 0 0 3500 Nova Scot 75 32 0 0 1 0 6000 Nova Scot 76 32 0 0 0 0 7000 Canada ou 78 19 0 0 0 1 8000 Nova Scot 78 20 0 0 0 0 7950 Nova Scot 78 22 0 1 1 1 0 7200 Africa 79 20 1 1 1 0 3500 Africa 79 22 1 0 0 0 5250 Caribbean + 36 37 38 39 40 41 42 43 44 45 AS AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Table Design Insert Calibri 11 A A = ab Wrap Text General DX Delete Paste BIU : y E Merge & Center .00 Format Conditional Format as Cell Formatting Table Styles Styles Fi Clipboard Font Alignment Number Cells E H2 X =VLOOKUP(I @Class],lookup !$J$1:$K$2,2,FALSE) Intl L M N o P 46 18 47 18 48 19 49 19 19 Class01 - Gender - Canada - Summe 1 0 1 0 2800 1 0 1 0 3000 0 1 1 1 2000 1 0 0 0 4000 1 1 0 0 4080 0 1 0 0 5000 1 0 1 0 2500 0 1 0 1 4000 0 0 0 0 5000 50 51 19 20 52 53 20 54 20 55 20 0 0 1 0 5000 5000 56 20 0 1 1 0 0 57 Home - HS Avg - Age Caribbean 80 Other 80 Canada ou 80 South Asic 80 Caribbean 80 Nova Scot 80 Other 80 Canada ou 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 China 80 Nova Scot 80 Nova Scot 80 Nova Scot 80 China 80 Nova Scot 80 China 80 Africa 80 Nova Scot 80 Nova Scot 80 Africa 80 20 0 0 Response ID Class Gender 32 2019 Female 43 2019 Female 308 2020 Female 171 2019 Male 361 2020 Male 408 2020 Male 47 2019 Female 309 2020 Male 136 2019 Male 184 2019 Female 318 2020 Female 65 2019 Male 349 2020 Male 303 2020 Male 301 2020 Male 321 2020 Male 350 2020 Male 412 2020 Male 10 2019 Female 210 2019 Male 58 2019 Female 405 2020 Male 48 2019 Female 56 2019 Female nnn ramala 0 5500 6000 20 0 1 0 58 59 0 0 0 21 1 1 0 0 0 60 21 0 1 1 61 21 0 0 0 0 1000 2000 5000 5000 10000 62 21 0 0 1 1 63 21 1 0 0 64 21 0 0 1 0 13500 3000 65 1 0 0 22 22 0 0 66 1 0 5000 1 0 67 22 0 1 1 0 0 0 68 23 0 1 30000 3500 4000 69 23 1 0 1 0 70 2-10 26 10nn 1 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11 AA a-A- a Wrap Text EE E Merge & Center DX Delete Paste BIU y .00 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A E F G H 1 J L M N 0 P 1500 70 315 0 0 1 1 25 25 1 1 0 71 200 0 0 1 0 2000 7000 72 148 26 0 0 0 73 354 26 0 0 1 0 0 0 0 8000 20000 74 28 0 0 0 75 80 80 80 80 80 80 80 80 81 81 81 30 0 0 0 0 76 31 0 0 23 155 167 356 39 106 0 0 0 30000 5000 2000 5500 77 40 1 1 1 78 20 0 0 0 0 79 20 1 0 1 0 6000 80 205 21 0 0 1 4000 0 0 81 D Canada ou South Asia Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot China Nova Scot South Asia Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Canada ou Caribbean Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot China 82 22 0 0 0 B 2020 Female 2019 Male 2019 Male 2020 Male 2019 Male 2019 Male 2019 Male 2020 Female 2019 Male 2019 Female 2019 Female 2019 Male 2019 Male 2020 Female 2020 Male 2020 Male 2019 Male 2019 Female 2020 Male 2019 Female 2019 Female 2020 Male 2019 Male 2020 Male 2010 Mold 44 169 394 82 22 0 0 0 83 83 8000 9000 10000 83 26 0 0 0 0 1 1 0 84 374 83 26 1 0 0 19000 6000 330 83 37 0 1 0 85 86 87 219 19 0 0 O 0 84 85 0 1 0 4000 800 17 19 1 0 1 88 85 0 1 0 0 2800 19 19 89 85 0 1 0 352 164 202 390 0 0 3000 3000 90 85 19 0 1 1 0 91 85 19 0 0 0 1 1 0 4000 9655 92 172 85 19 0 0 0 93 385 85 20 1 1 0 0 1000 nd A Moun foot 25 n con AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11AA a-A- a Wrap Text EE E Merge & Center W DX Delete Paste BIU y $ % -9 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X E F G H | J L M N 0 P K 2500 94 85 20 0 0 0 0 0 0 0 20 0 1 1 0 3000 95 96 85 85 85 20 0 1 1 0 3000 142 333 364 396 119 162 34 97 20 1 1 0 0 1 0 0 98 85 1 20 20 99 85 0 0 1 0 0 0 1 0 100 20 0 0 0 85 85 3000 5000 5000 5500 10000 11000 6500 2000 101 81 20 0 0 1 1 102 64 85 20 0 0 1 165 85 20 0 0 1 0 163 85 21 1 0 1 1 0 1 85 21 1 0 4000 103 104 105 106 107 108 109 357 360 B 2019 Male 2020 Female 2020 Female 2020 Female 2019 Female 2019 Female 2019 Male 2019 Female 2019 Female 2019 Female 2019 Female 2020 Female 2020 Female 2020 Male 2019 Male 2020 Male 2020 Female 2020 Male 2019 Male 2019 Male 2019 Male 2020 Female 2019 Female 2019 Male 2010 Cobala lookup D Nova Scot Nova Scot Nova Scot Caribbean Nova Scot Nova Scot Canada ou Nova Scot Canada ou Nova Scot South Asia Other Nova Scot Nova Scot Caribbean Nova Scot Canada ou Other Canada ou Canada ou Nova Scot Nova Scot Caribbean Nova Scot Canada a 85 21 0 1 1 4000 373 85 21 1 0 0 1 6000 4000 197 85 22 0 0 0 0 0 0 1 0 319 85 22 0 1 0 85 22 0 1 1 331 413 4500 7000 1000 23 1 110 111 112 113 1 0 0 0 94 23 0 1 2000 & & & & 214 23 0 0 1 0 0 0 23 0 0 0 199 380 5000 12000 3500 4996 85 24 0 1 1 114 115 116 117 0 1 1 0 29 85 1 1 0 181 86 24 19 10 0 0 0 0 3000 100nn 07 -1 data AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri == General 11 AA a-A- a Wrap Text EE E Merge & Center DX Delete v Paste BIU y $ %, -20 Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A E F H 1 J L M N o P 118 87 19 0 0 0 0 1 86 86 1 0 10000 300 7000 20 0 0 119 120 358 37 46 1 0 86 0 0 0 20 22 121 86 0 0 0 15000 87 19 1 0 1 7000 88 20 0 1 1 13 376 391 38 108 0 0 0 0 0 88 20 0 122 123 124 125 126 127 128 1 0 2000 6000 10000 2000 20 0 0 0 88 88 88 21 0 0 0 0 21 0 1 0 0 341 348 88 21 1 0 0 129 63 89 19 0 0 1 0 0 0 325 B 2019 Female 2020 Male 2019 Male 2019 Male 2019 Female 2020 Female 2020 Male 2019 Male 2019 Male 2020 Male 2020 Male 2019 Male 2020 Male 2019 Female 2020 Male 2019 Female 2020 Male 2020 Male 2019 Female 2019 Male 2019 Male 2020 Female 2019 Female 2020 Female 20 rossola lookup D Canada ou Nova Scot Nova Scot Nova Scot Caribbean Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Nova Scot Other Nova Scot Nova Scot China Nova Scot Nova Scot Nova Scot Canada OL Nova Scot South Asia Nova Scot Nova Scot Nova Scot Monocoat + 89 18 1 0 3500 7000 4000 3000 8800 2000 5000 20 89 19 0 0 0 0 0 1 399 89 20 1 1 0 59 20 0 0 1 0 89 89 338 20 0 1 130 131 132 133 134 135 136 137 138 139 140 0 0 5000 4500 381 89 21 0 0 0 1 0 180 90 19 0 1 1 4500 5000 50 90 19 0 0 0 0 213 90 19 1 0 0 0 7000 323 90 20 0 1 1 1 1 0 0 120 90 0 1 20 21 0 0 3000 10000 8000 141 388 90 1 1 0 inn 100 data File Home Insert Page Layout Formulas Data Review View Help X Insert Calibri 11 AA == Wrap Text General La DX Delete E Paste BIU a-A- EE 3 Merge & Center 00 .000 Format Y Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells M48 X A D E F G I J K L M N O 392 90 21 0 0 10000 142 143 1 0 0 1 1 90 22 1 1 2100 124 112 389 0 0 0 144 90 22 0 0 0 0 1 4000 145 90 22 0 1 1 1 146 393 90 23 0 1 1 0 147 90 33 0 0 1 0 148 183 147 75 91 18 1 0 0 1 0 91 19 0 0 0 0 149 150 91 21 0 0 1 0 151 91 28 0 0 1 0 177 27 320 407 152 92 19 1 1 1 0 Nova Scot Other Nova Scot Canada ou Nova Scot Nova Scot Other Nova Scot Nova Scot Nova Scot South Asia Nova Scot Nova Scot South Asia Nova Scot Caribbean Nova Scot Nova Scot China Caribbean Nova Scot Nova Scot Nova Scot 8000 2000 20000 5000 7000 6000 10000 1500 5000 7000 2800 5540 1500 13000 9000 92 19 0 1 0 B 2020 Female 2019 Female 2019 Female 2020 Female 2020 Female 2019 Female 2019 Female 2019 Male 2019 Female 2019 Female 2020 Female 2020 Male 2020 Female 2019 Female 2020 Male 2019 Female 2020 Male 2020 Female 2019 Female 2019 Female 2019 Female 2020 Male 2019 Female 2020 Male non Mala 153 154 0 302 92 19 0 0 1 0 1 1 1 155 92 20 1 0 156 92 0 1 0 0 20 24 157 92 1 0 1 0 217 406 99 359 335 201 24 0 1 0 0 92 92 93 25 0 1 1 158 159 160 161 0 19 1 0 1 0 600 93 19 1 0 1 0 179 55 346 2700 11000 19 0 0 1 162 163 164 0 93 93 93 20 0 1 0 0 400 41 0 0 0 9408 21 19 1 0 310 Nova Scot 94 0 1 0 165 100 8000 nnnn 39 Monocoat MA -1 File Home Insert Page Layout Formulas Data Review View Help Calibri 11 ' ' General a Wrap Text + 53Merge & Center Paste A Y $ -% - Conditional Format as Formatting Table Sty Styles Clipboard Font Alignment Number M48 for A H j K L M 163 1 0 0 400 346 41 164 0 1 0 9408 310 0 0 332 20 1 1 0 0 0 0 8000 9000 4000 0 165 166 167 168 169 170 171 1 115 314 185 387 1 0 1 0 1 1 1 B 2020 Male 2019 Female 2020 Male 2020 Male 2019 Male 2020 Female 2019 Female 2020 Male 2020 Female 2019 Male 2019 Female 2019 Male 2019 Female 2020 Male 2020 Female 2019 Male 2019 Female 2019 Female 366 D E F Nova Scot 93 20 0 Nova Scot 93 21 0 Nova Scot 94 19 0 Nova Scot 94 0 Nova Scot 94 21 0 Other 95 19 1 Canada ou 95 22 0 Canada ou 95 23 0 Nova Scot 96 20 0 Nova Scot 96 20 0 Nova Scot 96 21 0 Canada ou 96 0 China 99 23 1 Nova Scot 82 24 0 Nova Scot 82 55 0 Nova Scot 82 20 0 Other 82 22 1 Canada ou 82 300 0 1 1 0 500 10000 23000 10000 15000 5000 8000 172 0 153 45 70 0 1 o o 173 0 174 21 0 0 1 175 137 0 1 0 200 176 1 0 0 1500 382 334 177 1 1 0 14000 0 0 178 191 0 0 0 6000 166 1 3000 179 180 O o 0 1 127 1 10000 AutoSave Off HD = Survey 2019 and 2020 Assignment4 - Excel Search File Home Insert Page Layout Formulas Data Review View Help Insert Calibri 11 A A == General a Wrap Text EE E Merge & Center WE DX Delete Paste BIU MA y $ %) Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells F8 A B E F H K L M N O P J 2019 1 Male 0 0 0 0 1 0 2020 1 2 Female 3 1 0 1 D Nova Scot Canada ou Caribbean Africa South Asia China Other G Nova Scot Canada ou Caribbean Africa South Asia China Other 4 1 0 1 0 5 6 1 0 7 1 0 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 data lookup +