Excel Notes Phys 244 2005 B J Lieb
Excel Notes Phys 244 © 2005, B. J. Lieb
Calculating Velocity • The velocity is calculated by entering the following: =(B 3 -B 2) / (A 3 -A 2). • Then drag the box in the lower right corner of the cell down to calculate other values. • Note that there must be one fewer velocity value than position value.
Useful Functions • Excel has a number of functions that are useful in PHYS 244 • = SUM(A 1: A 10) — calculates the sum of the data in cells A 1 to A 10 • = AVERAGE(A 1: A 10) — calculates the average of the data in cells A 1 to A 10 • = STDEV(A 1: A 10) — calculates the standard deviation of the data in cells A 1 to A 10 • = SQRT(A 1) — square root of number in cell A 1. • = ROUND(A 1, 3) — rounds the number in cell A 1 to three significant digits.
Excel’s Trig Functions • You are likely to use SIN(A 1), COS(A 1), ASIN(A 1) and ACOS(A 1) • Trig functions in Excel assume the argument is in radians. • = Radians(A 1)– assumes that the value in cell A 1 is an angular measurement in degrees and converts it into radians. • =SIN(RADIANS(A 1)) – converts the value in cell A 1 into radians and then takes the sine of it.
Least Squares Fitting • The least squares fitting method minimizes the sum of the squares of the differences between the data points and a theory curve • When working with linear data, the lab manual often asks you to insert a trendline and then do a linear regression • The difference between the two operations in Excel is that the • trendline plots theory line on your graph • the linear regression gives the uncertainties in the fit parameters. • You will want to do both
Linear Regression • In Excel, to do a linear regression you must have selected a cell, not a graph • In Excel, the Data Analysis Tools must be installed • Select “Tools>Data Analysis>Regression Intercept Slope Uncertainties
Graphs • Plot experimental data as points or points with error bars • Plot theory as continuous lines—usually this is done using the “trendline” feature • To add error bars, double click on a data point and select X or Y error bars. (Do this before you add a trendline. ) • Trendlines can be linear, logarithmic, polynomial, power, exponential or a moving average. • When adding trendlines, select the options to display equation and R 2. • The equation of the trendline gives the fit parameters. • R 2 is a measure of the goodness of fit and R 2=1 is optimal.
Graphs Equation of trendline — note that parameters are the same as linear regression on previous slide.
- Slides: 8