i need the formulas to do these questions
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 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 RANK.EQ function) C-Use VLOOKUP to assign each salesperson to a quintile based on his or her rank, using the data in the Quintiles table. - Use a nosted of function to assign each salesperson to a quintile based on his or her rank. The results should match those obtained when completing parte (Recall that you will need n-1 IF statements, where nt# of categories). E. USE VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. F. Use AVERAGEIFS to calculate the average annual profits of salespersons for a given quintile and region. Use AVERAGEF to calculate the average annual profits of salespersons within a given quintile (Qul 1, 2011 2, etc.) 2 Antur IM ANO A. Chwan WEDGIRON Salon Det Quiet . A LE - an TRN La SALE at Cow . 19 ME DAN wwwy NOW Pretre D . TA w ca ME ELE 10 w TE (C) VLOOKUP Overall Quintile (D) IF Overall Quintile 21 22 Annual Profits 23 (A) SUMIF (B) RANK.EQ Salesperson Annual Overall Rank 24 Profits 25 Adsit, Donald 26 Armstrong, Paul 27 Arnold, Farris 28 Barkley, Jared 29 Barry, Molly 30 Bexley, Griffin 31 Bishop, Andrew 32 Bivins, Amelia 33 Black, Melinda 34 Blaisdell, Jonathan 35 Blank, Tomi 36 Blount, Bannca 2.7 Rollingar Camaran Instructions Your Info 01 02 Chan Keel, Bland 03 R Overall Rank Quintile Name 0 31 61 Qtl 1 Qtl 2 Qtl 3 Qtl 4 Qtl 5 91 121 VLOOKUP Part(E) Annual Profits Overall Rank Overall Quintile Chambers, Dwayne Keel, Bannca Blank, Tomi P. O Blank, Tomi Q R S AVERAGEIFS AVERAGEIF West Overall Part(F) East South Qtl 1 Qtl 2 Qti 3 Qtl 4 Qtl 5 Be careful with your use of cell references here! Quarterly Profit Annual Profil (A) SUMI (O) RANKE Annual Overall Rank Profits Region Quarter Salesperson Profits Region IC) VLOOKUP Overall Quintile (DF Overall Quintile Salesperson 1 2 West West South West 4 West West West West West West West West South South South South Weu West We West 1 2 3 4 1 2 3 Adsit, Donald A Donald Atsit, Donald Adsit, Donald matrone Paul Armstrong, Paul Armstrone, Paul Romstrene Paul Arnold. Farris Arnold, Farris Arnold, Faris Arnold, Paris Bardey.lared Barkley, Jared Haridelared Barkley.lared Garry, Molly Barry, Molly Bar Mall 55,576.00 $6.542.00 56,900.00 55100 1551200) 56.523,00 $1.476,00 52260.00 $83.00 52.569.00 $2232.00 $1.290.00 15100 $4,056.00 $7,063 00 52.495.00 $4,511.00 $1.892.00 $8.955.00 West Well South South Itast West East South West Isouth South Adit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry Molly tedey. Griffin Bishop, Andrew Bivint, Amelia Black Melinds Blaisdell loathan Blank, Tom Blount, Banca Bollinger Cameron Brayman, Valona Britain, Armelle Bryant Christofer Carter Charmeca Chambers Dwayne Channel, arrod IC 1 2 1 4 1 2 ant 1 East West La Instructions Your Info 01 Q2 Q3 + Fady O Type here to search E 69% 69% 741 Quarterly Profits Region Quarter Salesperson Profits West 1 2 3 4. 1 2 3 West West West West West West West South South South South West West West 4 1 2 Adsit, Donald Adsit, Donald Adsit, Donald Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris Arnold, Farris Arnold, Farris Arnold, Farris Barkley, Jared Barkley, Jared Barkley, Jared Barkley, Jared Barry, Molly Barry, Molly Barry, Molly DM Your Info $5,876.00 $6,542.00 $6,900.00 ($330.00) ($612.00) $6,523.00 $1,476.00 $7,360.00 $888.00 $7,569.00 $2,232.00 $1,290.00 ($198.00) $4,086.00 $7,063.00 $2,495.00 $4,511.00 $1,892.00 $8,955.00 3 4 1 2 3 West East 4 1 . East 2 3 East 3 Instructions Ceady Q1 Q2 Q3 Type here to search Annual Profits (A) SUMIF (B) RANK.EQ Annual Overall Rank Profits Region Salesperson (C) VLOOKUP Overall Quintile (D) IF Overall Quintile West West South West East West West South South East West East South West South South East East West Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry, Molly Bexley, Griffin Bishop, Andrew Bivins, Amelia Black, Melinda Blaisdell, Jonathan Blank, Tomi Blount, Bannca Bollinger, Cameron Brayman, Valauna Brittain, Armelle Bryant, Christofer Carter, Charmeca Chambers, Dwayne Channell, Jarrod Ch 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 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 RANK EQ function) C-Use VLOOKUP to assign each salesperson to a quintille based on his or her rank. using the data in the Quintiles table. 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 e, (Recall that you will need n-1 IF statements, where #of categories). E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. 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 (Qu 1, Qt1 2, etc.)
