For the past 10 years, five Colorado cities have held a recycling contest to see
ID: 3203950 • Letter: F
Question
For the past 10 years, five Colorado cities have held a recycling contest to see which city does the best job of recycling plastic, glass, and aluminum. Those participating in this year’s contest are Arvada, Centennial, Lakewood, Highlands Ranch, and Parker. To make the contest fair for both large and small cities, the winning city will be the one that recycles the largest number of cans per capita—the number of cans recycled divided by the number of city residents.
You have been asked to help the coordinator, Jill Slater, to compile the numbers in an Excel worksheet and create the formulas to compute the total recycling by city each month, total recycling for all cities each month, and the per capita recycling value that determines the contest winner. In addition, Jill wants to know a few statistics about the monthly re-cycling efforts, including the minimum, average, and maximum number of cans recycled. Jill has provided you with sample data, T2_RecyclingCans_Data.xls. Figure T2.32 shows a sample of what Jill would like to see as a completed worksheet.
h Ter Box Signature Line Herder & Footer object Shapes PivotTable Table Picture Column Line Area Scatter Othe ward Art 2 symbol Charts 2006 Regional Sales Summary 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Totals 4 Northeast $119,700.00 S125, 100.00 $133,400.00 $139,500.00 517,700.00 South 48.900.00 52,000.00 49,600.00 54,500.00 205,000.00 6 Midwest 34,600.00 S 32,200.00 S 47,000.00 45,900.00 59,700.00 S 56.200.00 S 49.000.00 s 52,000.00 51,300.00 208,500.00 West $259.400.00 S250 300.00 S282,000.00 $291,200.00 1,090,900.00 Totals 10 11 $160.000.00 13 $140,000.00 16 5120.000.00 18 is $100,000.00 Norte ast South $80,000.00 23 West 24 $60,000.00 25 26 $40,000.00 29 $20,000.00 30 32 st Quarter 2nd Quarter 3rd Quarter 4th Quarter 33 35 SummaryExplanation / Answer
Following are the formulas :::
Populations Jan Feb Mar Total Percapita City =SUM(E3:G3) =H3/D3 Arvada =SUM(E4:G4) =H4/D4 Contenental =SUM(E5:G5) =H5/D5 Lakewood =SUM(E6:G6) =H6/D6 Highlands Ranch =SUM(E7:G7) =H7/D7 Parker =SUM(E8:G8) =H8/D8 Total =SUM(D3:D8) =SUM(E3:E8) =SUM(F3:F8) =SUM(G3:G8) =SUM(H3:H8) Minimum =MIN(E3:E8) =MIN(F3:F8) =MIN(G3:G8) Average =AVERAGE(E3:E8) =AVERAGE(F3:F8) =AVERAGE(G3:G8) Maximum =MAX(E3:E8) =MAX(F3:F8) =MAX(G3:G8)