Open the attached Excel sheet Employee_data.xls [+] and copy and paste the following information to the bottom of the sheet.

ID Gender bDate educ Jobcat salary salbegin jobtime prevexp minority
475 f 10-Feb-1972 15 1 $103000 $55,000 80 36 0
476 m 20-Jul-1965 16 2 $148000 $70,000 45 100 1

Watch the two instructional videos for this problem linked here . Video on Averageifs [+] and Pivot table video [+]

Answer the following questions. Give answers to the nearest cent. 

You can get your answers by using the Averageif formula and check your answers by making a Pivot table and chart.

Copy and paste your answers from the Excel sheet.

1) What is the average salary for just Females?  

2) What is the average salary for just females that have 14 or more years of education?  

3) What is the average salary for just Males that have 16 or more years of education and have a Minority level of 1?  

4) What is the average salary for just females that have 15 or more years of education and a jobtime of at least 80?  

 

For these problems, you will have to make a Pivot table or chart. If you used a pivot table to do the problems above, make sure you do not have any filtering left over from previous problems.

5) What is the LOWEST level of education in which the salary for males with a minority value of 1 is higher than the salary of males with a minority level of 0?

6) Using the education level you got as the answer to problem 5, what is the average salary of males with a minority level of 1?  

7) Using the education level you got as the correct answer to problem 5, what is the average salary of males with a minority level of 0?  

 

 

License