1. Write a formula in cell E4 that uses a nested IF to set the cell value based on sales for 2018:

High if sales are >=1500000

Moderate if sales are >= 1000000 but

Copy the formula down col E.

Low for all others

2. Write a formula in cell F4 that uses a vlookup with the table in cells I4 through J6 to set the cell value based on sales for 2018:

High if sales are >=1500000

Moderate if sales are >=1000000 but

Low for all others

Copy the formula down col F.

3. In cell H7, write a formula to determine how many salespeople had high sales in 2018.
4. In cell H8, write a formula to determine how many salespeople from the Northeast Territory had high sales in 2018.
5. In cell H9, write a formula to determine the total sales for the West territory for 2018.
6. In cell H10, write a formula to determine how many total employees from the Boston and Chicago offices had low sales.
7. In cell H11, write a formula to calculate the average sales per sales rep for 2018.

9. In cell H12, write a formula to calculate the average sales for reps from the West territory for 2018.

10. In cell H13, write a formula to determine which Region had lower sales for 2018, West or Northeast.

11. In cell H14, write a formula to determine how many salespeoples IDs begin with S12.

12. Enter an employee id in cell G17. In cell H17, write a formula to determine which sales region the employee from G17 is assigned to.

3 alesperso's Iales Office Citiales Territor 2018 sal Sales CategorySales Category | Northeast $101111 $101112 $101113 $101114 Nework Nework New York Los Angele: 600000 Northeast 1000000 Northeast 1500000 750000 west $101115Los Angeles west 925000 3$10116Los kngdles Los Angeles wect 600000 101 S101117 Los Angeles w cst 600000 11 $101118 Los Angcles west 1000000 12 $101113 Los Angeles Wcst 1500000 13 $101120 Bocton Northeast 750000 S: 101121 $101122 101123 $101124 $101125 Northesct925000 Northeast Northcast 600000 Northeast 100 Midwest 14 Boston Boston Boston Boston Bocton 600000 16 Er 0000 400000 3 lesperson's lales office Cit ales Territor 2018 sal Sales CategorySales Categor Sales A Sales Category 101111 New York Northest600000 New York Nortest 1000000 Northeazt 1500000 750000 0 LOW 1000000 MODERATE $101113New York S10114 Los AngeleaWest 1500000 HIGH High Sales for 2018 High Sales for 2018 Northeast Territory) Totat Sales for 2018 for West territory ow Sales ftrom Boston and Chicago Offices Average sales for all sales reps 2018 Average sales fr West territory sales reps Which Region had lower sales for 2018, west or NortheastP Sales Ds beginning with S12 $101115Los Angeles Weot 325000 9 $10116 Loz Angeles west600000 10 $10117Los ngelsc west 600000 11$101115Los Angeles West1000000 12 $101113 Los Angeles West1500000 $101120 Boston Northeast 750000 14 is 16 $101121 101122 $101123 Boston Boston Boston Boston Bosto Northoset Northeast 600000 Northesct 600000 Northoact 1000000 925000 Enter Sales ID in G17 Region 18101125 Midwest : |400000 Sheeti 3 alesperso's Iales Office Citiales Territor 2018 sal Sales CategorySales Category | Northeast $101111 $101112 $101113 $101114 Nework Nework New York Los Angele: 600000 Northeast 1000000 Northeast 1500000 750000 west $101115Los Angeles west 925000 3$10116Los kngdles Los Angeles wect 600000 101 S101117 Los Angeles w cst 600000 11 $101118 Los Angcles west 1000000 12 $101113 Los Angeles Wcst 1500000 13 $101120 Bocton Northeast 750000 S: 101121 $101122 101123 $101124 $101125 Northesct925000 Northeast Northcast 600000 Northeast 100 Midwest 14 Boston Boston Boston Boston Bocton 600000 16 Er 0000 400000 3 lesperson's lales office Cit ales Territor 2018 sal Sales CategorySales Categor Sales A Sales Category 101111 New York Northest600000 New York Nortest 1000000 Northeazt 1500000 750000 0 LOW 1000000 MODERATE $101113New York S10114 Los AngeleaWest 1500000 HIGH High Sales for 2018 High Sales for 2018 Northeast Territory) Totat Sales for 2018 for West territory ow Sales ftrom Boston and Chicago Offices Average sales for all sales reps 2018 Average sales fr West territory sales reps Which Region had lower sales for 2018, west or NortheastP Sales Ds beginning with S12 $101115Los Angeles Weot 325000 9 $10116 Loz Angeles west600000 10 $10117Los ngelsc west 600000 11$101115Los Angeles West1000000 12 $101113 Los Angeles West1500000 $101120 Boston Northeast 750000 14 is 16 $101121 101122 $101123 Boston Boston Boston Boston Bosto Northoset Northeast 600000 Northesct 600000 Northoact 1000000 925000 Enter Sales ID in G17 Region 18101125 Midwest : |400000 Sheeti