Question

I State United CEO Total Age of Compensation Industry CompanyCity oF Industry ConyBirth of StatesCEO Birth Region F57 490,001.00 Financial Rochester Passaic 498,500.00 Financi TrustmarkAberdeen 500,000.00 Financi GP 523,040.00 Financial First 523,560.00 Financia SFFed 535,817.00 Financial Michigan Brooklyn 536,930.00 548,34100 Financia Syunovus Augusta 574,745.00 Financial CullentFros San AntoiTX 575,211.00 Financial CSF 53 Financial United Columbus NC Miami Belleville Charlotte Leavenworth KS FL Financial Magna 611,963.00 Financia First 615,841.00 Financia BBT 617,054.00 Financial California NY 627,048.00 Financial Premier 650,443.00 Financial First 652,115.00 Financia St 663,111.00 Financi Franklin Montclair 668,831.00 Financial First 674,514.00 Financial Ancho Philadelphia PA 687,147.00 Financial US 691,994.00 Financial Summit 697,279.00 Financial MMGIC 716,365.00 Financial United 728,010.00 Financi Coast 741,192.00 768,648.00 Financial First 770,034.00 Financial Dauphin Philadelphia PA 780,250.00 Financial Ambac City Watertown Kansas City MO Nogales Centerville Ogden Financial Fourth I 65 1 48 46 0,932.00 Financia Charter Baltimore MMD 793,214.00 Financia Hibernia Branch $798.255,00 Financial FirstFed PhiladelhiPA I 687,147.00 Financial US 691,994.00 Financial Summit Grandview WA Watertown Chicago W55 $ 697,279.00 Financial MGIC $716,365.00 Financial United Kansas City MO $728,010.00 FinancialCoast Nogales 49 67 WI58 741,192.00 Financial Fourth Centerville SD $768,648.00 Financial First $770,034.00 Financial Dauphin Philadelphia PA $780,250.00 Financial Aba City W59 E 65 Ogden 780,932.00 Financial CharterBaltimore MD 48 $793,214.00 Financial Hibernia Branch $798,255.00 Financial FirstFed Philadelphia PA $800,265.00 Financial FirstFed Gainesville FL $842,121.00 Financial Compass Birmingham AL 61 S148 869,452.00 Financial TCF N 50 $875,180.00 Financial Bank Washington DC $884,470.00 Financial Standard Detroit Los Angeles CA 912,994.00 Financial Star 921,921.00 Financial Wachovia Lovettsile VA WI50 $935,235.00 Financial Comerica Detroit 951,545.00 Financial AmSouh Evanston $953,206.00 Financial Southern Charlotte $960,565.00 Financial Integra $962,075.00 Financial Midlantic Fairmont 54 N 60 N i 54 57 E 62 Youngstown OH MMN 979,092.00 Financial Marshalllsle Milw aukeeW MD $987 400.00 Financial Mercantile Baltimore $1020,460.00 Financial Provident Dayton $1030,600.00 Financial GATX $1,047 910.00 Financia SouthTrust Dothan $ 1075,140.00 Financial Dime $1086,720.00 Financial National Davenport $1,121,950.00 Financia Cent Lincolnton NC $1,173,000.00 Financial MBIA AL 57 Brooklyn N 62 S 59 Canaan Question 1: The purpose of the first question is to demonstrate how to sort Suppose you wished to sort the data according to its region ih the United States. (2 pts.) 1]Highlight the entire table, including the column titles 2) Click on "Sort and Filter" and choose "Custom Sor 3) For the bo that pops up, choose from the drop-down menu of each: Sort by "United States Region", sort on "Values",an order by"A-Z" Click on"OK" and your data should be ordered with all of the Eastern States first, followed by the Northern States, Southe'n States, then western States 4) Question 2: Suppose you wished to count the number of CEO's born in each region of the United States. Rather than manually count them, Excels "COUNTIF" function is very helpful. So, we are going to use this function to count che number of CEO's born in the Eastern United States region. (5 pts.) 1)Type COUNTIF 2) You will be asked to input the range of cells from whih the coting takes place. Highlight the entire "United States Region" cclumn, but do not include the column title ) Now,enter a comma, followed by an E in double quocations. This statement will count the number of CEO's born in the Eastern United States region. Press "Enter" and a number will appear that shows the number of CEO's born in this region. Now, complete this table, using Excel's COUNTIF Function. 4) East West North South Question 3: Using the counts above, create a pie chart that shows the number of CEO's born in each of the four regions of the United States. Be sure to include a meaningful chart title, legend, and percentages. (4 pts.)

Answer 1

1.

The pop-up box that appears when you select custom sort

Sort CEO Total Industry Company City Of Bir State Of B United Ste Age of CEO 2 490001 Financial Rochester Passaic NJ 3 498500 Financial Trustmark Aberdeen MS 4 500000 Financial GP 5 523040 Financial First 6 523560 Financial SFFed Neosho MO 7 535817 Financial Michigan Brooklyn NY 8 536930 Financial United Columbus NC 9 548341 Financial Synovus Augusta GA 10 574745 Financial Cullen/Fro San Antor TX 11 575211 Financial CSF 12 608262 Financial Magna Belleville IL 13 611963 Financial First 14 615841 Financial BBT 15 617054 Financial California NY 16 627048 Financial Premier LeavenwCKS 17| 650443 Financial First 18 652115 Financial St 19 663111 Financial Franklin Montclair NJ 20 668831 Financial First 21 674514 Financial Anchor Philadelpl PA 22 687147 Financial US 57 Add Level XDelete Levelopy LevelO Options..MMy data has headers Column Sort On Order Racine WI 53 Smithfiel NC Sort by United States Regior Cell Values A to Z 52 57 Miami FL 52 Cancel NY Charlotte NC 45 57 Island NY 59 53 61 49 Detroit MI Clarksville IN Grandviev WA

After you sort on the basis of United States Region,this is the way the data appears:-

1 1 CEO Total Industry Company City Of Bi State Of B United Sta Age of CE 2 490001 Financial Rochester Passaic NJ 3 535817 Financial Michigan Brooklyn NY 4 611963 Financial First NY 5617054 Financial CaliforniaNY 6 650443 Financial First Island NY 7 663111 Financial Franklin MontclaiiNJ 8 674514 Financial Anchor Philadelp PA 9 691994 Financial Summit Watertow NY 10 770034 Financial Dauphin Philadelp PA 11 780932 Financial Charter BaltimorE MD E 12 793214 Financial Hibernia Branch NJ 13 798255 Financial FirstFed Philadelp PA 14 875180 Financial Bank Washingt DC 15 987400 Financial Mercantil Baltimore MD E 16 1075140 Financial Dime Brooklyn NY 17 1173000 Financial MBIA Canaan CT 18 500000 Financial GP 19 608262 Financial Magna Belleville IL 20 627048 Financial Premier Leavenwa KS 21 652115 Financial St 22 668831 Financial First Clarksvill IN 23 697279 Financial MGIC Chicago IL 24 741192 Financial Fourth CentervilLSD 25 869452 Financial TC 26 884470 Financial Standard Detroit M 27 935235 Finanrial Comercia DetroitM 57 54 NY NY 57 59 61 62 53 65 48 46 61 52 62 46 52 53 64 E Racine WI Detroit MI 53 49 49 56 50 63 Detroit MI

28 951545 Financial AmSouth Evanston IL 29 960565 Financial Integra Youngsto OH N 30 962075 Financial MidlanticFairmont MN N 31 979092 Financial Marshall Milw auk WIN 32 1020460 Financial Provident DaytonOH N 33 1030600 Financial GATX Chicago IL 34 1086720 Financial National Davenpor IA 35 498500 Financial Trustmarl Aberdeen MS 36 523040 Financial First Smithfiel NC S 37 523560 Financial SFFed Neosho MO S 38 536930 Financial United Columbus NC S 39 548341 Financial Synovus Augusta GAS 40 574745 Financial Cullen/Fr San Antor TXs 41 575211 Financial CSF Miami FL S 42 615841 Financial BBT Charlotte NC S 43 716365 Financial United Kansas Ci M0 S 44 780250 Financial Ambac City MOS 45 800265 Financial FirstFed Gainesvil FL S 461842121 Financial Compass Birminghỉ AL S 47 921921 Financial Wachovia Lovettsvil VA S 48 953206 Financial Southern Charlotte NC S 49 1047910 Financial SouthTrus Dothan AL S 50 1121950 Financial Cenral Lincolnto NC S 51 687147 Financial US 52 728010 Financial CoastNogales AZ 53 768648 Financial First Ogen UT 54 912994 Finanrial Star 62 54 57 52 59 62 56 52 57 52 52 45 67 50 48 51 52 54 57 59 Grandvie WA 58 9 59 Los Anpel CA NW

2.

K2 fr COUNTIF(F2F54,"E") 1 CEO Total Industry Company City Of Bi State Of B United StaAge of CEO 2 490001 Financial Rochester Passaic NJ 3 535817 Financial Michigan Brooklyn NY 4 611963 Financial First 5 617054 Financial California NY 6 650443 Financial First 7 663111 Financial Franklin Montclail NJ 8 674514 Financial Anchor Philadelp PA 9 691994 Financial Summit Watertow NY 10 770034 Financial Dauphin Philadelp PA 11 78093? Financial Charter Raltimore MD Region East West North #CEO's 16 57 54 60 57 59 17 16 53 Island NY Total 62 65

3. Select the table we created in 2nd part --> Go to insert tab --> Select pie chart option from Charts

No. Of CEO in US Regions 30% 80% 896 32% EastWest North South