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 vou will need n-1 IF statements, where n #of categories) Overall Rank Quintile Name 0 Qtl 1 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in table D 31 Qtl 2 #NAME? 61 Qtl 3 91 Qtl 4 Qtl 5 Quarterly Profits Region Quarter Salesperson Adsit, Donald Adsit, Donald Adsit, Donald Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris 121 al Profis Profits Part(A) Part(B) Part(C) Part(D) West West $5,876.00 $6,542.00 SUMIF RANK.EQ Overall Rank VLOOKUP IF 1 Salesperson Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry,Molly Bexley, Griffin Bishop, Andrew Bivins, Amelia Black, Melinda Blaisdell. Jonathan Region West West South West East West West South South East Overall Quintile #REF! Annual Overall 2 $6,900,00 $12,500.00 $16,247.00 $10,893.00 $18,155.00 $28,158.00 Part(E) West 3 11A VLOOKUP West Table D 4 ($330.00) 96 Annual West 1 ($612.00) 129 Overall Rank Overall Quintile Profits West 2 $6,523.00 S1,476.00 79 Chambers, Dwayne Keel, Bannca West 3 13 West South 4 $16,124.00 $12,355.00 $7,360.00 94 Blank, Tomi $888.00 116 Arnold, Farris Arnold, Farris Arnold, Farris Barkley, Jared South $7,569.00 $18,670,00 72 1 oint $22,685.00 2 points South 3 $2,232.00 S1,290.00 ($198.00) $4,086.00 $7,063.00 42 2 points 2 points South Part(F) AVERAGEIF 4 $26,651.00 17 AVERAGEIFS Blank, Tomi Blount, Bannca Bollinger, Cameron Brayman, Valauna Brittain, Armelle Bryant, Christofer Carter, Charmeca West West West East South West South South East $27,146.00 $25,326.00 $12,814.00 $32,344.00 $29,280.00 $21,858.00 Quintile/Region Qtl 1 1 15 East South West Overall Barkley, Jared Barkley, Jared Barkley, Jared |arry, Molly 2 24 Qtl 2 LWest 3 105 West $2,495.00 Qtl 3 4 5 East $4,511.00 $1,892.00 Qtl 4 1 Barry, Molly East Qtl 5 2 43 Barry, Molly Be careful with your use of cell references here! East $8.955.00 $21.704.00 3 45 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 vou will need n-1 IF statements, where n #of categories) Overall Rank Quintile Name 0 Qtl 1 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in table D 31 Qtl 2 #NAME? 61 Qtl 3 91 Qtl 4 Qtl 5 Quarterly Profits Region Quarter Salesperson Adsit, Donald Adsit, Donald Adsit, Donald Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris 121 al Profis Profits Part(A) Part(B) Part(C) Part(D) West West $5,876.00 $6,542.00 SUMIF RANK.EQ Overall Rank VLOOKUP IF 1 Salesperson Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry,Molly Bexley, Griffin Bishop, Andrew Bivins, Amelia Black, Melinda Blaisdell. Jonathan Region West West South West East West West South South East Overall Quintile #REF! Annual Overall 2 $6,900,00 $12,500.00 $16,247.00 $10,893.00 $18,155.00 $28,158.00 Part(E) West 3 11A VLOOKUP West Table D 4 ($330.00) 96 Annual West 1 ($612.00) 129 Overall Rank Overall Quintile Profits West 2 $6,523.00 S1,476.00 79 Chambers, Dwayne Keel, Bannca West 3 13 West South 4 $16,124.00 $12,355.00 $7,360.00 94 Blank, Tomi $888.00 116 Arnold, Farris Arnold, Farris Arnold, Farris Barkley, Jared South $7,569.00 $18,670,00 72 1 oint $22,685.00 2 points South 3 $2,232.00 S1,290.00 ($198.00) $4,086.00 $7,063.00 42 2 points 2 points South Part(F) AVERAGEIF 4 $26,651.00 17 AVERAGEIFS Blank, Tomi Blount, Bannca Bollinger, Cameron Brayman, Valauna Brittain, Armelle Bryant, Christofer Carter, Charmeca West West West East South West South South East $27,146.00 $25,326.00 $12,814.00 $32,344.00 $29,280.00 $21,858.00 Quintile/Region Qtl 1 1 15 East South West Overall Barkley, Jared Barkley, Jared Barkley, Jared |arry, Molly 2 24 Qtl 2 LWest 3 105 West $2,495.00 Qtl 3 4 5 East $4,511.00 $1,892.00 Qtl 4 1 Barry, Molly East Qtl 5 2 43 Barry, Molly Be careful with your use of cell references here! East $8.955.00 $21.704.00 3 45