I need help with the formulas.

H32 XV fx A B 1 Average Rating by Dept. 2 3 Accounting 4 Customer Service 5 Human Resource 6 Information Technology 7 Underwriting 8 7. Write a formula in cell 'Salary Averages by Department'\B3, which can be copied across to cell 'Salary Averages by Department'ID3, and down to 'Salary Averages by DepartmentIDZ to calculate the average salary by department and by salary range. (Do not copy the formula to column E as you will need another formula for that column.) The salary ranges are as follows. $30,000 - $44,999.99 $45,000 - $54,999.99 $55,000 - $ 64,999.99 > = $65,000 Because there will be multiple criteria for this formula, you must use the AVERAGEIFS function. The arguments for this function will be as follows. Also, use the IFERROR function to return 0 if a department doesn't have salaries within a range. If you don't use the IFERROR function, Excel will return error messages. (Use cell references for all ranges and criterion.) If you use cell references and the correct $ Average Range: Employee's salary Range to anchor the cell references, you will be able to copy this formula over and down to Range1: Employee's salary Range Criterial: >=30000 obtain the correct values. Range2: Employee's salary range Criteria 2: = $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: Employee's salary Range1: Employee's salary Range Criteria1: >= 65000 Range2: Departments Range Criterio2: Department 9 Save the file and upload it to SimNet, E2 4 x vfx 65000 F 1 . B C D E 1 Average Slary by Department and Salary range 2 $ 30,000.00 $ 45,000.00 $ 55,000.00 $ 65,000.00 3 Accounting Customer Service 5 Human Resource 6 Information Technology 7 Underwriting 8 4 800 BI U - Merge & Center % ) 00 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Xfx Rating Scale: Poor: 4 Fair: 3 Good:2 Excellent: 1 F G H 1 J L M N 0 Question 4 1 3 1 1 Question 5 2 3 3 2 2 Question 7 2 1 Question 6 3 4 2 3 3 1 1 1 -- 1 3 Question 9 4 3 4 4 2 2 Question 3 2 3 1 1 - 1 2 2 a 3 1 4 2 2 2 a 3 4 2 2 - 1 4 1 1 2 2 2 2 Question 8 4 4 1 1 2 4 a 3 3 3 3 3 3 3 3 3 3 2 2 1 3 3 3 3 1 1 1 - 2 3 - 1 1 A $78,764 4 1 A B D E Rating Scale: Poor: 4 Fair: 3 Good:2 Excellent: 1 Employee # Department Salary Question 1 Question 2 42534 Human Resources $36,905 3 1 59031 Customer Service $42,531 2 3 3 59044 Human Resources $74,078 4 4 4 52098 Information Technology 1 $47,758 1 3 Arad 46520 Underwriting $76,679 $76 679 1 - 4 56714 Accounting $66,647 4 2 3 9 31533 Accounting 5 $36,700 3 2 4 3 3 10 56831 Accounting 1 2 2 11 57441 Human Resources $75,940 1 1 3 12 46184 Accounting $71,723 1 1 1 wa 13 53170 Information Technology $37,038 3 3 4 14 52348 Accounting $35.804 1 1 15 31286 Customer Service $54,583 1 3 16 50128 Underwriting $67,099 3 4 17 49701 Customer Service $44,594 2 3 18 49163 Information Technology $77,092 4 3 19 50149 Underwriting $46,447 1 1 2 20 35485 Human Resources $57,068 3 3 21 32595 Customer Service $71,354 2 2 22 54127 Customer Service $66,717 $ 4 4 4 23 24 25 26 27 28 4 3 3 1 2 2 2 2 3 3 3 2 2 4 2 2 2 2 2 4 4 Question 10 Question 11 Question ! 3 4 4 3 2 2 2 2 3 3 4 4 4 4 2 3 2 4 3 3 4 1 1 4 1 2 2 1 4 1 1 1 1 2 4 + 1 1 3 1 4 4 4 1 - 3 1 2 4 1 4 4 4. 1 1 1 3 . 4 1 3 3 1 1 3 1 3 2 2 4. 4 2 1 2 1 2 3 4 1 1 4 2 1 2 4 3 4 3 1 4 3 3 1 3 1 1 4 2 3 4 4 1 2 4 3 4 1 4 1 4 3 2 3 3 1 3 3 3 1 4 2 4 3 3 4 3 3 4 2 3 3 1 1 2 2 4 4 2. 4 1 3 4 4 4 4 4 1 3 1 2 3 1 2 2 3 Individual Responses Cumulative Responses Average Rating by Departmen Salary Averages by Department + Ready H 120% 3. Write a formula in cell 'Cumulative Responses'lc3, to calculate the number of "poor" responses for Question 1 based on the responses in the 'Individual Responses' worksheet. Copy this formula over and down to calculate the "fair", "good", and "excellent" responses for Question 1-11, respectively. (You only need one formula to accomplish this task.) (Make sure you use $ to anchor the cell references when necessary.) H32 XV fx A B 1 Average Rating by Dept. 2 3 Accounting 4 Customer Service 5 Human Resource 6 Information Technology 7 Underwriting 8 7. Write a formula in cell 'Salary Averages by Department'\B3, which can be copied across to cell 'Salary Averages by Department'ID3, and down to 'Salary Averages by DepartmentIDZ to calculate the average salary by department and by salary range. (Do not copy the formula to column E as you will need another formula for that column.) The salary ranges are as follows. $30,000 - $44,999.99 $45,000 - $54,999.99 $55,000 - $ 64,999.99 > = $65,000 Because there will be multiple criteria for this formula, you must use the AVERAGEIFS function. The arguments for this function will be as follows. Also, use the IFERROR function to return 0 if a department doesn't have salaries within a range. If you don't use the IFERROR function, Excel will return error messages. (Use cell references for all ranges and criterion.) If you use cell references and the correct $ Average Range: Employee's salary Range to anchor the cell references, you will be able to copy this formula over and down to Range1: Employee's salary Range Criterial: >=30000 obtain the correct values. Range2: Employee's salary range Criteria 2: = $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: Employee's salary Range1: Employee's salary Range Criteria1: >= 65000 Range2: Departments Range Criterio2: Department 9 Save the file and upload it to SimNet, E2 4 x vfx 65000 F 1 . B C D E 1 Average Slary by Department and Salary range 2 $ 30,000.00 $ 45,000.00 $ 55,000.00 $ 65,000.00 3 Accounting Customer Service 5 Human Resource 6 Information Technology 7 Underwriting 8 4 800 BI U - Merge & Center % ) 00 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Xfx Rating Scale: Poor: 4 Fair: 3 Good:2 Excellent: 1 F G H 1 J L M N 0 Question 4 1 3 1 1 Question 5 2 3 3 2 2 Question 7 2 1 Question 6 3 4 2 3 3 1 1 1 -- 1 3 Question 9 4 3 4 4 2 2 Question 3 2 3 1 1 - 1 2 2 a 3 1 4 2 2 2 a 3 4 2 2 - 1 4 1 1 2 2 2 2 Question 8 4 4 1 1 2 4 a 3 3 3 3 3 3 3 3 3 3 2 2 1 3 3 3 3 1 1 1 - 2 3 - 1 1 A $78,764 4 1 A B D E Rating Scale: Poor: 4 Fair: 3 Good:2 Excellent: 1 Employee # Department Salary Question 1 Question 2 42534 Human Resources $36,905 3 1 59031 Customer Service $42,531 2 3 3 59044 Human Resources $74,078 4 4 4 52098 Information Technology 1 $47,758 1 3 Arad 46520 Underwriting $76,679 $76 679 1 - 4 56714 Accounting $66,647 4 2 3 9 31533 Accounting 5 $36,700 3 2 4 3 3 10 56831 Accounting 1 2 2 11 57441 Human Resources $75,940 1 1 3 12 46184 Accounting $71,723 1 1 1 wa 13 53170 Information Technology $37,038 3 3 4 14 52348 Accounting $35.804 1 1 15 31286 Customer Service $54,583 1 3 16 50128 Underwriting $67,099 3 4 17 49701 Customer Service $44,594 2 3 18 49163 Information Technology $77,092 4 3 19 50149 Underwriting $46,447 1 1 2 20 35485 Human Resources $57,068 3 3 21 32595 Customer Service $71,354 2 2 22 54127 Customer Service $66,717 $ 4 4 4 23 24 25 26 27 28 4 3 3 1 2 2 2 2 3 3 3 2 2 4 2 2 2 2 2 4 4 Question 10 Question 11 Question ! 3 4 4 3 2 2 2 2 3 3 4 4 4 4 2 3 2 4 3 3 4 1 1 4 1 2 2 1 4 1 1 1 1 2 4 + 1 1 3 1 4 4 4 1 - 3 1 2 4 1 4 4 4. 1 1 1 3 . 4 1 3 3 1 1 3 1 3 2 2 4. 4 2 1 2 1 2 3 4 1 1 4 2 1 2 4 3 4 3 1 4 3 3 1 3 1 1 4 2 3 4 4 1 2 4 3 4 1 4 1 4 3 2 3 3 1 3 3 3 1 4 2 4 3 3 4 3 3 4 2 3 3 1 1 2 2 4 4 2. 4 1 3 4 4 4 4 4 1 3 1 2 3 1 2 2 3 Individual Responses Cumulative Responses Average Rating by Departmen Salary Averages by Department + Ready H 120% 3. Write a formula in cell 'Cumulative Responses'lc3, to calculate the number of "poor" responses for Question 1 based on the responses in the 'Individual Responses' worksheet. Copy this formula over and down to calculate the "fair", "good", and "excellent" responses for Question 1-11, respectively. (You only need one formula to accomplish this task.) (Make sure you use $ to anchor the cell references when necessary.)