Graphing and Curve Fitting Using Excel       PHYS 211L    Section ________ 

Name:__________________    Partner(s):______________________

Experiments in physics involve data collection and analysis. Throughout the semester we will use excel spread sheet program for data analysis and graphing. In this lab activity you will be introduced to tabulation of data, calculating new variables, and graphing.

A. Tabulation of data and Calculating values for a new variable: Area = Length X Width.

1. Enter the following length and width data in the excel spread sheet program. Please note that the first raw is used for titles and their units. It is customary to put units in parenthesis.

Length (cm) Width (cm)
5.1 4.3
2.5 2.3
15.1 10.5
25 20
30.5 25.3
175 150
16.5 14.5
54.7 49.7
5.4 4.5

2. Title the third column as Area with its units. 

3. Enter the formula to calculate the area in cell C2 as,  =A2*B2, and enter. Go back to cell C2 and move the mouse to the lower right corner until the white plus becomes a black plus. At this point click and drag down the mouse until cell C10 is high-lighted and then release the mouse. Now the calculated area is displayed in column C. Change the decimal places to 1.

4. Print a hard copy of your data table, with gridlines, and attach it to your report.

 

B. Graphing Data                                               

Here you will enter some temperatures in degrees Celsius, convert the temperature to degrees Farenheit using this website, and graph Tf versus Tc.  

1. Start with a blank page and title the first column as Tc (0C) and the second column as Tf (0F).  Enter the following temperatures in degrees Celsius in the first column: -40, -20, 0, 20, 40, 60, 80, 100, 120, 140, 160, and 180 .

2. Convert the above temperatures to degrees Farenheit and enter their values in the second column.

3. Graph Tf versus Tc: Tf on Y-axis and Tc on X-axis.
    a. High-light the data, click the Chart Wizard, and select XY (Scatter) chart type, and click Next.
    What you see is a graph where the first column (Tc) is plotted in the X-axis and the second column (Tf) is plotted in the Y-axis. You can check this
    by clicking on the series tab. Click the series tab and make sure that column A is on the X-axis and column B is on the Y-axis.
    b. Click Next and name the title and axes of the graph. Axes names must include units.
    c. Click Next and then Click Finish.
    d. To display equation: Click Chart Menu, Click Add Trendline, Click Options Tab, Check Display Equation on chart, and Click OK.
    e. Click the equation and change x and y to appropriate names.
    f. Print a hard copy of your graph and attach it to your report.
   

4.  Now you will plot Tc versus Tf: Tc on Y-axis and Tf on X-axis, by following the procedures below.     

  1. Click “Chart Wizard” in the tool bar.
  2. Click chart type, “XY (Scatter)” and click “Next”.
  3. Click the “series” tab and click “add”.
  4. Click the “Red arrow box” for the Y-values, high-light the Y-values to be plotted, and click the “Red arrow box” again.
  5. Repeat the above procedure for X-values.
  6. Type in the name for the graph and click “Next”.
  7. Enter the names for X-axis and Y-axis, click “Next”, and click finish.
  8. Click “Chart” in the tool menu and click “Add Trendline”.
  9. Click the “Options” tab, check “Display equation on chart”, and click “ok”.
  10. Click the equation and change x and y to appropriate names.
  11. Print a hard copy of your graph and attach it to your report.
     

C. Graphing Exercises

1. The force, F as a function of distance, Z is given below, where k and F0 are constants. 

 F = kz + F0.  

Z (m) F (N)
0.5 7.5
1.0 12
1.5 17
2.0 21.5
2.5 24
3.0 30
3.5 32
4.0 37

Plot the above data points to obtain a linear graph and determine k and F0 from the graph. Include units for k and F0. Print a hard copy of your graph and attach it to your report.

k = ______________            F0 = ___________________

 

2. The distance, s as a function of time, t is given below, where a and b are constants.

                          s = a t2 + b 

t (s) s (m)
0.5 2.5
1.0 3.5
1.5 4.6
1.7 5.4
1.9 6.3
2.0 7.0
2.2 8.0
2.5 9.2

Enter the above data and plot s versus t. Your graph should be a curve. Add a polynomial trendline  and determine a and b from the displayed equation. Include units for a and b. Print a hard copy of your graph and attach it to your report.

a = __________________    b = ___________________

To get a linear graph, you need to plot s versus t2. Plot s versus tand obtain a and b from the graph. Include units for a and b. Print a hard copy of your graph and attach it to your report.

a = __________________    b = ___________________

 

3. The emf, e in millivolt, of a thermocouple operating between a bath at temperature T and an ice water standard is given by;

 e = AT + BT2, where A and B are constants.

e (mV) T (0C)   
0.8 10
2.6 20
5.4 30
9.2 40
14.0 50
19.8 60
26.6 70
34.4 80

Enter the above data and plot e versus T. Your graph should be a curve. Add a polynomial trendline  and determine A and B from the displayed equation. Include units for A and B. Print a hard copy of your graph and attach it to your report.

A = __________________    B = ___________________

 

Change the variables to obtain a linear plot and determine A and B from the graph. Print a hard copy of your graph and attach it to your report.

A = ________________    B = _____________________

 

4. The period, T as a function of mass, m is given by the following equation; where k is a constant.

                       

m (kg) T (s)
0.01 0.63
0.05 1.40
0.10 1.99
0.20 2.81
0.40 3.97
0.6 4.87
0.75 5.44
0.9 5.96

Plot T versus m, add a power trendline, and determine k from the displayed equation. Print a hard copy of your graph and attach it to your report.

Coefficient of the power fit = ________________    k = ____________________

Plot a linear graph, and determine the slope, and then determine the constant k. Print a hard copy of your graph and attach it to your report.

Slope = _______________    k = ___________________

 

5. In this last exercise you will calculate the volume, V and surface area, A of a sphere of radius, R for R = 1m to R = 100 m. You will also calculate the ratio V/A, and plot V, A, and V/A as a function of R in a graph. Add suitable trendlines and display the equations. Show your graph to the instructor before you print.