Consider the data set we use to analyze the Chilean college admissions case. I added one more sheet that contains demographics for each student, including:

$$ DOB: date of birth

$$ Family size: number of people that live with the student

$$ How many in your family work: number of people that work within the family

$$ Income level: categorical variable representing monthly income, as detailed in the next

table:

Category $1$

$2$

$3$

$456$

$$

From To

$035035010451045175017502450245031253125$ more

Level education father$/$mother: categorical variable representing the maximum level of education attained by the parents, as detailed in the next table:

Category Highest Level of Education

$0$ No data

$1$ No education

$2$ Elementary incomplete

$3$ Elementary complete

$4$ Hish$-$school incomplete

$5$ High$-$school complete

$6$ Technical school incomplete

$7$ Technical school complete

$8$ University incomplete

$9$ University complete

$10$ Other

Descriptive$/$Diagnostic Analysis

$1.$ Separate the Demographics data in columns.

$2.$ Create a Table for Demographics.

$3.$ Filter observations in Demographics for which the information is incomplete or wrong $($describe

what filters you apply and why you think it is correct$).$

Page $1$ of $2$

$4.$ Compute the current age of each student using their DOB. $($Hint: the format of the date of birth is DMMYYYY$,$ where D may have one or two digits depending on the day; i$.$e$.,$ if the date of birth is less than $10,$ then D has only one digit, while it has two digits otherwise$).$

$5.$ Compute the median monthly income per family member considering the middle point of each income level $($for the highest income level, consider $$4000/$month$).$

$6.$ How is the median monthly income related to the level of education of the father and mother? $($Hint: create a two dimensional pivot table$).$

$7.$ What is the effect of the number of family members in the NEM score? $($Hint: paste the variable number of family members in the Scores table and create a pivot table$)$

$8.$ Compute the average between the Math $($MATE$)$ and Language $($LYC$)$ scores, and analyze the effect of the level of education of the mother. What do you see? What about the father? Which effect seems to be bigger?

$9.$ What is the effect of the level of income on test scores?

$10.$ What is the relation between high school type and level of income? in excel