WORKING WITH SPREAD SHEETS
Purpose: To gain
experience with electronic spreadsheets by using them is some simple
applications.
Equipment:
Computer with Excel software and Graphical Analysis software.
Procedure: We began by opening the Excel software and creating a simple spreadsheet that calculates values for the function f(x) = A sin(Bx +C). Our constants were A = 5, B = 3 and C = pi/3. We put these values to the right of the spreadsheet and labeled them Amplitude for A, Frequency for B, and Phase for C. Towards the center of the spreadsheet we labeled two columns one with the heading “x” and the other with the heading “f(x)”. The x column was going to be used for our variables of x, while the f(x) was going to be used for the value of our function with the given x value. Our initial x value was 0 and it was inputted right below the x heading. Our desired increment was .1 and our desired range was from 0 to 10 radians. We then started to increment from 0 to 10, the way we did this was by putting the cell number where 0 was with “+.1 “ right below the 0 cell. By doing that the program recognized that we were really performing an addition of the cell with 0 + .1 and inputting this in our desired cell. We then clicked and dragged the corner of the cell for 100 rows, which was equivalent to the range .1 to 10. After having all our x values we were ready to input the function and calculate it for our given x values. Right below the f(x) column we put our function as follows for the constants A, B, and C we used “$”, this is needed so the constants remain the same when incrementing, for the x value we put the cell number of our initial x value which was 0. The Excel software then made the calculation with the x value 0. In the same way we incremented the x, we clicked and dragged the corner of the cell to 100 rows matching our x values the program recognized we wanted to do the calculation for the rest of the x values. The following is a print out of the table produced for the first 23 values.
Procedure: We began by opening the Excel software and creating a simple spreadsheet that calculates values for the function f(x) = A sin(Bx +C). Our constants were A = 5, B = 3 and C = pi/3. We put these values to the right of the spreadsheet and labeled them Amplitude for A, Frequency for B, and Phase for C. Towards the center of the spreadsheet we labeled two columns one with the heading “x” and the other with the heading “f(x)”. The x column was going to be used for our variables of x, while the f(x) was going to be used for the value of our function with the given x value. Our initial x value was 0 and it was inputted right below the x heading. Our desired increment was .1 and our desired range was from 0 to 10 radians. We then started to increment from 0 to 10, the way we did this was by putting the cell number where 0 was with “+.1 “ right below the 0 cell. By doing that the program recognized that we were really performing an addition of the cell with 0 + .1 and inputting this in our desired cell. We then clicked and dragged the corner of the cell for 100 rows, which was equivalent to the range .1 to 10. After having all our x values we were ready to input the function and calculate it for our given x values. Right below the f(x) column we put our function as follows for the constants A, B, and C we used “$”, this is needed so the constants remain the same when incrementing, for the x value we put the cell number of our initial x value which was 0. The Excel software then made the calculation with the x value 0. In the same way we incremented the x, we clicked and dragged the corner of the cell to 100 rows matching our x values the program recognized we wanted to do the calculation for the rest of the x values. The following is a print out of the table produced for the first 23 values.
After having our table ready we highlighted the first twenty
rows and chose the Edit/Copy function.
We then opened our Graphical Analysis software and chose the paste option
on the x column. By doing this the Graphical
Analysis software produced a graph for our given values of x and f(x). Our graph represented what we expected to see
a sine graph. With the Graphical
Analysis software we were able to calculate our constants that we had in our
spreadsheet. This was done with the
Analyze/Curve fit tool within the program.
This tool was very accurate it calculated our A and B values exactly our
C value was only of by .52. Below is
print out image of our graph and calculated constants.
This process was then repeated with a different
function. Our next spreadsheet was used
to calculate the position(f(t)) of a free falling object as a function of time(t).
We were given the time increment to be .2 seconds, initial velocity to be 50
meter/second, initial position 1000 meters, and of course the acceleration due
to gravity to -9.8 meters/second squared. We plugged in these numbers to the
kinematic equation (position = (1/2)acceleration*time^2 + initialvelocity*time
+ initial position.) This gave us our
equation f(t) = 1000 +50t – 4.9t^2. In
the same way as the previous spreadsheet we labeled our columns t and
f(t). We then changed our increment from
.1 to .2 and changed our function. Below
is a print out image of the spreadsheet with the first 21 values of t and f(t).
We continued by highlighting the first 100 rows
and using the Edit/Copy function again.
We pasted our table onto the x column of the Graphical Analysis. The result of this was a graph produced
indicating the position of a free falling object as a function of time. The Analyze/Curve fit tool was used again,
and our constants were calculated. This
time our constants were perfectly accurate.
Below is a print out of our position vs time graph and constants.
Conclusion: This
lab instructed us on a powerful tool used for analyzing data. With this tool we were able to make a data
table in a relatively small amount of time and did not have to go to through
the trouble of making every single calculation for every increment of
time(t). The importance of this is that we
as scientists always want to analyze some kind of data in order to see how this
data affects real life scenarios and the Excel software makes it very simple to
put this data in an easy to read format.
With the tables we were also able to extract another useful tool, a
graphical representation of our table, this was also done easily by coping and
pasting the data on to the Graphical
Analysis software.
Favian, nice work. You mention above "We then changed our increment from .1 to .2 and changed our function. Below is a print out image of the spreadsheet with the first 21 values of t and f(t)."
ReplyDeleteHow does changing the time step change the accuracy of the spreadsheet?
Thanks, the smaller you make your time interval (smaller increment and more points) the more accurate you will be. This can be seen if we plot the points in the graphical analysis software. The more points we have the smoother the curve, fewer points some times make sharp corners or "kinks".
ReplyDelete