Sinusoidal Graphs
We will be investigating sine and cosine graphs in the “real world” by looking at how the amount of daylight varies over a 2 year period.
Go to the website
Choose a country, state and city. Make sure you have chosen a different location than your neighbor. We want the class to have as much location variety as possible.
Select the year 2010, leave the month and date the same.
Click “Show the graph!”
Click on the first graph “Sunrise, sunset and time of light”
Click “open”
Save the spreadsheet.
Repeat the steps above for the year 2011, leave the month and date the same.
Copy the data for 2011 below the data for 2010. Do not leave any blank rows, do not copy any text.
Save the spreadsheet.
Repeat the steps above for the year 2012, leave the month and date the same
Copy the data for 2012 below the data for 2011. Do not leave any blank rows, do not copy any text.
Save the spreadsheet
Rename the sheet with the city, state and country of your data.
Delete columns B and C (when you are finished you should only have two columns: “Date” and “Daylength”
Press ctrl+F. Click the “replace” tab. In the space next to “Find What” Type a quotation mark (“). Leave the “Replace with” blank. Click “Replace All” button.
Column A might have some cells that have ####. This is because Column A is not wide enough to show the entire date. Make Column A wide enough so there are no “####” cells.
STOP….make sure everyone around you is to this point before proceeding.
Now we’re ready to graph
Click on “Insert” at the top of Excel. Then click on the arrow below “Scatter”, then choose one of the graphs with a curved line connecting (there are two options).
Click the “Move Chart” Button. Click “New Sheet” Click on “OK”.
Click the “Select Data”. Click delete. If there are already things in the box, click Remove.
Now click “Add”.
Click “OK” again
You should have a sinusoidal graph. Because I had you change the colons (:) to decimals (.) Your graph will look a little funny, but we’ll be able to work with it anyway.
Save again.
Now we’re going write a sinusoidal function to model this data.
So we can come up with a sinusoidal function, we are going to add a columns with numbers corresponding to the date much like we did in the “Applications of Sinusoidal Functions” worksheet.
Click on the “B” in column B. Then right click and hit insert. This should insert a column between “Date” and “Daylength”.
In cell B2 put a 1 this will represent the first day of our data. Put a 2 in the next row.
To fill the rest of the column with numbers representing the day since the first date of our data:
Select both B2 and B3.
Make sure the numbers are sequential and don’t repeat 1,2,1,2 or 1,1,1,1
Now go back to the “Now we’re ready to graph” section. Repeat those steps except instead of using column “A” as the series “X” you are going to use column “B”
If you have done these steps correctly, you will have a graph with numbers at the bottom instead of dates.
Save.
Use the graph and data to determine a sinusoidal function for this data.
Amplitude
In cell G1, write “a”. You will calculate the amplitude in cell H1
First find the cell that contains a maximum and then the cell that contains the minimum.
In cell H1, type =(max-min)/2 it should look something like this
When calculating amplitude, do not use the cells I used, they may not be the same as yours
Save
Period
In cell G2, write “b”. You will calculate “b” in cell H2.
First find the period. Then in cell H2, type =2*pi()/period. It should look something like this
Save
Horizontal Shift
In cell G3, write “c”. You will calculate “c” in cell H3
Determine how far the graph shifts.
If your horizontal shift is zero, type 0 and then move to the next step.
If the horizontal shift is not zero, in cell H3, type =horizontal shift*H2. It should look something like this
Save
Vertical Shift
In cell G4, write “d”. You will calculate “d” in cell H4.
In cell H4, type =min+H1 or =max-H1. It should look something like this
When calculating vertical shift, the cell in column “C” might not be the same as yours
Save
Now we can type in the function
In cell D1, type “Calculated Daylength”
In cell D2, type in something like this
The dollar signs make sure it does not change cells when you copy the formula. Make sure you do not put dollar signs around the B2
Now we will copy the formula down to the rest of the column.
Save
Now we graph the calculated daylength and hope it looks like the actual!
Click on the chart with the numbers at the bottom. Click “Select Data”
Change the chart name to Amount of Daylight in City, State, Country
To add the calculated daylength to your graph, go back to the “Now we’re ready to graph” section, start with the sentence “Now click “Add”” (Don’t delete or remove anything)
Series name: D1
X-Series: Data in column B
Y-Series: Data in column D
Save
Print the graph.
Write the sinusoidal function on the graph.
Now where in the world in your data from
Get a world map from Ms. Parker. Put an “x” or circle on the location of your chosen location. Get as close as possible, using the longitude and latitude as guides. Write the name of the city, state, country close to the “x” or circle
Write your name and number on the back of both sheets