Transcript Excel_Workshop

Spreadsheets in
Math and Science Class
Robert Culbertson, ASU Physics
1
ExCell Basics
2
3
4
5
6
7
8
Text Manipulation
Some Favorite Functions
The Plot Thickens
Having a Fit
Some Random Cases
Summing It Up
Animations
6/25/08
MSTF Workshop
1
1. ExCell Basics
• Cell References
Letternumber
For example, A1, A2, B35, …
Relative vs. Absolute
For example, A1, $A1, A$1, $A$1
• Referencing cells by names
For example, M_1 (not M1, however)
• Formulas
Begin with “=“
• Autofill vs. Copy/Paste
6/25/08
MSTF Workshop
2
2. Text Manipulation
• UPPER(text)
LOWER(text)
• LEFT(start, [num_chars])
RIGHT(start, [num_chars])
MID(text, start_num, [num_chars])
FIND(find_text, within_text, [start_num])
• LEN(text)
• Exercise: text manipulation.xls
6/25/08
MSTF Workshop
3
3. Some Favorites
•
•
•
•
•
•
COUNT(range); COUNTIF(range, criteria)
ROUND(number, num_digits)
IF(logical_test [ ,if_true] [,if_false])
OR(logical_test1 [,logical_test2…]
AND(logical_test1 [,logical_test2…])
AVERAGE(range), STDEV(range)
6/25/08
MSTF Workshop
4
4. The Plot Thickens
•
•
•
•
Charts are graphs
Of many, many Charts, “XY Scatter” is most useful
Excel’s default format needs dressing up
Chart options, etc:
Source Data, Format Axes, Format Plot Area. Font
Format, Numbers
• Trendlines
• Exercise: formatting.xls
6/25/08
MSTF Workshop
5
Trig Trivia
• Angles are in Radians
SIN(angle), COS(angle), TAN(angle)
ASIN, ACOS, ATAN return Radians
• PI() returns pi (to 16 decimal places)
• Conversions: RADIANS(angle) and
DEGREES(angle)
6/25/08
MSTF Workshop
6
5. Having a Fit
• “Add Trendline” will fit several kinds of
functions
• Linear plots: SLOPE(datarange),
INTERCEPT(datarange)
• Exercise: fitting.xls
1. Add trendlines
2. Guess a and c
3. Use Solver to find a and c
6/25/08
MSTF Workshop
7
6. Some Random Cases
• RAND() always generates a number between 0
and 1
• RANDBETWEEN(2,5) generates a number
between 2 and 5. Note: 3*RAND()+2 also
generates a number between 2 and 5
• Use to generate data
• Use for simulations
Exercise: rollthedice.xls
6/25/08
MSTF Workshop
8
7. Summing It Up
• Put 0 in cell A1. Then put
=A1+1
in cell A2. Then, clone cell A2 downward
• Euler’s method
Set initial conditions (x_0, v_0) and dt
Calculate initial acceleration, a_0
Calculate v_1 = v_0 + a_0*dt
Calculate x_1=x_0 + v_1*dt
Calculate a_1 from x_1, v_1
Repeat downward
• Example: mass-spring.xls
• Exercise: add damping to mass-spring.xls
6/25/08
MSTF Workshop
9
8. “Animations”
• Excel doesn’t really do animations, but…
• Consider A1 = A1 +1
1. Algebraically impossible
2. Spreadsheets normally complain
Circular Reference
3. It’s the key to “animations”
• To avoid circular reference, go to Preferences,
set Calculation to Manual and Iterations to 1
• Example: waves.xls
6/25/08
MSTF Workshop
10
For more, see
Spreadsheets in Physics Class:
Dr. Bob’s Excellent Adventure
PowerPoint presentation with several dozen
spreadsheet examples
6/25/08
MSTF Workshop
11