please provide formulas used for blank cells

The table below shows the quarterly profits of 150 salespersons in three different regions (East, South, and West). Use the data to complete items beur. I already created the cell ranges salesperson and profits for you. AFor each salesperson, calculate annual profits using quarterly profits. Use SUMIF function B - In the Overall Rank column, assign a rank to cach salesperson based on the individual's annual profits. Usea RANK.FO function. Rank the list in deesending order. For example, the salesperson with the highest annual profits has a rank of "1", and the person with the lowest annual profits S mok or "150", C-Use VLOOKUP to assign each salesperson to a quintile based on his or her rank D-Lise a nested IF function to assign each salesperson to a quintile hased on his or her rank. The results should match those ohtained when completing part c. (Recall that you will need n-1 IF statements, where n = # of categories) Ovemll Rak Quintile Name Od Qul 2 E. Use VLOOKUP to find the annual profits. overall rank, and quintile for the three salespersons listed in table D 31 F. Use AVERAGEIFS to calculate the average annual profits of salespersons for a glven quintile and region. Use AVERAGEIF to calculate the average annual prefits of salespersons within a given quintile QUL 3 121 QUL 5 Rexion West West West West Partit) IF Overall Part(E) Table D | VLOOKUP West 124 Annual Trulits Overall Rank Overall Quintile Quariwly Props Quarter Salesperson Adsit, Donald Adsit, Donald Adsit, Dunald Adsit, Donald Amstrong, Paul Amstrong, Paul Anstrucs, Paul Annsins, Paul Arnold, Faris Arnold, Faris Arnold, Faris Arnold, Farus 1 Barkley. Je 2 Barkley, Jeral 3 Barkley, Joel 4 Barkley, Jr Rany, Molly 2 Bany, Mally 3 Bany, Mally 4 Barry, Molly Part ) VLOOKUP Overall Quintile Ou3 Odl4 Ods OL 4 QUI 2 Qul) Qil 3 QUI 3 West West West South South South South West West West West Pofits S5,876.00 S6.542.00 S6,900.00 ISB30.00) (S612.00) S6, $23.00 $1,476.00 57,360.00 SSN 00 S7.569.00 S2,232.00 $1,290.00 IS198.00 $4.0186 $2.06. $2,495.00 $4,511.00 Chumbo, Dwayne Kerl, Banca Blank, Tomi 1111111 Region West West South West East Wust West South South East West Fast South West South South Fast Annwal Profits PartiA) Part() SUMIF RANK.EO Annual Overall Rank $18,983.00 $14,747.00 110 SL1,979.00 SIJ,446,00 117 $22.8-16.00 S17,411.00 $19.963.00 61 $17,900.00 BU $14.905.00 $27,397,00 $32,130.00 $21.052,00 $15,312,00 105 $31,479,00 $27,865,00 13 $22.979.00 SI8.767.00 $28,208.000 Salesperson Adsit, Donald Amstrong, Paul Arnold, Faris Barkley, Jaud Barry, Molly Bexley, Grillin Bisbop, Andrew Bivins, Amelia Black. M ind Bruscell, Jonathan Blink, Tomi Blount, Banca Bollinger, Cameron Rryman, Valang Brittain, Amelle Bivat. Christofer Carter, Charme Chamhers, D ryne 15 AVERAGEIF West 0112 0:14 Par(F) AVERAGELFS Quintiler Rogice East South Ol1 Oxl 2 Oli Oxl 4 Oxls Be careful with your use of cell references here! Od Fast Fast Fast S8.955.) $7,488.00 Fast Od The table below shows the quarterly profits of 150 salespersons in three different regions (East, South, and West). Use the data to complete items beur. I already created the cell ranges salesperson and profits for you. AFor each salesperson, calculate annual profits using quarterly profits. Use SUMIF function B - In the Overall Rank column, assign a rank to cach salesperson based on the individual's annual profits. Usea RANK.FO function. Rank the list in deesending order. For example, the salesperson with the highest annual profits has a rank of "1", and the person with the lowest annual profits S mok or "150", C-Use VLOOKUP to assign each salesperson to a quintile based on his or her rank D-Lise a nested IF function to assign each salesperson to a quintile hased on his or her rank. The results should match those ohtained when completing part c. (Recall that you will need n-1 IF statements, where n = # of categories) Ovemll Rak Quintile Name Od Qul 2 E. Use VLOOKUP to find the annual profits. overall rank, and quintile for the three salespersons listed in table D 31 F. Use AVERAGEIFS to calculate the average annual profits of salespersons for a glven quintile and region. Use AVERAGEIF to calculate the average annual prefits of salespersons within a given quintile QUL 3 121 QUL 5 Rexion West West West West Partit) IF Overall Part(E) Table D | VLOOKUP West 124 Annual Trulits Overall Rank Overall Quintile Quariwly Props Quarter Salesperson Adsit, Donald Adsit, Donald Adsit, Dunald Adsit, Donald Amstrong, Paul Amstrong, Paul Anstrucs, Paul Annsins, Paul Arnold, Faris Arnold, Faris Arnold, Faris Arnold, Farus 1 Barkley. Je 2 Barkley, Jeral 3 Barkley, Joel 4 Barkley, Jr Rany, Molly 2 Bany, Mally 3 Bany, Mally 4 Barry, Molly Part ) VLOOKUP Overall Quintile Ou3 Odl4 Ods OL 4 QUI 2 Qul) Qil 3 QUI 3 West West West South South South South West West West West Pofits S5,876.00 S6.542.00 S6,900.00 ISB30.00) (S612.00) S6, $23.00 $1,476.00 57,360.00 SSN 00 S7.569.00 S2,232.00 $1,290.00 IS198.00 $4.0186 $2.06. $2,495.00 $4,511.00 Chumbo, Dwayne Kerl, Banca Blank, Tomi 1111111 Region West West South West East Wust West South South East West Fast South West South South Fast Annwal Profits PartiA) Part() SUMIF RANK.EO Annual Overall Rank $18,983.00 $14,747.00 110 SL1,979.00 SIJ,446,00 117 $22.8-16.00 S17,411.00 $19.963.00 61 $17,900.00 BU $14.905.00 $27,397,00 $32,130.00 $21.052,00 $15,312,00 105 $31,479,00 $27,865,00 13 $22.979.00 SI8.767.00 $28,208.000 Salesperson Adsit, Donald Amstrong, Paul Arnold, Faris Barkley, Jaud Barry, Molly Bexley, Grillin Bisbop, Andrew Bivins, Amelia Black. M ind Bruscell, Jonathan Blink, Tomi Blount, Banca Bollinger, Cameron Rryman, Valang Brittain, Amelle Bivat. Christofer Carter, Charme Chamhers, D ryne 15 AVERAGEIF West 0112 0:14 Par(F) AVERAGELFS Quintiler Rogice East South Ol1 Oxl 2 Oli Oxl 4 Oxls Be careful with your use of cell references here! Od Fast Fast Fast S8.955.) $7,488.00 Fast Od