Can someone please solve in Excel and show formulas, fill in the orange cells. Thanks
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 B-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. 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 = Il 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 (Qtl 1, Qt12, etc.) Quarterly Profits Annual Profits (A) SUMIF Annual Profits Region West Region West West South Salesperson Adsit, Donald Armstrong, Paul Arnold, Farris West West West Barkley, Jared Profits $5,876.00 $6,542.00 $6,900.00 ($330.00) ($612.00) $6,523.00 $1,476.00 $7,360.00 $888.00 West East West Barry, Molly West West Bexley, Griffin West West Bishop, Andrew West South Bivins, Amelia South South Black, Melinda South East Blaisdell, Jonathan $7,569.00 $2,232.00 South West Blank, Tomi South Blount, Bannca $1,290.00 East West ($198.00) South Bollinger, Cameron West West Brayman, Valauna Brittain, Armelle West West Bryant, Christofer South South East East East Carter, Charmeca Chambers, Dwayne East East Channell, Jarrod West West East Chapman, Rebecca West East West West Cline, Troy Cooper, Christofer Cory, Bennett West South Wort Fast Culherson Phillin Quarter 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 1 Salesperson 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 Barry, Molly Bexley, Griffin Bexley, Griffin Bexley, Griffin Bexley Griffin $4,086.00 $7,063.00 $2,495.00 $4,511.00 $1,892.00 $8,955.00 $7,488.00 $9,823.00 ($541.00) $1,150.00 $6.979.00 (B) RANK.EQ (C) VLOOKUP (D) IF Overall Rank Overall Quintile Overall Quintile Quintiles Overall Rank 0 31 61 91 121 Part(E) Chambers, Dwayne Keel, Bannca Blank, Tomi Quintile Name Qtl 1 Qtl 2 Qtl 3 Qtl 4 Qtl 5 VLOOKUP Annual Profits Overall Rank AVERAGEIFS East South of e of cell references here! Part(F) Qtl 1 Qtl 2 Qtl 3 Qtl 4 Qtl 5 Be careful with your use Overall Quintile West AVERAGEIF Overall
