Create the named ranges listed in the table below.
Industry Name $F$3:$F$6054
Revenue $K$3:$K$6054
Group Name $N$4:$N$71
Total Revenue $Q$4:$Q$71
Overall Quartile $S$4:$S$71
Overall Rank Quartile $V$4:$W$7
Industry Name Total Revenue Rank Quartile $P$4:$S$71
A. For each industry name, calculate total revenue. Use a SUMIF function that includes the named ranges Industry_Name and Revenue.
B. In the Overall Rank column, assign a rank to each industry based on its total revenue. Use a RANK.EQ function that includes the named range Total_Revenue. Rank the list in descending order. For example, the industry that has the most total revenue has a rank of 1, and the industry that has the most total revenue has a rank of 68.
C. Use VLOOKUP to assign each industry to a quartile based on its rank. Use the named range Overall_Rank_Quartile.
D. For each industry, use COUNTIF to count the number of firms that compose the industry.
E. Use VLOOKUP and the named range Industry_Name_Total_Revenue_Rank_Quartile to find the total revenue, overall rank, and quartile for the three industries listed in table E.
F. Use SUMIFS and the named ranges Group_Name, Total_Revenue, and Overall_Quartile to calculate the total revenue for a given quartile and industry group.
8 SUMIF RANKEQ VLOOKUP COUNTIF QuartleF Overall Rank Rank 1010 Aerospace and Defense 03010 Air Freight and Logistics 203020 Airnes 2030 Transportation 7 2510 Autonobiles and Components 251010 Auto Components 8 2510 Automobiles and Components 251020 Automobiles 9 4010 Banks 401010 Banks 10 3020 Food, Beverage and Tobacco 302010 Beverages 11 3520 Pharmaceuticals, Biotechnolog 352010 Biotechnology 201020 Buiding Products 402030 Capital Markets 151010 Chemica's 2010 Capital Goods 13 4020 Diversified Financias 14 1510 Materials 15 2020 Commercial and Professional 202010 Comercial Services and Supplies 16 4520 Technology Hardware and Equ 452010 Communications Equipment Distri Construction and Engineeing 2010 Capital Goods 1510 Materials 01030 402020 Consumer Finance 151030 Containers and Packaging 255010 Dstnbutors 253020 Diversified Consumer Services 402010 Diversified Financial Services 19 4020 Diversified Financials 20 1510 Materials Capital Goods Commercial and Professional Services 21 2550 Retailing 22 2530 Consumer Services 23 4020 Diversified Financials 24 5010 25 5510 Utilties Automobiles and Com Consumer Durables and Services 501010 Diversified Telecommunication Services 551010 Electric Utilities 26 2010 Capital Goods201040 Electrical Equipment 27 4520 Technology Hardware and Equ 452030 Electronic Equipment, Instruments and Components 28 1010 Energy 29 6010 Real Estate 30 3010 Food and Stapies Reta ing 301010 Food and Staples Reta ing 31 3020 Food, Beverage and Tobacco 302020 Food Products 101010 01010 Energy Equipment and Services Equity Real Estate Investment Trusts (REITS Retail Food and Food, Beverage and Tobacco Health Care E t and Services 5510 Utilities 510 Health Care Equipment and Se 351010 3510 Health Care Equipment and Se 351020 3510 Health Care Equipment and Se 351030 551020 Gas Utlities and L Health Care Equipment and Suppies Health Care Providers and Services Health Care Technology Hotels, Restaurants and Leisure Household Durables Household Products Care fd Lesure 36 2530 Consumer Services 37 2520 Consumer Durables and Appar 252010 38 3030 Household and Personal Produ 303010 39 5510 Utilties 40 2010 Capital Goods 41 4030 Insurance 42 2550 Retailing 253010 IndustriaRenewable Intemet and Direct Marketing Retail Technology Hardware and E Telecommun cation Services Real Estate 51050 Independent Power and Renewable Electricity Producers 050 255020 8 SUMIF RANKEQ VLOOKUP COUNTIF QuartleF Overall Rank Rank 1010 Aerospace and Defense 03010 Air Freight and Logistics 203020 Airnes 2030 Transportation 7 2510 Autonobiles and Components 251010 Auto Components 8 2510 Automobiles and Components 251020 Automobiles 9 4010 Banks 401010 Banks 10 3020 Food, Beverage and Tobacco 302010 Beverages 11 3520 Pharmaceuticals, Biotechnolog 352010 Biotechnology 201020 Buiding Products 402030 Capital Markets 151010 Chemica's 2010 Capital Goods 13 4020 Diversified Financias 14 1510 Materials 15 2020 Commercial and Professional 202010 Comercial Services and Supplies 16 4520 Technology Hardware and Equ 452010 Communications Equipment Distri Construction and Engineeing 2010 Capital Goods 1510 Materials 01030 402020 Consumer Finance 151030 Containers and Packaging 255010 Dstnbutors 253020 Diversified Consumer Services 402010 Diversified Financial Services 19 4020 Diversified Financials 20 1510 Materials Capital Goods Commercial and Professional Services 21 2550 Retailing 22 2530 Consumer Services 23 4020 Diversified Financials 24 5010 25 5510 Utilties Automobiles and Com Consumer Durables and Services 501010 Diversified Telecommunication Services 551010 Electric Utilities 26 2010 Capital Goods201040 Electrical Equipment 27 4520 Technology Hardware and Equ 452030 Electronic Equipment, Instruments and Components 28 1010 Energy 29 6010 Real Estate 30 3010 Food and Stapies Reta ing 301010 Food and Staples Reta ing 31 3020 Food, Beverage and Tobacco 302020 Food Products 101010 01010 Energy Equipment and Services Equity Real Estate Investment Trusts (REITS Retail Food and Food, Beverage and Tobacco Health Care E t and Services 5510 Utilities 510 Health Care Equipment and Se 351010 3510 Health Care Equipment and Se 351020 3510 Health Care Equipment and Se 351030 551020 Gas Utlities and L Health Care Equipment and Suppies Health Care Providers and Services Health Care Technology Hotels, Restaurants and Leisure Household Durables Household Products Care fd Lesure 36 2530 Consumer Services 37 2520 Consumer Durables and Appar 252010 38 3030 Household and Personal Produ 303010 39 5510 Utilties 40 2010 Capital Goods 41 4030 Insurance 42 2550 Retailing 253010 IndustriaRenewable Intemet and Direct Marketing Retail Technology Hardware and E Telecommun cation Services Real Estate 51050 Independent Power and Renewable Electricity Producers 050 255020
