ONLY NEED HELP WITH PART F ONLY (PLEASE SHOW WORK USING FORMULAS AND FUNCTIONS ON EXCEL)

E Question 3 The table below shows the quarterly profits of 150 salespersons in three different regions (East, South, and West). Use the data to complete items below. I already created the cell ranges salesperson and profits for you. A. For each salesperson, calculate annual profits using quarterly profits. Use SUMIF function 9 10 8-Sort the data by annual profit in descending order and assign each salesman an overall rank, so that the salesman with the highest profit is ranked 1 and the second highest score is ranked 2, etc. (You could also use a RANKEQ function) 12 C-Use VLOOKUP to assign each salesperson to a quintile based on his or her rank, using the data in the Quintiles table. 13 D-Use a nested IF function to assign each salesperson to a quintile based on his or her rank. The results should match those obtained when completing part c. (Recall that you will need n-1 IF statements, where n=of categories). 16 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. 1.8 F. Use AVERAGEIFS to calculate the average annual profits of salespersons for a given quintile and region. Use AVERAGEIF to calculate the average annual profits of salespersons within a given quintile (Qtl 1, Qt1 2, etc.) 19 21 Quarterly Profits Region Salesperson Region Profits 24 Overall Rank Overall Quintile Overall Quintile INRANKEQ ICVLOOKUP (D) F 1 Qtl 1 2 3 25 West Ql1 Otl 1 26 West Otl 1 27 West Qu 1 Qul 1 Annual Profits Annual (A) SUMIF $36,936.00 $32,828.00 $32,444.00 $32.130.00 $31,479.00 $29,106.00 $28.539.00 $28,503.00 $28,350.00 $28.308.00 $28,191.00 28 West 4 Qil 1 Qtl 1 29 West 5 Otl 1 Otl 1 30 Wit Qu 1 Qul 1 I 31 7 West al 1 Ql1 32 West 8 Qtl 1 Otl 1 31 South Qu 1 Qul 1 34 South Otl 1 Otl 1 Qtl 1 Qtl 1 South South 37 West 35 35 9 10 11 12 13 14 Qul 1 Qul 1 Od 1 Ortl 1 Qtl 1 38 Wit Qtl 1 39 West 15 Qil 1 Qul 1 40 West 16 Otl 1 Crtl 1 41 17 Q 1 East Qul 1 $28,148.00 $27.865.00 $27,777.00 $27,397.00 $26.978.00 $26,861.00 $26,384.00 $26,070.00 $25,591.00 $25,313.00 $24,982.00 22 $24,826.00 23 $24,737.00 24 42 Qil 1 Qil 1 East 43 18 19 20 Otl 1 East Otl 1 44 Q1 East Qui 1 45 West 21 Qtl 1 Qil 1 46 West Otl 1 Otl 1 47 West Q1 Qul 1 45 West Q1 Qtl 1 Quarter 1 2 3 4 1 2 3 4 + 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 Salesperson Adsit, Donald Adit, Donald Adet, Denis Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris Arnols Farris Arnold, Farris Arnold, Farris Barkley, Jared Barkley, Jared Barkley, Jared Barkley, Jared Barry, Maly Barry, Molly Barry, Maly Barry, Maly Bexley, Griffin Baskey, Griff Beary, Griffin Bekley, Grimin $5.870.00 $6,542.00 $6,900.00 ($330.00) 8413.00 (5612.00) $6,523.00 $1,476.00 $7,360.00 Sa.co $7,569.00 $2.232.00 $1,290.00 ($198.00) $4,066.00 $7.063.00 $2,495.00 $4,511.00 $1,392.00 $8.955.00 $7.ARA.CO $9.323.00 (5541.00) $1,150.00 $6.979.00 East East West West West South West South East East Wast South South East East South West South East West South South East aine, Tray Grimes, Blake Cooper, Christofer Blank, Tomi Brayman, Valaura Lower, Jefrey Rowell, Calvin Wila, Jackson Jackson, Jason Chambers, Dwayne Swerean, Catherine Sumner, Thomas Brittain, Armelle Rolf Joshu Blaisdell, Jonathan Herson, Chad Martin, Mark Wall, Cornelius Myneni, Preston Wint, Leh Hotte, Philip Griffith, Sam nod, Leonard Lambert, Travis L MN 0 Quinties Overall Rank 31 61 91 121 Part/E Chambers, way Keel. Banca Blank, Tomi Quintile Name at 1 ON 2 Ctl 4 Qu5 Annual Profits $28,308.00 $15.900.00 $32,130.00 Part F East Qil 1 Cyl 3 Qil 3 0114 chl s Be careful with your use of cell references here! Q VLOOKUP Overall Rank Overal Quintile K Q 1 102 4 K Qui 1 AVERAGEIFS South West S AVERAGEIF Overal E Question 3 The table below shows the quarterly profits of 150 salespersons in three different regions (East, South, and West). Use the data to complete items below. I already created the cell ranges salesperson and profits for you. A. For each salesperson, calculate annual profits using quarterly profits. Use SUMIF function 9 10 8-Sort the data by annual profit in descending order and assign each salesman an overall rank, so that the salesman with the highest profit is ranked 1 and the second highest score is ranked 2, etc. (You could also use a RANKEQ function) 12 C-Use VLOOKUP to assign each salesperson to a quintile based on his or her rank, using the data in the Quintiles table. 13 D-Use a nested IF function to assign each salesperson to a quintile based on his or her rank. The results should match those obtained when completing part c. (Recall that you will need n-1 IF statements, where n=of categories). 16 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. 1.8 F. Use AVERAGEIFS to calculate the average annual profits of salespersons for a given quintile and region. Use AVERAGEIF to calculate the average annual profits of salespersons within a given quintile (Qtl 1, Qt1 2, etc.) 19 21 Quarterly Profits Region Salesperson Region Profits 24 Overall Rank Overall Quintile Overall Quintile INRANKEQ ICVLOOKUP (D) F 1 Qtl 1 2 3 25 West Ql1 Otl 1 26 West Otl 1 27 West Qu 1 Qul 1 Annual Profits Annual (A) SUMIF $36,936.00 $32,828.00 $32,444.00 $32.130.00 $31,479.00 $29,106.00 $28.539.00 $28,503.00 $28,350.00 $28.308.00 $28,191.00 28 West 4 Qil 1 Qtl 1 29 West 5 Otl 1 Otl 1 30 Wit Qu 1 Qul 1 I 31 7 West al 1 Ql1 32 West 8 Qtl 1 Otl 1 31 South Qu 1 Qul 1 34 South Otl 1 Otl 1 Qtl 1 Qtl 1 South South 37 West 35 35 9 10 11 12 13 14 Qul 1 Qul 1 Od 1 Ortl 1 Qtl 1 38 Wit Qtl 1 39 West 15 Qil 1 Qul 1 40 West 16 Otl 1 Crtl 1 41 17 Q 1 East Qul 1 $28,148.00 $27.865.00 $27,777.00 $27,397.00 $26.978.00 $26,861.00 $26,384.00 $26,070.00 $25,591.00 $25,313.00 $24,982.00 22 $24,826.00 23 $24,737.00 24 42 Qil 1 Qil 1 East 43 18 19 20 Otl 1 East Otl 1 44 Q1 East Qui 1 45 West 21 Qtl 1 Qil 1 46 West Otl 1 Otl 1 47 West Q1 Qul 1 45 West Q1 Qtl 1 Quarter 1 2 3 4 1 2 3 4 + 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 Salesperson Adsit, Donald Adit, Donald Adet, Denis Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris Arnols Farris Arnold, Farris Arnold, Farris Barkley, Jared Barkley, Jared Barkley, Jared Barkley, Jared Barry, Maly Barry, Molly Barry, Maly Barry, Maly Bexley, Griffin Baskey, Griff Beary, Griffin Bekley, Grimin $5.870.00 $6,542.00 $6,900.00 ($330.00) 8413.00 (5612.00) $6,523.00 $1,476.00 $7,360.00 Sa.co $7,569.00 $2.232.00 $1,290.00 ($198.00) $4,066.00 $7.063.00 $2,495.00 $4,511.00 $1,392.00 $8.955.00 $7.ARA.CO $9.323.00 (5541.00) $1,150.00 $6.979.00 East East West West West South West South East East Wast South South East East South West South East West South South East aine, Tray Grimes, Blake Cooper, Christofer Blank, Tomi Brayman, Valaura Lower, Jefrey Rowell, Calvin Wila, Jackson Jackson, Jason Chambers, Dwayne Swerean, Catherine Sumner, Thomas Brittain, Armelle Rolf Joshu Blaisdell, Jonathan Herson, Chad Martin, Mark Wall, Cornelius Myneni, Preston Wint, Leh Hotte, Philip Griffith, Sam nod, Leonard Lambert, Travis L MN 0 Quinties Overall Rank 31 61 91 121 Part/E Chambers, way Keel. Banca Blank, Tomi Quintile Name at 1 ON 2 Ctl 4 Qu5 Annual Profits $28,308.00 $15.900.00 $32,130.00 Part F East Qil 1 Cyl 3 Qil 3 0114 chl s Be careful with your use of cell references here! Q VLOOKUP Overall Rank Overal Quintile K Q 1 102 4 K Qui 1 AVERAGEIFS South West S AVERAGEIF Overal