Start with column (D) to be worked on unless other cell data is wrong. Please show the excel formulas for the orange cells.

AutoSave OFF @ en ... Homework 1 Part 2 (Empty) - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Insert v V v WE 48- O DX Delete v Fm IN Analyze Data V V .00 X Calibri (Body) 12 . A General 1 La Paste Conditional Format BIU $ %) * ' 0 Formatting as Table L25 4 X v P26,1F>0,1F(J2530,1F(J2560,1F(J2590,1F(J25>>>>>> E Cell Styles Find & Select Sort & Filter Format Create and Share Adobe PDF D F G H L MN O P O R 1 Question 3 2 3 4 5 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. 6 7 8 9 10 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) 11 12 . 13 14 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 . c. will need n-1 IF statements, where n = # of categories). 15 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. 16 17 18 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, Qti 2, etc.) 19 20 21 Quarterly Profits Quintiles 22 23 (C)VLOOKUP C) ( (D) IF Region Quarter Salesperson Profits Region Salesperson Overall Quintile Overall Quintile Overall Rank Quintile Name 1 West West 2 3 $5,876.00 $6,542.00 $6,900.00 Qtl 3 Qul 5 VL00KUP 0 0 31 61 West $11,979.00 24 25 26 27 28 29 30 31 32 33 34 West Qtl 1 Qti 2 Oti 3 Otl 4 Qtl 5 4 1 IC 230.00) 10612.00) 91 121 West Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry, Molly Barn Moll Bexley, Griffin Bishop, Andrew Bivins, Amelia place Black, Melinda West West .. West 2 3 , Annual Profits (A) SUMIF (B) RANK.EQ B Annual Overall Rank Profits $18,988.00 68 $14,747.00 110 124 $13,446.00 117 2016 $22,846.00 40 44444 $17,411.00 88 40cm .. $19,963.00 61 $17,900.00 80 SAGO 404 $14,905.00 107 2309 $27,397.00 15 $32,130.00 4 $21,052.00 51 $15,312.00 105 $31,479.00 5 $27,865.00 13 trone Paul Adsit, Donald Adsit, Donald Adsit, Donald Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris Arnold, Farris Arnold Arnold, Farris Arnold, Farris Arnold Barkley, Jared Barkley, Jared Barkley, Jared Your Info Q1 4 West West South West East - West - West South . South East West East South West South + VLOOKUP Qtl 3 Qtl 4 Qtl 5 Qtl 4 Qtl 2 Qtl 3 Qtl 3 Qtl 3 Qtl 4 Qtl 1 Qtl 1 Qtl 2 Qtl 4 Qtl 1 Qtl 1 1 plaisdeil, Jonathan CER $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 no $7,063.00 Q2 South .. South .. South F South Overall Rank Overall Quintile 2 3 Annual Profits $0.00 35 36 37 38 Part(E) E Chambers, Dwayne Keel, Bannca Blank, Tomi West plat Blank, Tomi Blount, Bannca Bollinger, Cameron Brayman, Valauna Brittain, Armelle 1 West West 39 2 3 Instructions Q3 Ready Circular References: P35 + 100% AutoSave OFF @ en ... Homework 1 Part 2 (Empty) - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Calibri (Body) 12 V . A Insert v ab General X La 1 v 29 O. DX Delete v Fm IN Analyze Data Paste BIU ' $ %) v .00 .00 0 V Conditional Format Formatting as Table Cell Styles Sort & Filter Format Find & Select Create and Share Adobe PDF 125 4 x fx =IF(J25>0,1F(J2530,1F(J2560,1F(J2590,1F(J25>>>>>> D E F G H K L MN o O R S 23 J (B) RANK.EQ (C)VLOOKUP (D) IF Overall Rank Overall Quintile Overall Quintile Quarter Salesperson Profits Region Salesperson Overall Rank Quintile Name 24 25 68 110 0 31 26 1 1 2 3 4 Qtl 3 Qtl 5 VLOOKUP 27 61 Oti 1 Qti 2 Qti 3 Qtl 4 Otis 28 29 30 91 121 1 mong West West South West we East West we West My South south South Puu East West Fast 2 3 4 31 32 33 124 117 40 88 61 80 107 15 - 4 51 105 VLOOKUP Part(E) 34 35 Overall Rank Overall Quintile 1 - 2 3 4 Annual Profits $0.00 Chambers, Dwayne Keel, Bannca Blank, Tomi 1 2 36 37 38 39 40 41 42 3 4 AVERAGEIFS AVERAGEIF 1 South West South South East - East ... West - West - East 2 (A) SUMIF Annual Profits $18,988.00 $14,747.00 $11,979.00 $13,446.00 13,170.00 $22.846.00 10. $17,411.00 11.ca $19.963.00 $17,900.00 www. $14,905.00 $27.397.00 Pence $32,130.00 124,130.00 $21.052.00 21.02. $15,312.00 113,312 $31,479.00 $27,865.00 * $22,977.00 $18,767.00 $28,308.00 $20,616.00 $18,797.00 * $36,936.00 $32,444.00 $22,748.00 $23,233.00 $16,240.00 $451.00 sacos $11,681.00 $ 16,975.00 $12,510.00 $12,510.00 $20,770.00 ** $18,729.00 149 $23,470.00 $3,857.00 $14,400.00 $17,660.00 $11,607.00 $10.711.00 5 13 39 70 10 55 69 . 1 West Overall 43 Adsit, Donald $5,876.00 Adsit, Donald $6,542.00 Adsit, Donald $6,900.00 Adsit, Donald ($330.00) Armstrong, Paul ($612.00) mons 0 12.00) Armstrong, Paul $6,523.00 10/22 Armstrong, Paul mons, Paul $1,476.00 1. Armstrong, Paul OS VI. $7,360.00 Arnold, Farris $888.00 an Arnold, Farris $7,569.00 V22 Arnold, Farris $2.232.00 12,232.00 Arnold, Farris $1,290.00 Barkley, Jared ($198.00) annicy, arcu Barkley, Jared $4,086.00 Barkley, Jared $7,063.00 Bu Barkley, Jared $2,495.00 1. Barry, Molly $4,511.00 . Barry, Molly $1,892.00 Barry, Molly $8,955.00 Barry, Molly $7,488.00 care Bexley, Griffin $9,823.00 Bc Bexley, Griffin ($541.00) . Bexley, Griffin $1,150.00 Bexley, Griffin $6,979.00 Bishop, Andrew $8,536.00 Bishop, Andrew $2,001.00 Bishop, Andrew $4,944.00 Bishop, Andrew $4,482.00 Bivins, Amelia $928.00 ... Bivins, Amelia $9,914.00 Bivins, Amelia $1,921.00 Bivins, Amelia $5,137.00 B. Black, Melinda $1,698.00 Ballada Black, Melinda $4,368.00 e Malinda Black, Melinda $7,923.00 Black, Melinda $916.00 Blaisdell, Jonatha $9,478.00 plaid Blaisdell, Jonatha S861.00 Instructions Your Info Q1 3 Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry, Molly Pally wony Bexley, Griffin Bishop, Andrew Pannes Bivins, Amelia mene Black, Melinda we Blaisdell, Jonathan tolla Blank, Tomi f Blount, Bannca Bollinger, Cameron Brayman, Valauna mu Brittain, Armelle Bryant, Christofer - Carter, Charmeca R. Chambers, Dwayne Harrod Channell, Jarrod be Chapman, Rebecca F Cline, Troy Cooper, Christofer ten Cory, Bennett Ich Culberson, Phillip Bennett Currey, Bennett lahman Kate Delehman, Kate Denson, Adolphus Drummond, Sam HU Duncan, Jared Dunning, Albert Ebanks, Frances Edwards, Jared Edward Lord Albach Elmore, Albert Essary, Cody Escar Code HI Farlow, Margaret Flores Alev Flores, Alex Floyd, Kevin Fonger, Anne Q3 + 4 Qtl 3 Qtl 4 Qtl 5 Qtl 4 Qtl 2 Qtl 3 Qtl 3 Qtl 3 24 Qtl 4 Qtl 1 Qtl 1 Qtl 2 Qtl 4 Qtl 1 Qtl 1 Qtl 2 2 Qtl 3 Qtl 1 Qtl 2 Qtl 3 Qtl 1 Qtl 1 Qtl 2 Qtl 2 Qtl 4 Qtl 5 Qtl 5 Qtl 4 Qtl 5 Qtl 5 Qtl 2 Qtl 3 Qtl 2 Qtl 5 Qtl 4 Qtl 3 Qtl 5 Qtl 5 44 45 46 1 Part(F) East South Qti 1 Oti 2 2 Qti 3 Ot14 Qt15 Be careful with your use of cell references here! 2 West 3 47 48 49 South East 3 .. 41 36 4 1 2 3 alabile 50 51 52 53 54 99 150 125 91 4 1 East .. South East - West East - West South South South East Albert 2 3 55 56 57 121 121 52 71 35 4 1 58 149 2 3 59 113 4 60 61 62 1 2 West South West South Q2 86 126 130 + Ready Circular References: P35 @ a TUTO AutoSave OFF @ en ... Homework 1 Part 2 (Empty) - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Insert v V v WE 48- O DX Delete v Fm IN Analyze Data V V .00 X Calibri (Body) 12 . A General 1 La Paste Conditional Format BIU $ %) * ' 0 Formatting as Table L25 4 X v P26,1F>0,1F(J2530,1F(J2560,1F(J2590,1F(J25>>>>>> E Cell Styles Find & Select Sort & Filter Format Create and Share Adobe PDF D F G H L MN O P O R 1 Question 3 2 3 4 5 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. 6 7 8 9 10 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) 11 12 . 13 14 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 . c. will need n-1 IF statements, where n = # of categories). 15 E. Use VLOOKUP to find the annual profits, overall rank, and quintile for the three salespersons listed in the table. 16 17 18 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, Qti 2, etc.) 19 20 21 Quarterly Profits Quintiles 22 23 (C)VLOOKUP C) ( (D) IF Region Quarter Salesperson Profits Region Salesperson Overall Quintile Overall Quintile Overall Rank Quintile Name 1 West West 2 3 $5,876.00 $6,542.00 $6,900.00 Qtl 3 Qul 5 VL00KUP 0 0 31 61 West $11,979.00 24 25 26 27 28 29 30 31 32 33 34 West Qtl 1 Qti 2 Oti 3 Otl 4 Qtl 5 4 1 IC 230.00) 10612.00) 91 121 West Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry, Molly Barn Moll Bexley, Griffin Bishop, Andrew Bivins, Amelia place Black, Melinda West West .. West 2 3 , Annual Profits (A) SUMIF (B) RANK.EQ B Annual Overall Rank Profits $18,988.00 68 $14,747.00 110 124 $13,446.00 117 2016 $22,846.00 40 44444 $17,411.00 88 40cm .. $19,963.00 61 $17,900.00 80 SAGO 404 $14,905.00 107 2309 $27,397.00 15 $32,130.00 4 $21,052.00 51 $15,312.00 105 $31,479.00 5 $27,865.00 13 trone Paul Adsit, Donald Adsit, Donald Adsit, Donald Adsit, Donald Armstrong, Paul Armstrong, Paul Armstrong, Paul Armstrong, Paul Arnold, Farris Arnold, Farris Arnold Arnold, Farris Arnold, Farris Arnold Barkley, Jared Barkley, Jared Barkley, Jared Your Info Q1 4 West West South West East - West - West South . South East West East South West South + VLOOKUP Qtl 3 Qtl 4 Qtl 5 Qtl 4 Qtl 2 Qtl 3 Qtl 3 Qtl 3 Qtl 4 Qtl 1 Qtl 1 Qtl 2 Qtl 4 Qtl 1 Qtl 1 1 plaisdeil, Jonathan CER $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 no $7,063.00 Q2 South .. South .. South F South Overall Rank Overall Quintile 2 3 Annual Profits $0.00 35 36 37 38 Part(E) E Chambers, Dwayne Keel, Bannca Blank, Tomi West plat Blank, Tomi Blount, Bannca Bollinger, Cameron Brayman, Valauna Brittain, Armelle 1 West West 39 2 3 Instructions Q3 Ready Circular References: P35 + 100% AutoSave OFF @ en ... Homework 1 Part 2 (Empty) - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share 0 Comments Calibri (Body) 12 V . A Insert v ab General X La 1 v 29 O. DX Delete v Fm IN Analyze Data Paste BIU ' $ %) v .00 .00 0 V Conditional Format Formatting as Table Cell Styles Sort & Filter Format Find & Select Create and Share Adobe PDF 125 4 x fx =IF(J25>0,1F(J2530,1F(J2560,1F(J2590,1F(J25>>>>>> D E F G H K L MN o O R S 23 J (B) RANK.EQ (C)VLOOKUP (D) IF Overall Rank Overall Quintile Overall Quintile Quarter Salesperson Profits Region Salesperson Overall Rank Quintile Name 24 25 68 110 0 31 26 1 1 2 3 4 Qtl 3 Qtl 5 VLOOKUP 27 61 Oti 1 Qti 2 Qti 3 Qtl 4 Otis 28 29 30 91 121 1 mong West West South West we East West we West My South south South Puu East West Fast 2 3 4 31 32 33 124 117 40 88 61 80 107 15 - 4 51 105 VLOOKUP Part(E) 34 35 Overall Rank Overall Quintile 1 - 2 3 4 Annual Profits $0.00 Chambers, Dwayne Keel, Bannca Blank, Tomi 1 2 36 37 38 39 40 41 42 3 4 AVERAGEIFS AVERAGEIF 1 South West South South East - East ... West - West - East 2 (A) SUMIF Annual Profits $18,988.00 $14,747.00 $11,979.00 $13,446.00 13,170.00 $22.846.00 10. $17,411.00 11.ca $19.963.00 $17,900.00 www. $14,905.00 $27.397.00 Pence $32,130.00 124,130.00 $21.052.00 21.02. $15,312.00 113,312 $31,479.00 $27,865.00 * $22,977.00 $18,767.00 $28,308.00 $20,616.00 $18,797.00 * $36,936.00 $32,444.00 $22,748.00 $23,233.00 $16,240.00 $451.00 sacos $11,681.00 $ 16,975.00 $12,510.00 $12,510.00 $20,770.00 ** $18,729.00 149 $23,470.00 $3,857.00 $14,400.00 $17,660.00 $11,607.00 $10.711.00 5 13 39 70 10 55 69 . 1 West Overall 43 Adsit, Donald $5,876.00 Adsit, Donald $6,542.00 Adsit, Donald $6,900.00 Adsit, Donald ($330.00) Armstrong, Paul ($612.00) mons 0 12.00) Armstrong, Paul $6,523.00 10/22 Armstrong, Paul mons, Paul $1,476.00 1. Armstrong, Paul OS VI. $7,360.00 Arnold, Farris $888.00 an Arnold, Farris $7,569.00 V22 Arnold, Farris $2.232.00 12,232.00 Arnold, Farris $1,290.00 Barkley, Jared ($198.00) annicy, arcu Barkley, Jared $4,086.00 Barkley, Jared $7,063.00 Bu Barkley, Jared $2,495.00 1. Barry, Molly $4,511.00 . Barry, Molly $1,892.00 Barry, Molly $8,955.00 Barry, Molly $7,488.00 care Bexley, Griffin $9,823.00 Bc Bexley, Griffin ($541.00) . Bexley, Griffin $1,150.00 Bexley, Griffin $6,979.00 Bishop, Andrew $8,536.00 Bishop, Andrew $2,001.00 Bishop, Andrew $4,944.00 Bishop, Andrew $4,482.00 Bivins, Amelia $928.00 ... Bivins, Amelia $9,914.00 Bivins, Amelia $1,921.00 Bivins, Amelia $5,137.00 B. Black, Melinda $1,698.00 Ballada Black, Melinda $4,368.00 e Malinda Black, Melinda $7,923.00 Black, Melinda $916.00 Blaisdell, Jonatha $9,478.00 plaid Blaisdell, Jonatha S861.00 Instructions Your Info Q1 3 Adsit, Donald Armstrong, Paul Arnold, Farris Barkley, Jared Barry, Molly Pally wony Bexley, Griffin Bishop, Andrew Pannes Bivins, Amelia mene Black, Melinda we Blaisdell, Jonathan tolla Blank, Tomi f Blount, Bannca Bollinger, Cameron Brayman, Valauna mu Brittain, Armelle Bryant, Christofer - Carter, Charmeca R. Chambers, Dwayne Harrod Channell, Jarrod be Chapman, Rebecca F Cline, Troy Cooper, Christofer ten Cory, Bennett Ich Culberson, Phillip Bennett Currey, Bennett lahman Kate Delehman, Kate Denson, Adolphus Drummond, Sam HU Duncan, Jared Dunning, Albert Ebanks, Frances Edwards, Jared Edward Lord Albach Elmore, Albert Essary, Cody Escar Code HI Farlow, Margaret Flores Alev Flores, Alex Floyd, Kevin Fonger, Anne Q3 + 4 Qtl 3 Qtl 4 Qtl 5 Qtl 4 Qtl 2 Qtl 3 Qtl 3 Qtl 3 24 Qtl 4 Qtl 1 Qtl 1 Qtl 2 Qtl 4 Qtl 1 Qtl 1 Qtl 2 2 Qtl 3 Qtl 1 Qtl 2 Qtl 3 Qtl 1 Qtl 1 Qtl 2 Qtl 2 Qtl 4 Qtl 5 Qtl 5 Qtl 4 Qtl 5 Qtl 5 Qtl 2 Qtl 3 Qtl 2 Qtl 5 Qtl 4 Qtl 3 Qtl 5 Qtl 5 44 45 46 1 Part(F) East South Qti 1 Oti 2 2 Qti 3 Ot14 Qt15 Be careful with your use of cell references here! 2 West 3 47 48 49 South East 3 .. 41 36 4 1 2 3 alabile 50 51 52 53 54 99 150 125 91 4 1 East .. South East - West East - West South South South East Albert 2 3 55 56 57 121 121 52 71 35 4 1 58 149 2 3 59 113 4 60 61 62 1 2 West South West South Q2 86 126 130 + Ready Circular References: P35 @ a TUTO