| 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
How to get your data into your year worksheets -
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)
Create a Pie Chart -
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
Share with your friends: |
