Sunday, December 16, 2012

Working with Spreadsheets


Working with Spreadsheets


Purpose:

To get familiar with electronic spreadsheets by using them in some simple applications.


Equipment:

  • 1 Computer with Microsoft EXCEL software.

Procedure: (from lab handout)
1. Your instructor will give you a brief explanation of how a spreadsheet works and show you some 
of the basic operations and functions. 

2. Turn on the computer and load Excel software by clicking on Start, move the mouse over              
Programs them move the mouse over Microsoft Excel and then press the left button. 

3. Create a simple spreadsheet that calculates the values of the following function: 

    f(x) = A sin(Bx + C) 
   
Initially choose values for  of  A = 5, B = 3 and  C = π/3.  Place these values at the right side of the 
spreadsheet in the region reserved for constants.  Put the words amplitude, frequency, and phase 
next to each as an explanation for the meaning of each constant.  Place column headings for "x" 
and "f(x)" near the middle of the spreadsheet, enter a zero in the cell below "x", and enter the 
formula shown above in the cell below "f(x)".  Be sure to put an equal sign in front of the formula.  
Create a column for values of x that run from zero to 10 radians in steps of  0.1 radians.  Use the 
copy feature to create these x values (Don't enter them all by hand!).  Similarly, create in the next 
column the corresponding values of  f(x) by copying the formula shown above down through the 
same number of rows (100 in all). 

4. Once the generated data looks reasonable, copy this data onto the clipboard  by highlighting the 
contents of the two columns and choosing  EDIT/COPY from the menu bar. Print out a copy of 
your spreadsheet (first 20 rows or so) and also print out the spreadsheet formulas (try CTRL~).  Be 
sure that your rows and columns are numbered and lettered. 

5. Minimize the spreadsheet window and run the Graphical Analysis program by opening the Physics 
Apps icon (double click the mouse on the icon) and then double click on the Graphical Analysis
icon.  Once the program loads, click on the top of the x column and then choose EDIT/PASTE to 
place the data from the clipboard into your graphing program.  A graph of the data should appear 
in the graph window.  Put appropriate labels on the horizontal and vertical axes of the graph.  

6. Highlight the portion of the graph you want to analyze and choose ANALYZE/CURVE FIT from 
the menu bar to direct the computer to find a function that best fits the data.  From the list of 
possible functions, give the computer a hint as to what type of function you expect your data to 
match.  The computer should display a value for A, B, and C that fit the sine curve that you are 
plotting.  How do these compare with the values that you started with in your spreadsheet?  Make 
a copy of the data and graph by selecting FILE/PRINT.  Include this in your lab report. 

7. Repeat the above process for a spreadsheet that calculates the position of a freely falling particle as 
a function of time.  This time your constants should include the acceleration of gravity, the initial 
velocity, initial position, and the time increment.  Start off with g = 9.8 m/s^2, v0 = 50 m/s, x0 = 
1000 m and ∆t = 0.2 s.  Print out the spreadsheet (calculated results and formula as in part 4).  
Again copy the data into the Graphical Analysis program and obtain a graph of position vs time.  
Fit this data to a function  (y = A + Bx + Cx^2) which closely matches the data.  Interpret the 
values of  A, B, and C.  Get a printout of this graph with the data table.  Include this printout in 
your lab report.

  
1st Spreadsheet

We used f(x)=Asin(Bx+C) as our function. We had x start at zero and then go to 10 by 0.1. In the equation, A = amplitude which is 5 in this equation, B = frequency which is 3, and C = phase which is pi/3.
The first picture has the equation shown. In the second picture, we have the numerical value after excel plugged in the numbers.
This is the graph of the plotted points. The x values are that of which we used in excel sheet. It starts at zero and goes to ten by 0.1. The y values are the numerical values we got after excel plugged the numbers into the equation:
The original equation is f(x)=5sin(3x+(pi/3). After doing the best fit quadratic line, out equation comes out to be f(x)=5sin(3x+1.05). This is fairly correct when pi/3 is rounded to two decimal places. We used 1.047198 for pi/3.


y(formula)=&A&2+($8$2*E:E)+(SC$2*E:E^2)
(the unclear data is input as above on this picture)

Graph # 1  ------------------> 











These are the excel spreadsheets from the second equation we did, f(x)=A+Bx+Cx^2. In this equation, A is position (x0) which equals 1000m. B is velocity (v0) which equals 50m/s. Lastly, C is gravity which equals -4.9m/s^2. For the x values, x is represented by delta t which equals 0.2. We interpreted this as the x values start at 0.2 and increase by 0.2 as well. We had it go to ten just like the last equation.

This spreadsheet "shows the equation" (bad quality) and the numerical value after excel plugged the numbers into the equation.










Graph # 2
This it the graph that comes from the second equation. The x values are delta t which starts at 0.2 and increase by 0.2. The y values are the numerical values given by plugging the numbers into the equation.
Our original equation was f(x)=1000+50x-4.9x^2. The best fit line we got was f(x)=1000+50x-4.9x^2. The equation is the exact same as the one we used.

Conclusion:

     This was a good lab exercise  because we got a chance to practice and learn how to use Microsoft EXCEL. It also helped because we were able to see how the different parts of equations affect graphs. In our group, we had initially put our acceleration (due to gravity) as positive 4.9m/s^2 instead of negative 4.9m/s^2. The only problem we had in this lab was that our graph was completely different from the one that was negative. Our parabola was upside down opening up. However, when we fixed the problem, we understood what was going on.

No comments:

Post a Comment