50 State Quarters Program Production
In this project you will be taking a whole bunch of raw data and turning it into useful information.
In 1999 the government started to print quarters that had reference to one of the 50 states on the back. They became instant collectors’ items. Every year they produced 5 states, making in a 10 year project. They were so popular that at the end of the 10 year project, they started to make new quarter backs with different landmarks and events.
Project Steps:

Open the Minted Coins Project

Rename the Worksheet 50 States

Create a new Worksheet for each year and name it by the year. 1999, 2000, 2001, etc

Insert a row at Row 1

Insert another row ( You should now have two blank rows above the column headings “Year, Design, Denver, Philadelphia”

Merge A1:H2

Name your new Cell “50 State Quarters Program Production”

Wrap the Text and apply a new font and size (at least 26 size)

In E3 add the text, “Difference of Coins Printed in Denver”

In F3 add the text, “Total Quarters Produced”

In G3 add the text “Total by Year”

In H3 add the Text “% of Total”

Highlight the range A3:H3

Apply a bold and size 14 to each cell and center & middle align all of the cells in that range (you may to do it twice for it to apply to all of the headings)

In E3 add text wrap and drag to a width of 16 pixels

In F3 add text wrap and drag to a width of 16 pixels

In G3 add text wrap

In H3 add text wrap

In D3 extend the column width so you can see Philadelphia

Select your data (everything but your column headings)—should be the 2008 Oklahoma row down to the 1999 Connecticut row and sort it from smallest to largest. All of the 1999 years should be at the top, and should now be in chronological order

In this next step you are going to merge and center the 5 cells that are in the year column. So Select all of the 1999’s and Merge and Center the Cells so that there is just a big cell that contains the year. Click OK if you get a message. See the picture for more details.
1999

Connecticut

657,880,000

688,744,000

30,864,000

1,346,624,000

4,430,940,000

12.73%

Delaware

401,424,000

373,400,000

28,024,000

774,824,000

Georgia

488,744,000

451,188,000

37,556,000

939,932,000

New Jersey

299,028,000

363,200,000

64,172,000

662,228,000

Pennsylvania

358,332,000

349,000,000

9,332,000

707,332,000


Do this for all the years 1999 – 2008

Select each year cell and change the font direction so that it is angled counterclockwise and middle aligned. Apply a bold font style and change the font size to 20

In each year, select all of the data (not including column A and sort the states so that they are alphabetized) Do this for each year

Create a formula in E4 that will find the difference of coins that were minted in Denver (You’ll need to subtract Denver – Philadelphia

Copy that formula all the way down to the 2008 states

Create a function in F4 that will add the total number of quarters minted that year

Copy the function all the way down to the 2008 states

Merge the cells G4:G8

Create a function that will add up all of the 1999 total quarters

Middle align the text so that it is in the middle and center of the merged cells

You will have to do this for each year before you copy and paste the function down. Apply the same formatting. Center and middle alignment to all of the years total numbers. If you merge and center all of the total by years cells (the five cells for each year) You can copy down the formula from the first year

Merge and center the five cells in each year for % of total column (H4:H8) Middle align the cells as well

Once you have done all of this you should be ready to find the total number of quarters made for all 10 years and all 50 states. (It will be a big number)

In Cell B54 type in the word Total

Find the total number of quarters produced and put it in cell C54. Continue to fine the total number of quarters for that row. You can copy the formula across with Mrs. Copy’sworth to the G Column and it will add up the totals for each row. Bold everything is this row

In Cell B55 Type in the words “Total in Dollars”

In Cell C55 create a formula that will find the total in dollars. Continue to find that total for that row (Remember that a quarter is 25% of 1. Hint .25 Then apply the currency format to the cells in that row

In cell B56 type in the word Least . In cell C57 find the least amount of quarters produced in one year. Find the least amount for Denver and Philadelphia. Bold everything in that row

In Cell B57 Type in the word Most and find the most quarters produced in one year. Find the most for Denver and Philiadelphia in a given year. Bold everything in this row

In Cell B58 Type in the word Average and find the averages for all the years in Cell C59. Find the averages of Denver and Philadelphia and average for the yearly total. Bold everything in this row

In H4, Once you find the total of all of the quarters produced, you can find the percentage of quarters that each year produced. (Take the total from that year and divide it from the total)

Do this for every year

Reduce the decimal size down to 2 numbers after the decimal

Put an All border on the Data A1:H58

Add a color to your title

Add a color to your headings

Each year needs a different color
Each Year needs to be placed on it’s own worksheet in the workbook. On each of the year worksheets, please add the following headings: 
In Cell A1 type in the words “1999 50 State Quarters Program Production

Merge and center A1:D1

Leave row 2 empty

In A3 Type in the “Design”; In B3 type in “Denver” in C3 type in “Philadelphia” In D3 type in “Total”

Make them all bold and center the text in the cells

In the 50 states worksheet, copy the data in cells for each year. For 1999 it will be these references: B4:D8 and paste it into cells A4:D8 in the 1999 worksheet (For other years find the states and totals that each mint made and paste them into the appropriate years)

Find the total of the two mints and put it in Column D

Type in “Grand total” in C9 Find the Grand total in Cell D9

Type in “Monetary” in C10. Find what the monetary value is in D10 (Total * .25)

Highlight the cells A3:A8 and D3:D8 (You’ll need to use control to select both ranges)

Go to the Insert tab

Click on the Pie Chart in the Charts section. Choose any Pie Chart you want

Place the chart under your data

Where it says “Title” on the chart, change the title to the year of the quarter production

IF you want to change the color of any of the colors you can.
Repeat all of these steps for all the years
Save the assignment: Period_Lastname_firstname_Mintedcoins 