Using spreadsheets for lab work PHY102M Henry Schreiner The University of Texas at Austin Entering information Cells Cells have names A B C 1
A1 B1 C1 2 A2 B2 C2 3 A3
B3 C3 Cells contain data, text, or formulas Cells have formats Normally overwrites, F2 or clicking again edits Formulas Start with = Standard math symbols: +-*/^() Functions: SIN(), COS(), SQRT(), Cells by name (click shortcut) A B
C 1 1 = A1 + A2 + A3 6 2 2 = ( A1 + A2 ) / A3 1 3 3
= SQRT(A2 ^2) 2 Cell Ranges You can refer to a range of cells using : A1:A3 refers to the cells A1, A2, and A3 SUM(), AVERAGE(), A B C 1 1
=SUM(A1:A3) 6 2 2 =AVERAGE(A1:A3) 2 3 3 Copy and paste Normal copy/paste copies formulas More options available Numbers Formats
Linked cells Copy and paste formulas Copy and paste automatically translates positions A B C 2 =A1
=B1 3 =A2 1 Locking Adding $ locks the following value A$1 -> the 1 wont change $A1 -> The A wont change $A$1 -> Neither will change A B
C 2 =$A$1 =$A$1 3 =$A$1 1 Smart fill Using the mouse, you can smart fill values quickly Works off selected cells
Drag the corner down or across Smart fill Using the mouse, you can smart fill values quickly Works off selected cells Drag the corner down or across Formulas -> Like copy/paste Numbers/Text -> Tries to guess the series Handy formulas PI() SIN(x), COS(x), TAN(x) ASIN(x), ACOS(x), ATAN(x) SQRT(x) RADIANS(x), DEGREES(x)
SUM(x), AVERAGE(X) CONVERT(x, from, to) SLOPE(y,x), INTERCEPT(y,x) Graphing Select a range Insert graph Choose type Format options Labels Trend line and option Move to separate sheet Advanced graphing Changing type Changing data source Multiple data sources on one graph
Printing Page ranges Scale to fit Use sheets if you can! Different views available Advanced: Naming cells Cells can have a name Makes formulas nicer Fewer $ Personal preference: Start names with _ =$A$1 * $A$2 * B1 vs. =_m * _g * B1
Advanced: Unconnected selection Use control to select ranges that dont touch Useful for graphing Also works in some other programs Advanced: Cell formatting You can control sig-figs with formatting You can change display types You can control color, etc. 0.001234 0.0012 1.2E-3
Advanced: Special text Super/subscripts Greek letters Special symbols Force text with Advanced: Tables (Excel) Tables allow simple manipulation of tabular data Auto-fills, auto-updates for formulas Nicer range names (?) Easy sorting Advanced: Formula helpers List of functions Can move cells by dragging
Can show formulas Can show dependents Sort: not (usually) a formula Example lab-like work j t=j/30 (s) h (m) v (m/s) 0 0.141 0.000 1
0.113 0.267 2 0.091 0.533 3 0.083 0.800
4 0.083 1.067 5 0.026 1.333 6 -0.016 1.600
7 -0.053 1.867 8 -0.071 2.133 m=2.1 kg m*g* h
*m*v ^2 g = 9.81 m/s2 Second example Run 1 Run 2 Run 3 Run 4 m (kg) m1
m2 m1+m2 m1+m2 v (m/s) 1.2 2.2 .70 .75
Averag e Total p=m*v KE=1/2*m*v ^2 m1=2.1 kg m2=0.93 kg More help Appendix A in lab manual Specific tutorials on 102M website Look for help online
Look for YouTube videos Ask