Assignment Prompt & Instructions
– ## Instructionsofproject.gdoc
– IMG_1252.jpeg
– IMG_1253.jpeg
**## Instructionsofproject.gdoc**
Using Spreadsheets to Gather and
Formulate Macroeconomic Data (Submit
assignment as a PDF)
PDF available for printing
There are three parts to this assignment (A, B & C). After each set of
instructions, a video demonstration is provided.
Getting Started:
● This assignment utilizes Google Drive and Google Spreadsheets. If you do not
have Google Drive on your desktop, please follow the How to Download Google
Drive instructions before continuing.
● If you use Excel regularly, you may convert the Google spreadsheet to an Excel
spreadsheet.
● Please note that the directions refer to Google commands on a Chromebook or
PC, which may differ from those in Excel or on a MacBook. Your use of either
assumes that you understand and can use those functions in those formats.
● Once you are in this assignment, open the Macro Data Spreadsheet that has
been shared with you.
● STOP AND MAKE A COPY of the Google Spreadsheet before changing or
adding anything to the spreadsheet.
○ To make a copy, go to File>Make a Copy.
○ At the top of your new spreadsheet, below the title, add your name – "first name
last name”.
● You can make changes to your spreadsheet by opening it in Google Drive.
● Once you have submitted the assignment, any changes you make will require you to
resubmit it.
A. Gathering Data, Step-by-Step Instructions:
You will gather data for all columns on the spreadsheet in black font. You will formulate
data for all columns in blue font. Instructions are clustered below by websites. The data
links are embedded in the step-by-step instructions and listed below.
[https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1](https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing)
1.Nominal GDP/billion
1. GDP Deflator Index
2. Population/thousands
3. Unemployment rate
4. Consumer Price Index
Nominal GDP and GDP Deflator
● Open the Nominal GDP link.
● When the web page opens, above the chart on the right-hand side, change the
given dates to range from 1980-01-01 to 2024-01-01.
● Click Download in the upper right corner> Excel (data)
● When Excel opens, at the bottom of the sheet, click ANNUAL to see the data.
Then click Enable Editing (at the top of the page), select all the data (not the
titles) in both columns (dates and GDPA),and copy it.
● Open your Google Sheet template, select the first cell under YEAR, and paste
the copied data. Use the Special Paste option>paste values only.
● Open the GDP Deflator link
● When the web page opens, above the chart on the right-hand side, change the
date range to 1980-01-01 to 2024-01-01.
● Click on Edit Graph, then in Modify frequency > Annual and in Aggregation
method > Average.
● Follow the remaining steps for NGDP above, except copy only the deflator
data, not the years. Use the Special Paste option>paste values only
Population
● Open the Population link.
● For Population, you will follow the instructions for Nominal GDP and GDP
Deflator EXCEPT once you have downloaded the data to Excel (#1-3), BEFORE
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
you copy and paste it on your sheet (#4), you must convert the data from millions
to billions. (We are doing this now to make calculating Real GDP per capita
easier)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
○ BEFORE transferring this data to your Google sheet, in the column next to
the data on the first row of the data, type in = (this tells Google Sheets
that you are inserting a formula), then type the following formula as-is:
b12/1000000 (6 zeroes). Please note that"b12" identifies the first cell in
your Nominal GDP column.
○ Hit Enter. The converted population number will appear.
○ Hover the mouse over that cell to find the black+ symbol in the bottom
right corner of that cell and drag to the bottom of the column to copy the
formula and populate data in all the cells.
● Highlight and copy the last column (the one you just formulated).
● Go to your Google Sheet and paste it in the Population column. Use the Special
Paste option>paste values only.
Unemployment
● Open the Unemployment link.
● At the top of the page, change the dates to 1980 to 2024, uncheck “Include
Graphs,” and check “Include Annual Averages.” Click GO.
● Scroll down to data and above the schedule, click "Download.xslx".
● When the spreadsheet opens, click on Enable editing.
● You will not copy all of this into your spreadsheet. You only need the annual
average. Since those did not self-populate, you will have Excel calculate those
for you.
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
● In the first cell (1980 data) of the last column under Annual, type in the following
formula as-is: =Average(b13:m13). Hit Enter, and the yearly average of the
monthly unemployment rates will be entered into the cell. (Note: Instead of typing
in the letter and number of the cell, you can click on the cell that will be used for
the calculation.)
● In the bottom right corner of the cell with the new data, hover over it until you find
the black + symbol, then drag the black + symbol to the bottom of the column
to copy the formula and populate the data.
● BEFORE transferring this data to your Google sheet, in the cell to the left of the
first row of the Annual column, type in the following formula:=n13/100. This
converts a percentage to a decimal (to standardize how the data is formatted).
● In the bottom right corner of the cell, find the black + symbol, then drag it to the
bottom of the column to copy the formula and populate the data.
● Highlight and copy only the data you just created.
● Go to your Google Sheet, and paste in the first cell in the Unemployment Annual
Average Rate column. Use the Special Paste option>paste values only.
Consumer Price Index
● Open the Consumer Price Index link,
● Click on "More Formatting Options".
● Leave everything in the "Select view of the data" box the same.
● In the "Select the time frame for your data" box, set the years to 1980-2024.
● Click on "Select one time period" and choose Annual Data.
● Uncheck "Include graphs".
● Leave everything else the same and click on "Retrieve Data".
● Highlight and copy the Annual column, then paste it into the Consumer Price
Index column. Use the Special Paste option>paste values only.
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
. Using Formulas to Create New Data, Step-by-Step Instructions:
● You are now ready to use the data to formulate data for your blue columns. You
will use the formula feature of your Google Sheet to do the calculations for you.
However, you must tell it what to calculate by inserting a correct formula.
○ You will use your “Macroeconomic Formulas” handout for the basic
formulas.
Follow these directions to calculate the NGDP growth rate and use this example for the
formula function for your other calculations that ask for a % change.
● Starting in the NGDP growth rate column, in the second row (1981), below the
title type =(this tells Google Sheets that you are inserting a formula). Then tell
Google Sheets the formula to use, replacing values with cell letters and numbers
(see example below). Hit Enter to populate the cell.
○ On your Macroeconomic Formula sheet, you have formulas for Economic
Growth Rate and Percent Change. These are the same formulas. You will
use these steps for any data set that is a " % change".
● To calculate the NGDP Growth Rate for 1981, use the formula: (Current year
GDP – Previous year GDP) / Previous year GDP. Do NOT multiply by 100.
Instead of converting the decimal values to percentages, we will have the
spreadsheet perform the conversion during Cleanup.
● Start on the row for 1981 in the NGDP Growth Rate column. (Note: We cannot
calculate the growth rate for 1980 because we do not have data for 1979.)
● Using the data cells on your sheet, type in =(b6-b5)/b5. These letters and
numbers reflect the second & first cells in your Nominal GDP column. (Note:
Instead of typing in the letter and number of the cell, you can click on the cell that
will be used for the calculation.)
[https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1](https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1)
[https://canvas.tccd.edu/courses/144870/files/24469895/download](https://canvas.tccd.edu/courses/144870/files/24469895/download)
● Populate the entire column by finding the black + symbol in the bottom right
corner of the cell you formulated and dragging it down to the bottom of the
column. This will copy the formula.
● At this point, you have used the formula feature for several calculations. Repeat
those steps for all required calculations using the appropriate formulas from your
formula sheet with the appropriate data on your sheet.
● The first row of any rate-of-change/growth-rate column will NOT be calculated,
as there is no previous data to calculate it. You will start with the 2nd row on all
of these.
● To calculate Real GDP, use the formula from your formula sheet that employs the
GDP Deflator.
● If you are still unsure about doing this, watch the video demonstration.
● All columns, including the titles, should be formatted from the right margin.
● Adjust the cell size so the column titles are legible.
● The font type and size should be the same throughout.
○ Highlight all your data. Choose a font size and type.
● Check that you have data for every year listed except the "% Change" columns
for 1980.
● Check that you do not have a value of 100 at the bottom of any of your growth
rate or rate of change columns. If you do, it is because you did not skip the first
row of those columns. To correct, cut and paste everything in that column, one
row down.
●
– Using Spreadsheets to Gather and Formulate Macroeconomic Data (Submit assignment as a PDF)
– Getting Started:
– A. Gathering Data, Step-by-Step Instructions:
– . Using Formulas to Create New Data, Step-by-Step Instructions:
**IMG_1252.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1252-jpeg-10468521)
**IMG_1253.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1253-jpeg-10468523)
**IMG_1253.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1253-jpeg-10468523)
**## Instructionsofproject.gdoc**
Using Spreadsheets to Gather and
Formulate Macroeconomic Data (Submit
assignment as a PDF)
PDF available for printing
There are three parts to this assignment (A, B & C). After each set of
instructions, a video demonstration is provided.
Getting Started:
● This assignment utilizes Google Drive and Google Spreadsheets. If you do not
have Google Drive on your desktop, please follow the How to Download Google
Drive instructions before continuing.
● If you use Excel regularly, you may convert the Google spreadsheet to an Excel
spreadsheet.
● Please note that the directions refer to Google commands on a Chromebook or
PC, which may differ from those in Excel or on a MacBook. Your use of either
assumes that you understand and can use those functions in those formats.
● Once you are in this assignment, open the Macro Data Spreadsheet that has
been shared with you.
● STOP AND MAKE A COPY of the Google Spreadsheet before changing or
adding anything to the spreadsheet.
○ To make a copy, go to File>Make a Copy.
○ At the top of your new spreadsheet, below the title, add your name – "first name
last name”.
● You can make changes to your spreadsheet by opening it in Google Drive.
● Once you have submitted the assignment, any changes you make will require you to
resubmit it.
A. Gathering Data, Step-by-Step Instructions:
You will gather data for all columns on the spreadsheet in black font. You will formulate
data for all columns in blue font. Instructions are clustered below by websites. The data
links are embedded in the step-by-step instructions and listed below.
[https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1](https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing)
1.Nominal GDP/billion
1. GDP Deflator Index
2. Population/thousands
3. Unemployment rate
4. Consumer Price Index
Nominal GDP and GDP Deflator
● Open the Nominal GDP link.
● When the web page opens, above the chart on the right-hand side, change the
given dates to range from 1980-01-01 to 2024-01-01.
● Click Download in the upper right corner> Excel (data)
● When Excel opens, at the bottom of the sheet, click ANNUAL to see the data.
Then click Enable Editing (at the top of the page), select all the data (not the
titles) in both columns (dates and GDPA),and copy it.
● Open your Google Sheet template, select the first cell under YEAR, and paste
the copied data. Use the Special Paste option>paste values only.
● Open the GDP Deflator link
● When the web page opens, above the chart on the right-hand side, change the
date range to 1980-01-01 to 2024-01-01.
● Click on Edit Graph, then in Modify frequency > Annual and in Aggregation
method > Average.
● Follow the remaining steps for NGDP above, except copy only the deflator
data, not the years. Use the Special Paste option>paste values only
Population
● Open the Population link.
● For Population, you will follow the instructions for Nominal GDP and GDP
Deflator EXCEPT once you have downloaded the data to Excel (#1-3), BEFORE
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
you copy and paste it on your sheet (#4), you must convert the data from millions
to billions. (We are doing this now to make calculating Real GDP per capita
easier)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
○ BEFORE transferring this data to your Google sheet, in the column next to
the data on the first row of the data, type in = (this tells Google Sheets
that you are inserting a formula), then type the following formula as-is:
b12/1000000 (6 zeroes). Please note that"b12" identifies the first cell in
your Nominal GDP column.
○ Hit Enter. The converted population number will appear.
○ Hover the mouse over that cell to find the black+ symbol in the bottom
right corner of that cell and drag to the bottom of the column to copy the
formula and populate data in all the cells.
● Highlight and copy the last column (the one you just formulated).
● Go to your Google Sheet and paste it in the Population column. Use the Special
Paste option>paste values only.
Unemployment
● Open the Unemployment link.
● At the top of the page, change the dates to 1980 to 2024, uncheck “Include
Graphs,” and check “Include Annual Averages.” Click GO.
● Scroll down to data and above the schedule, click "Download.xslx".
● When the spreadsheet opens, click on Enable editing.
● You will not copy all of this into your spreadsheet. You only need the annual
average. Since those did not self-populate, you will have Excel calculate those
for you.
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
● In the first cell (1980 data) of the last column under Annual, type in the following
formula as-is: =Average(b13:m13). Hit Enter, and the yearly average of the
monthly unemployment rates will be entered into the cell. (Note: Instead of typing
in the letter and number of the cell, you can click on the cell that will be used for
the calculation.)
● In the bottom right corner of the cell with the new data, hover over it until you find
the black + symbol, then drag the black + symbol to the bottom of the column
to copy the formula and populate the data.
● BEFORE transferring this data to your Google sheet, in the cell to the left of the
first row of the Annual column, type in the following formula:=n13/100. This
converts a percentage to a decimal (to standardize how the data is formatted).
● In the bottom right corner of the cell, find the black + symbol, then drag it to the
bottom of the column to copy the formula and populate the data.
● Highlight and copy only the data you just created.
● Go to your Google Sheet, and paste in the first cell in the Unemployment Annual
Average Rate column. Use the Special Paste option>paste values only.
Consumer Price Index
● Open the Consumer Price Index link,
● Click on "More Formatting Options".
● Leave everything in the "Select view of the data" box the same.
● In the "Select the time frame for your data" box, set the years to 1980-2024.
● Click on "Select one time period" and choose Annual Data.
● Uncheck "Include graphs".
● Leave everything else the same and click on "Retrieve Data".
● Highlight and copy the Annual column, then paste it into the Consumer Price
Index column. Use the Special Paste option>paste values only.
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
. Using Formulas to Create New Data, Step-by-Step Instructions:
● You are now ready to use the data to formulate data for your blue columns. You
will use the formula feature of your Google Sheet to do the calculations for you.
However, you must tell it what to calculate by inserting a correct formula.
○ You will use your “Macroeconomic Formulas” handout for the basic
formulas.
Follow these directions to calculate the NGDP growth rate and use this example for the
formula function for your other calculations that ask for a % change.
● Starting in the NGDP growth rate column, in the second row (1981), below the
title type =(this tells Google Sheets that you are inserting a formula). Then tell
Google Sheets the formula to use, replacing values with cell letters and numbers
(see example below). Hit Enter to populate the cell.
○ On your Macroeconomic Formula sheet, you have formulas for Economic
Growth Rate and Percent Change. These are the same formulas. You will
use these steps for any data set that is a " % change".
● To calculate the NGDP Growth Rate for 1981, use the formula: (Current year
GDP – Previous year GDP) / Previous year GDP. Do NOT multiply by 100.
Instead of converting the decimal values to percentages, we will have the
spreadsheet perform the conversion during Cleanup.
● Start on the row for 1981 in the NGDP Growth Rate column. (Note: We cannot
calculate the growth rate for 1980 because we do not have data for 1979.)
● Using the data cells on your sheet, type in =(b6-b5)/b5. These letters and
numbers reflect the second & first cells in your Nominal GDP column. (Note:
Instead of typing in the letter and number of the cell, you can click on the cell that
will be used for the calculation.)
[https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1](https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1)
[https://canvas.tccd.edu/courses/144870/files/24469895/download](https://canvas.tccd.edu/courses/144870/files/24469895/download)
● Populate the entire column by finding the black + symbol in the bottom right
corner of the cell you formulated and dragging it down to the bottom of the
column. This will copy the formula.
● At this point, you have used the formula feature for several calculations. Repeat
those steps for all required calculations using the appropriate formulas from your
formula sheet with the appropriate data on your sheet.
● The first row of any rate-of-change/growth-rate column will NOT be calculated,
as there is no previous data to calculate it. You will start with the 2nd row on all
of these.
● To calculate Real GDP, use the formula from your formula sheet that employs the
GDP Deflator.
● If you are still unsure about doing this, watch the video demonstration.
● All columns, including the titles, should be formatted from the right margin.
● Adjust the cell size so the column titles are legible.
● The font type and size should be the same throughout.
○ Highlight all your data. Choose a font size and type.
● Check that you have data for every year listed except the "% Change" columns
for 1980.
● Check that you do not have a value of 100 at the bottom of any of your growth
rate or rate of change columns. If you do, it is because you did not skip the first
row of those columns. To correct, cut and paste everything in that column, one
row down.
●
– Using Spreadsheets to Gather and Formulate Macroeconomic Data (Submit assignment as a PDF)
– Getting Started:
– A. Gathering Data, Step-by-Step Instructions:
– . Using Formulas to Create New Data, Step-by-Step Instructions:
**IMG_1252.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1252-jpeg-10468521)
**IMG_1253.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1253-jpeg-10468523)
**## Instructionsofproject.gdoc**
Using Spreadsheets to Gather and
Formulate Macroeconomic Data (Submit
assignment as a PDF)
PDF available for printing
There are three parts to this assignment (A, B & C). After each set of
instructions, a video demonstration is provided.
Getting Started:
● This assignment utilizes Google Drive and Google Spreadsheets. If you do not
have Google Drive on your desktop, please follow the How to Download Google
Drive instructions before continuing.
● If you use Excel regularly, you may convert the Google spreadsheet to an Excel
spreadsheet.
● Please note that the directions refer to Google commands on a Chromebook or
PC, which may differ from those in Excel or on a MacBook. Your use of either
assumes that you understand and can use those functions in those formats.
● Once you are in this assignment, open the Macro Data Spreadsheet that has
been shared with you.
● STOP AND MAKE A COPY of the Google Spreadsheet before changing or
adding anything to the spreadsheet.
○ To make a copy, go to File>Make a Copy.
○ At the top of your new spreadsheet, below the title, add your name – "first name
last name”.
● You can make changes to your spreadsheet by opening it in Google Drive.
● Once you have submitted the assignment, any changes you make will require you to
resubmit it.
A. Gathering Data, Step-by-Step Instructions:
You will gather data for all columns on the spreadsheet in black font. You will formulate
data for all columns in blue font. Instructions are clustered below by websites. The data
links are embedded in the step-by-step instructions and listed below.
[https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1](https://canvas.tccd.edu/courses/144870/files/24469893?wrap=1)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://support.google.com/a/users/answer/13022292?hl=en](https://support.google.com/a/users/answer/13022292?hl=en)
[https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1F9j7MqgLMwDjZmvjg5LcCmm72EgskXkTR3nMCCt1-Pc/edit?usp=sharing)
1.Nominal GDP/billion
1. GDP Deflator Index
2. Population/thousands
3. Unemployment rate
4. Consumer Price Index
Nominal GDP and GDP Deflator
● Open the Nominal GDP link.
● When the web page opens, above the chart on the right-hand side, change the
given dates to range from 1980-01-01 to 2024-01-01.
● Click Download in the upper right corner> Excel (data)
● When Excel opens, at the bottom of the sheet, click ANNUAL to see the data.
Then click Enable Editing (at the top of the page), select all the data (not the
titles) in both columns (dates and GDPA),and copy it.
● Open your Google Sheet template, select the first cell under YEAR, and paste
the copied data. Use the Special Paste option>paste values only.
● Open the GDP Deflator link
● When the web page opens, above the chart on the right-hand side, change the
date range to 1980-01-01 to 2024-01-01.
● Click on Edit Graph, then in Modify frequency > Annual and in Aggregation
method > Average.
● Follow the remaining steps for NGDP above, except copy only the deflator
data, not the years. Use the Special Paste option>paste values only
Population
● Open the Population link.
● For Population, you will follow the instructions for Nominal GDP and GDP
Deflator EXCEPT once you have downloaded the data to Excel (#1-3), BEFORE
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
[https://fred.stlouisfed.org/series/GDPA](https://fred.stlouisfed.org/series/GDPA)
[https://fred.stlouisfed.org/series/GDPDEF](https://fred.stlouisfed.org/series/GDPDEF)
[https://fred.stlouisfed.org/series/B230RC0A052NBEA](https://fred.stlouisfed.org/series/B230RC0A052NBEA)
you copy and paste it on your sheet (#4), you must convert the data from millions
to billions. (We are doing this now to make calculating Real GDP per capita
easier)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
○ BEFORE transferring this data to your Google sheet, in the column next to
the data on the first row of the data, type in = (this tells Google Sheets
that you are inserting a formula), then type the following formula as-is:
b12/1000000 (6 zeroes). Please note that"b12" identifies the first cell in
your Nominal GDP column.
○ Hit Enter. The converted population number will appear.
○ Hover the mouse over that cell to find the black+ symbol in the bottom
right corner of that cell and drag to the bottom of the column to copy the
formula and populate data in all the cells.
● Highlight and copy the last column (the one you just formulated).
● Go to your Google Sheet and paste it in the Population column. Use the Special
Paste option>paste values only.
Unemployment
● Open the Unemployment link.
● At the top of the page, change the dates to 1980 to 2024, uncheck “Include
Graphs,” and check “Include Annual Averages.” Click GO.
● Scroll down to data and above the schedule, click "Download.xslx".
● When the spreadsheet opens, click on Enable editing.
● You will not copy all of this into your spreadsheet. You only need the annual
average. Since those did not self-populate, you will have Excel calculate those
for you.
[https://data.bls.gov/timeseries/LNS14000000](https://data.bls.gov/timeseries/LNS14000000)
○ If using a Chromebook, go to File>Save as a Google Sheet before
continuing.
● In the first cell (1980 data) of the last column under Annual, type in the following
formula as-is: =Average(b13:m13). Hit Enter, and the yearly average of the
monthly unemployment rates will be entered into the cell. (Note: Instead of typing
in the letter and number of the cell, you can click on the cell that will be used for
the calculation.)
● In the bottom right corner of the cell with the new data, hover over it until you find
the black + symbol, then drag the black + symbol to the bottom of the column
to copy the formula and populate the data.
● BEFORE transferring this data to your Google sheet, in the cell to the left of the
first row of the Annual column, type in the following formula:=n13/100. This
converts a percentage to a decimal (to standardize how the data is formatted).
● In the bottom right corner of the cell, find the black + symbol, then drag it to the
bottom of the column to copy the formula and populate the data.
● Highlight and copy only the data you just created.
● Go to your Google Sheet, and paste in the first cell in the Unemployment Annual
Average Rate column. Use the Special Paste option>paste values only.
Consumer Price Index
● Open the Consumer Price Index link,
● Click on "More Formatting Options".
● Leave everything in the "Select view of the data" box the same.
● In the "Select the time frame for your data" box, set the years to 1980-2024.
● Click on "Select one time period" and choose Annual Data.
● Uncheck "Include graphs".
● Leave everything else the same and click on "Retrieve Data".
● Highlight and copy the Annual column, then paste it into the Consumer Price
Index column. Use the Special Paste option>paste values only.
[https://data.bls.gov/timeseries/CUUR0000SA0](https://data.bls.gov/timeseries/CUUR0000SA0)
. Using Formulas to Create New Data, Step-by-Step Instructions:
● You are now ready to use the data to formulate data for your blue columns. You
will use the formula feature of your Google Sheet to do the calculations for you.
However, you must tell it what to calculate by inserting a correct formula.
○ You will use your “Macroeconomic Formulas” handout for the basic
formulas.
Follow these directions to calculate the NGDP growth rate and use this example for the
formula function for your other calculations that ask for a % change.
● Starting in the NGDP growth rate column, in the second row (1981), below the
title type =(this tells Google Sheets that you are inserting a formula). Then tell
Google Sheets the formula to use, replacing values with cell letters and numbers
(see example below). Hit Enter to populate the cell.
○ On your Macroeconomic Formula sheet, you have formulas for Economic
Growth Rate and Percent Change. These are the same formulas. You will
use these steps for any data set that is a " % change".
● To calculate the NGDP Growth Rate for 1981, use the formula: (Current year
GDP – Previous year GDP) / Previous year GDP. Do NOT multiply by 100.
Instead of converting the decimal values to percentages, we will have the
spreadsheet perform the conversion during Cleanup.
● Start on the row for 1981 in the NGDP Growth Rate column. (Note: We cannot
calculate the growth rate for 1980 because we do not have data for 1979.)
● Using the data cells on your sheet, type in =(b6-b5)/b5. These letters and
numbers reflect the second & first cells in your Nominal GDP column. (Note:
Instead of typing in the letter and number of the cell, you can click on the cell that
will be used for the calculation.)
[https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1](https://canvas.tccd.edu/courses/144870/files/26451416?wrap=1)
[https://canvas.tccd.edu/courses/144870/files/24469895/download](https://canvas.tccd.edu/courses/144870/files/24469895/download)
● Populate the entire column by finding the black + symbol in the bottom right
corner of the cell you formulated and dragging it down to the bottom of the
column. This will copy the formula.
● At this point, you have used the formula feature for several calculations. Repeat
those steps for all required calculations using the appropriate formulas from your
formula sheet with the appropriate data on your sheet.
● The first row of any rate-of-change/growth-rate column will NOT be calculated,
as there is no previous data to calculate it. You will start with the 2nd row on all
of these.
● To calculate Real GDP, use the formula from your formula sheet that employs the
GDP Deflator.
● If you are still unsure about doing this, watch the video demonstration.
● All columns, including the titles, should be formatted from the right margin.
● Adjust the cell size so the column titles are legible.
● The font type and size should be the same throughout.
○ Highlight all your data. Choose a font size and type.
● Check that you have data for every year listed except the "% Change" columns
for 1980.
● Check that you do not have a value of 100 at the bottom of any of your growth
rate or rate of change columns. If you do, it is because you did not skip the first
row of those columns. To correct, cut and paste everything in that column, one
row down.
●
– Using Spreadsheets to Gather and Formulate Macroeconomic Data (Submit assignment as a PDF)
– Getting Started:
– A. Gathering Data, Step-by-Step Instructions:
– . Using Formulas to Create New Data, Step-by-Step Instructions:
**IMG_1252.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1252-jpeg-10468521)
**IMG_1253.jpeg**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/img-1253-jpeg-10468523)
– 1
– 2
– 3