Exercise 1.7-4 Chapter Review: – Logic – Employee Analysis


You are the supervisor of an accounting department for a large corporation and need to evaluate your employees for possible promotions or for indicating if probationary action required. Above is a spreadsheet that you have created to keep track of all the related information including the name, salary, job level, retirement eligibility, performance review scores (1-worst to 5 best) for the past 3 years and the year of last promotion for each employee. You will be completing this worksheet by filling in the required formulas.

1.  Write an Excel formula to be put in cell I3, which can be copied down the column, to calculate the average review score for the past 3 years of the employee. Round this value to the nearest hundredth.

=ROUND(AVERAGE(E3:G3),2)

2.  Write an Excel formula in cell J3, which can be copied down the column, to determine whether this average review exceeds 4.0. The result should be a True/False Boolean value.

=I3>4

3.  Write an Excel formula in cell F18 to determine whether (TRUE/FALSE) all the employees’ average review scores for the past 3 years exceed 4.0.

=AND(J3:J15)

4.  Write an Excel formula in cell K3, which can be copied down the column, to determine whether (TRUE/FALSE) this employee is recommended for promotion this year. An employee will be recommended for promotion if they have a performance review score of over 4.0 in any of the past 3 years and have not been promoted anytime after 2005.

=AND(OR(E3>4, F3>4, G3>4), H3<=2005)

5.  Write an Excel formula in cell L3, which can be copied down the column, to determine whether (TRUE/FALSE) this employee should be placed on probation. An employee should be placed on probation if their performance review score for 2006 is lower than 2.0, or if their average performance review score is lower than 2.0.

=OR(G3<2,I3<2)

6.  Write an Excel formula in cell F19 to determine (TRUE/FALSE) if there are no employees eligible for retirement.

=NOT(OR(D3:D15))

7.  Write an Excel formula in cell K16, which can be copied across to cell L16, to calculate the total number of employees that will be recommended for promotion or probation respectively.

=COUNTIF(K3:K15,TRUE)

8.  Write an Excel formula in cell F20 to calculate the total salary for all employees combined, whose job level is 6.

=SUMIF(C3:C15,6,B3:B15)