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.
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 t2 and 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.