Problem
I. Retrieve the excel sheet needed for the material below:
This text file contains data on the bi-weekly salaries of employees. It also has the following data for each employee: (i) number of years employed, (ii) position within the company, (iii) education level, and (iv) gender (0=male, 1=female).
II. Make a new column RAISE. RAISE should be computed for each employee using the formula indicated in the table below, based on the education level of employee:
|
Education Level
|
RAISE
|
|
1
|
(Position+Years)*Salary/100
|
|
2
|
(Position+Years+Education Level)*Salary/100
|
|
3
|
(Position*Years)*Salary/100
|
|
4
|
(Position+Years+Education Level)*Salary/200
|
Note: You are required to write one formula with a nested IF statements to achieve this. Make sure Salary and RAISE are shown as currency with $ symbol.
III. Compute the average RAISE amount for all employees.
IV. Make a new column PERCENT RAISE. In this column, compute the raise for each employee as a percentage of his/her salary. Make sure this is displayed with a % symbol.
V. Compute the average PERCENT RAISE by Gender (on a separate sheet in the same Excel file). Draw a bar chart with Gender on the x-axis and the average PERCENT RAISE for each gender on the y-axis. You can accomplish this using Pivot tables or using SUMIF statements.
VI. Compute the total RAISE (not PERCENT RAISE) by Education Level (on a separate sheet in the same Excel file). Which education level has the highest total RAISE? Draw a line chart with Education Level on the x-axis and the total RAISE on the y-axis.
VII. To the original data, add a new column YEARSPOSITION. In this new column, for each employee, you need to display the following: Years-<<#years from column B>>-Positon-<>
For example, for row 2 in the spreadsheet with original data, it should display Years-6-Position-3.
VIII. Make a pivot table with Gender as Column, Education Level as Rows, and total salary for each combination. SHow the total salary and also the percent of total salary for each combination (as a percent of the grant total).
Attachment:- Excel-Sheet.rar