Write a formula in cell Salary Averages by Department!E3, which can be copied down to Salary Averages by Department!E7, to calculate the average rating by
Write a formula in cell Salary Averages by Department!E3, which can be copied down to
Salary Averages by Department!E7, to calculate the average rating by department and
by salary range for all salaries >= $65,000.
Again, because there will be multiple criteria for this formula, you must use the AVERAGEIFS function, but
the arguments will not be the same as the previous formula. The arguments for this function will be as
follows.
(Use cell references for all ranges and criterion.)
Average Range: Employees salary
Range1: Employees salary Range
Criteria1: >= 65000
Range2: Departments Range
Criteria2: Department
B D E N 1 Average Salary by Department and Salary Range $ 30,000.00 $ 45,000.00 $ 55,000.00 $ 65,000.00 3 Accounting 4 Customer Service 5 Human Resources 6 Information Technology 7 Underwriting A B C D E G H J K L M N Question 8 Question 9 Question 10 Question 11 Question 12 4 4 3 4 4 3 3 2 2 4 4 4 1 2 3 3 3 3 2 1 2. 1 4 3 4 1 3 2 1 1 3 1 1 4 1 2 4 1 3 1 1 1 4 4 1 1 Rating Scale: Poor: 4 Fair: 3 Good: 2 Excellent: 1 2 Employee # Department Salary Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Question 3 42534 Human Resources $36,905 3 1 2 1 2 3 2 4 59031 Customer Service $42,531 2 3 3 3 3 2 1 5 59044 Human Resources $74,078 4 4 1 1 2 3 4 5 52098 Information Technology $47,758 1 3 1 2. 4 1 2 7 46520 Underwriting $76,679 1 4 2 3 1 1 1 3 56714 Accounting $66,647 4 3 3 4 2 3 3 2 31533 Accounting $36,700 2 3 1 3 2 2 3 LO 56831 Accounting $78,764 1 2 4 1 2 4 1 1 57441 Human Resources $75,940 1 3 2 2 4 2 1 2 46184 Accounting $71,723 1 1 1 2 3 2 4 3 53170 Information Technology $37,038 3 4 2 2 4 2 3 4 52348| Accounting $35,804 1 1 4 3 3 4 1 -5 31286 Customer Service $54,583 1 3 4 3 4 1 2 L6 50128 Underwriting $67,099 3 4 3 3 4 1 4 -7 49701 Customer Service $44,594 2 3 2 3 1 3 4 8 49163 Information Technology $77,092 4 3 3 4 3 1 2 9 50149 Underwriting $46,447 1 2 3 2. 1 1 4 CO 35485 Human Resources $57,068 3 3 1 3 2 3 4 1 32595 Customer Service $71,354 4 2 4 3 1 4 1 -2 54127 Customer Service $66,717 4 4 3 1 2 4 2 -3 3 4 3 1 2 1 4 1 4 1 4 2 4 4 1 4 3 1 1 3 1 4 4 1 3 3 3 1 3 1 2 2 3 4 3 4 2 4 2 2 3 4 1 2 1 3 4 2 3 4 2 3 1 4 2 B D E N 1 Average Salary by Department and Salary Range $ 30,000.00 $ 45,000.00 $ 55,000.00 $ 65,000.00 3 Accounting 4 Customer Service 5 Human Resources 6 Information Technology 7 Underwriting A B C D E G H J K L M N Question 8 Question 9 Question 10 Question 11 Question 12 4 4 3 4 4 3 3 2 2 4 4 4 1 2 3 3 3 3 2 1 2. 1 4 3 4 1 3 2 1 1 3 1 1 4 1 2 4 1 3 1 1 1 4 4 1 1 Rating Scale: Poor: 4 Fair: 3 Good: 2 Excellent: 1 2 Employee # Department Salary Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Question 3 42534 Human Resources $36,905 3 1 2 1 2 3 2 4 59031 Customer Service $42,531 2 3 3 3 3 2 1 5 59044 Human Resources $74,078 4 4 1 1 2 3 4 5 52098 Information Technology $47,758 1 3 1 2. 4 1 2 7 46520 Underwriting $76,679 1 4 2 3 1 1 1 3 56714 Accounting $66,647 4 3 3 4 2 3 3 2 31533 Accounting $36,700 2 3 1 3 2 2 3 LO 56831 Accounting $78,764 1 2 4 1 2 4 1 1 57441 Human Resources $75,940 1 3 2 2 4 2 1 2 46184 Accounting $71,723 1 1 1 2 3 2 4 3 53170 Information Technology $37,038 3 4 2 2 4 2 3 4 52348| Accounting $35,804 1 1 4 3 3 4 1 -5 31286 Customer Service $54,583 1 3 4 3 4 1 2 L6 50128 Underwriting $67,099 3 4 3 3 4 1 4 -7 49701 Customer Service $44,594 2 3 2 3 1 3 4 8 49163 Information Technology $77,092 4 3 3 4 3 1 2 9 50149 Underwriting $46,447 1 2 3 2. 1 1 4 CO 35485 Human Resources $57,068 3 3 1 3 2 3 4 1 32595 Customer Service $71,354 4 2 4 3 1 4 1 -2 54127 Customer Service $66,717 4 4 3 1 2 4 2 -3 3 4 3 1 2 1 4 1 4 1 4 2 4 4 1 4 3 1 1 3 1 4 4 1 3 3 3 1 3 1 2 2 3 4 3 4 2 4 2 2 3 4 1 2 1 3 4 2 3 4 2 3 1 4 2
