Exercise 11. Open a blank worksheet in Excel. Enter the First name, Last Name, Gender, Department, andYearly Salary (with 2 decimals) for 10 fictitious people in Columns A-E, Beginning in Row 2. Besure to enter column headings (Row 1) and align them with the data. Adjust column widths asneeded.2. Sort the data by Last Name.3. Insert a column before the name (new column A), and enter the Employee Number. Type onlythe first two numbers, then select both and copy down for autofill.4. In Column G, type the heading “Tax Withheld”, and enter a formula that computes it. Assume20% of the salary is withheld for taxes.5. The new tax code give a Tax Break of $2,000 for all those who have more than $20,000withheld. Use an IF statement to compute this in Column H. (If Tax Witheld is greater than$20000, then Tax Break is $2000; otherwise, Tax Break is $0.)6. Assume now that the Tax Break is more complex. If you had a least $10,000 withheld, you get$500 back. If you had at least $15,000 withheld, you get $1,000 back. If you had $20,000 ormore withheld, you get $2,000 back as before. Use a VLOOKUP statement to compute the NewTax Break in Column I.(Hint: if TAX WITHELD < $10000, then NEW TAX BREAK = $0;if $10000 <= TAX WITHELD < $15000, then NEW TAX BREAK = $500;if $15000 <= TAX WITHELD < $20000, then NEW TAX BREAK = $1000;if TAX WITHELD >= $20000, then NEW TAX BREAK = $2000Only put the lower bound of each range as the cutoff point on the VLOOKUP table. PleaseseeExcelExampleSolutionSteps.doc, Page 4, on Canvas.)7. Draw a bar graph showing the salaries for each person, along with their last name. Label axesappropriately.Exercise 2Continue with the spreadsheet from the above Exercise 1, and do the following1. Insert a new row right after all 10 row of data; use Excel formula to find the AVERAGE salary ofall persons2. Insert a new row right after average salary of all persons; use Excel formula to find the MEDIANsalary of all persons.3. Sort the entire dataset by Gender (do not include the averages in the sorting)4. Insert a new row right after median salary of all persons; use Excel formula to find the AVERAGEsalary of all females5. Insert a new row right after average salary of all females; use Excel formula to find the MEDIANsalary of all females6. Insert a new row right after median salary of all females; use Excel formula to find the AVERAGEsalary of all males7. Insert a new row right after average salary of all males; use Excel formula to find the MEDIANsalary of all males Show this eercise on excel sheet
Exercise 1
1. Open a blank worksheet in Excel. Enter the First name, Last Name, Gender, Department, and
Yearly Salary (with 2 decimals) for 10 fictitious people in Columns A-E, Beginning in Row 2. Be
sure to enter column headings (Row 1) and align them with the data. Adjust column widths as
needed.
2. Sort the data by Last Name.
3. Insert a column before the name (new column A), and enter the Employee Number. Type only
the first two numbers, then select both and copy down for autofill.
4. In Column G, type the heading “Tax Withheld”, and enter a formula that computes it. Assume
20% of the salary is withheld for taxes.
5. The new tax code give a Tax Break of $2,000 for all those who have more than $20,000
withheld. Use an IF statement to compute this in Column H. (If Tax Witheld is greater than
$20000, then Tax Break is $2000; otherwise, Tax Break is $0.)
6. Assume now that the Tax Break is more complex. If you had a least $10,000 withheld, you get
$500 back. If you had at least $15,000 withheld, you get $1,000 back. If you had $20,000 or
more withheld, you get $2,000 back as before. Use a VLOOKUP statement to compute the New
Tax Break in Column I.
(Hint: if TAX WITHELD < $10000, then NEW TAX BREAK = $0;
if $10000 <= TAX WITHELD < $15000, then NEW TAX BREAK = $500;
if $15000 <= TAX WITHELD < $20000, then NEW TAX BREAK = $1000;
if TAX WITHELD >= $20000, then NEW TAX BREAK = $2000
Only put the lower bound of each range as the cutoff point on the VLOOKUP table. Please
seeExcelExampleSolutionSteps.doc, Page 4, on Canvas.)
7. Draw a bar graph showing the salaries for each person, along with their last name. Label axes
appropriately.
Exercise 2
Continue with the spreadsheet from the above Exercise 1, and do the following
1. Insert a new row right after all 10 row of data; use Excel formula to find the AVERAGE salary of
all persons
2. Insert a new row right after average salary of all persons; use Excel formula to find the MEDIAN
salary of all persons.
3. Sort the entire dataset by Gender (do not include the averages in the sorting)
4. Insert a new row right after median salary of all persons; use Excel formula to find the AVERAGE
salary of all females
5. Insert a new row right after average salary of all females; use Excel formula to find the MEDIAN
salary of all females
6. Insert a new row right after median salary of all females; use Excel formula to find the AVERAGE
salary of all males
7. Insert a new row right after average salary of all males; use Excel formula to find the MEDIAN
salary of all males
Show this eercise on excel sheet
Step by step
Solved in 2 steps
