1
Lecture 11B
Using ExcelChapter 8
2
Example of an Excel Worksheet
Measured Volts Measured Resistance Computed Current(Volt) (Ohm) (Amps)
10 100 0.112 100 0.1211 100 0.1110 100 0.110 100 0.112 100 0.1211 100 0.1111 100 0.11
3
Starting Excel
Figure 1-2,page 1.04
4
The Excel Window Tool bar Formula bar Worksheet window
Columns and Rows Cells
Pointer Sheet tabs
5
Moving Around a Worksheet Use the mouse Use the keyboard
Arrow keys Page Up Page Down Home Crtl + Home F5
6
File Open...
Open 1-OhmsLaw.xls
Figure 1-8,page 1.11
7
Some Basic Excel Stuff Learn how to enter text, values,
formulas, and functions Describe a range of cells Perform what-if analysis Learn how to use online Help Demonstrate how to print a
worksheet
8
Entering Information Text - letters, symbols, numbers,
and spaces Values - numbers that represent a
quantity Formulas - used to calculate values Functions - special pre-written
formulas
9
Range—used in functions Range - a group of cells: A1:C4
Figure 1-16,Page 1.19
10
Saving a Workbook File Save As...
Figure 1-19,Page 1.23
11
Playing “What-if?” Change a value in a cell Excel automatically recalculates
the worksheet
12
Correcting Mistakes Before you press the Enter key
Use the Backspace key After you pressed the Enter Key
Double click on the cell to put it in Edit mode
Don’t use the Space bar to clear a cell
13
Getting Help Microsoft Excel Help
Contents Answer Wizard Index
Show/Hide the Office Assistant What’s This? Office on the Web About Microsoft Excel
14
Printing the Worksheet File Print...
Figure 1-28,Page 1.32
15
Closing and Exiting File Close File Exit
16
Intermediate Objectives List the components of a well
designed worksheet Understand “Order of Precedence” Demonstrate several methods for
copying information Understand Relative vs Absolute
Cell Referencing Change the name of the worksheet
17
A Well Designed Worksheet Clearly identifies its goal Presents information in a clear,
well organized format Includes all necessary data to
produce the intended results
18
Developing the Worksheet Determine the worksheet’s purpose Enter the data and formulas Test the worksheet Correct errors and make modifications Document the worksheet Improve the appearance Save and print the completed
worksheet
19
Planning the Worksheet What is the goal of the worksheet? What data is needed to calculate the
results? What calculations are needed? EXAMPLE—Calculate density of air as a
function of temperature from 0-50C in 5 degree steps
=P/RT P=stnd atmosphere 101.3kPa R=gas constant for air 286.9 J/kg-K T=temp in Kelvins
20
Building the Worksheet Establish the layout Enter the data Enter the formulas See 2-AirDensity.xls example
21
Entering Labels Helps to identify the cells where
you will enter the data and formulas
Alignment is left justified, and spill into empty cells to the right
22
Entering Data Values can be:
Numbers Formulas Functions
The data is the information you need to perform the calculations
23
Entering Formulas Formulas are equations that
perform a calculation An = sign at the beginning of a cell
indicates you are entering a formula If formulas contain more than one
operator, Excel performs the calculation according to the standard order of precedence
24
Order of Precedence Predefined rules used to perform a
calculation:
3 + 4 * 5 = ?
Is the answer 35 or 23?
25
Order of Precedence (continued)
Exponent: ^ Multiplication and Division: * and / Addition and Subtraction: + and -
3 + 4 * 5 = 23
(3 + 4) * 5 = 35
26
Copying Formulas Menu Commands Toolbar buttons Fill Handle
27
AutoSum Button Automatically creates a formula
that contains the SUM function Looks at the cells adjacent to the
active cell and guesses which range of cells you want to sum
Excel’s guess is displayed
28
Entering Functions Type the function by hand Use the Paste Function
button
Do the example in 3-Functions.xls
Figure 2.17,page 2.17
29
Relative vs Absolute Reference Relative Cell Reference
= A5*B5 Absolute Cell Reference
= $A$5*$B$5
30
Relative vs Absolute References when Copying Formulas
Relative Cell References - cell references change when they are copied
Absolute Cell References - cell references do not change when they are copied
31
Mixed References Part of the reference is absolute
and part is relative:
$A5
Do the example in4-AbsoluteVsRelative.xls
32
Copying using Copy & Paste Select the cell or cells to be copied Select Edit Copy Select the cell or cells you want to
replace Select Edit Paste
The copied cells are placed in the Clipboard, and can be pasted many
times.
33
Renaming the Worksheet Double click on the worksheet tab Enter the new name
34
Excel Plotting Skills Learn to use tables and graphs as
problem solving tools Learn and apply different types of
graphs and scales Prepare graphs in Excel Be able to edit graphs
35
Plotting Data Independent Variables
“The Cause” X-Axis on Graphs (abscissa) Left Columns on Tables
Dependent Variables “The Effect” Y-Axis on Graphs (ordinate) Right Columns on Tables
36
Question
The speed of sound in air depends on the temperature, humidity, and air pressure. What are the independent variables? …dependent variables?
37
Table and Graph Requirements
Label the axes on your graphs. Include units on the axes and on
column headings. Use landscape graphs.
38
Proper Use of Tables & Graphs
Height H (m)
Temp T (C)
Pressure P (kPa)
0 15.0 101.3 300 12.8 97.7
600 11.1 94.2
0
2
4
6
8
10
12
14
16
0 500 1000 1500Height (m)
Tem
per
atu
re (
C)
0
20
40
60
80
100
120
140
160
Pre
ssu
re (
kPa)
Temp
Pressure
You can copy straight intoyour final report!
39
Tables Tables should always have:
Title Column headings with brief descriptive name,
symbol and appropriate units. Numerical data in the table should be written
to the proper number of significant digits. The decimal points in a column should be aligned.
Tables should always be referenced and discussed (at least briefly) in the body of the text of the document containing the table.
40
Table Example
Temperature and Pressure ofWidgets at Various Heights
Height H (m)
Temp T (C)
Pressure P (kPa)
0 15.0 101.3 300 12.8 97.7 600 11.1 94.2
41
Graphs Proper graphing of data involves
several steps: Select appropriate graph type Select scale and gradation of axes,
and completely label axes Plot data points, then plot or fit
curves Add titles, notes, and or legend
42
Graphs - Types
Travel Expenses
02468
1012141618
Fo
od
Ga
s
Mo
tel
Exp
ense
s ($
)
1. Pie Chart 2. Bar Graph
43
Graphs - Types
4. Line Graph
0
2
4
6
8
10
12
14
16
0 500 1000 1500Height (m)
Tem
per
atu
re (
C)
0
20
40
60
80
100
120
140
160
Pre
ssu
re (
kPa)
Temp
Pressure
1
2
3
4
5
6
7
01234567
8
02468101214
Object 1Object 2
Bod
y T
emp
erat
ure
(0C
Speed (m/s) Dis
tanc
e (m
)
3. 3-D Graph
44
Graphs Each graph must include:
A descriptive title which provides a clear and concise statement of the information being presented
A legend defining point symbols or line types used for curves needs to be included
Labeled axes Graphs should always be
referenced/discussed in the body of the text of the document containing the table.
45
Titles and Legends Each graph must be identified with
a descriptive title The title should include clear and
concise statement of the information being presented
A legend defining point symbols or line types used for curves needs to be included
46
Axis Labels Each axis must be labeled The axis label should contain the
name of the variable and its units. The units can be enclosed in
parentheses, or separated from the label by a comma.
Length (km)
47
Gradation Scale gradations should be
selected so that the smallest division of the axis is an integer power of 10 times 1, 2, or 5.
Exception is units of time.Scale Graduations,Smallest Division=1
Acceptable
Scale Graduations,Smallest Division=3.33
Not Acceptable
48
Data Points and Curves Data Points are plotted using symbols
The symbol size must be large enough to easily distinguish them
A different symbol is used for each data set
Data Points are often connected with lines A different line style is often used for
each data set
49
ExampleVelocity of Three Runners
During a 5 km Race
50
Building a Graph In Excel Select the data that you want to include in the chart
by dragging through it with the mouse. Then click the Chart Wizard
51
Choose XY (Scatter), with data connected by lines if desired.
Click “Next”
Building the Graph
52
Building the Graph Make sure that the
series is listed in columns, since your data is presented in columns.
Click the Series tab to enter a name for the data set, if desired.
Choose “Next”
53
Building the Graph Fill in Title and Axis information “Next”
54
Building a Chart Select “As new sheet” to create the chart on it’s own sheet
in your Excel file, or “As object in” to create the chart on an existing sheet
“Finish”
55
Creating a Secondary Axis This is useful when the data sets cover very
different ranges. Right click on the line
(data series) on the chart that you want to associate with a secondary axis.
Select “format data series”
Select the Axis tab, then “Plot series on secondary axis” as shown.
“OK”
56
Editing/Adding Labels Now you can go back to the “chart options” to add labels
Click the chart in a blank area, then either right click and select chart options or choose chart options from the “Chart” menu
Fill in or edit the axes labels, title, etc.
Click “OK”
57
Result
58
Graphical Analysis and Excel
Solving Problems UsingGraphical Analysis
59
RAT 5.1 Close you books, notes and laptops.
Turn off computer monitors.
As a TEAM, you have 3 minutes to answer the following question.
Name the type of function that plots as a straight line on: A) a semi-log graph B) a log-log graph
60
Learning Objectives Learn to use tables and graphs as
problem solving tools Learn and apply different types of graphs
and scales Prepare graphs in Excel Be able to edit graphs Be able to plot data on log scale Be able to determine the best-fit
equations for linear, exponential and power functions
61
Exercise Enter the following table in Excel
You can make your tables look nice by formatting text and borders
Independent Variable, x
Dependent Variable, y1
Dependent Variable, y2
1 1 1
2500 10 50 5000 100 100
7500 1000 150
10000 10000 200
62
Axis Formats (Scales) There are three common axis formats:
Rectilinear: Two linear axes Semi-log: one log axis Log-log: two log axes
Length (km)
1 km 10 km
Log scale:
1 km
Length (km)
Linear scale:
63
Use of Logarithmic Scales A logarithmic scale is normally
used to plot numbers that span many orders of magnitude
1 10 100 1000 10000
64
Creating Log Scales in Excel
Exercise (2 min): Create a graph using only x and y1.
New Graph
0
2000
4000
6000
8000
10000
12000
0 2000 4000 6000 8000 10000 12000
x
y1 y1 data
65
Creating Log Scales in Excel Now modify the graph so the data
is plotted as semi-log y This means that the y-axis is log scale
and the x-axis is linear. Right click on the axis to be
modified and select “format axis”
66
Creating Log Scales in Excel
On the Scale tab, select logarithmic
“OK” Next, go to Chart
Options and select the Gridlines tab. Turn on (check) the Minor gridlines for the y-axis.
“OK”
67
Result: Graph is straight line.
New Graph
1
10
100
1000
10000
0 2000 4000 6000 8000 10000 12000
x
y1 y1 data
68
Exercise (10 min) Copy and Paste the graph twice. Modify one of the new graphs to be
semi-log x Modify the other new graph to be
log-log Note how the scale affects the
shape of the curve.
69
Equations The equation that represents a straight line
on each type of scale is: Linear (rectilinear): y = mx + b Exponential (semi-log): y = bemx or y = b10mx
Power (log-log): y = bxm
The values of m and b can be determined if the coordinates of 2 points on THE BEST-FIT LINE are known: Insert the values of x and y for each point in the
equation (2 equations) Solve for m and b (2 unknowns)
70
Equations (CAUTION) The values of m and b can be
determined if the coordinates of 2 points on THE BEST-FIT LINE are known.
You must select the points FROM THE LINE to compute m and b. In general, this will not be a data point from the data set. The exception - if the data point lies on the best-fit line.
71
Consider the data set:X Y1 42 83 104 125 116 167 188 199 20
10 24
72
Which data points should be used to determine the equation of this best-fit line?
Example of Best-Fit Line
0
5
10
15
20
25
30
0 2 4 6 8 10 12
X, Independent Variable (No Units)
Y, D
ep
en
de
nt
Va
ria
ble
(N
o U
nit
s)
73
Example Points (0.1, 2) and (6, 20) are taken
from a straight line on a rectilinear graph.
Find the equation of the line. Solution:
2 = m(0.1) + b a)20 = m(6) + b b)
Solving a) & b) simultaneously:m = 3.05, b = 1.69
Thus: y = 3.05x + 1.69
74
Pairs Exercise (10 min) FRONT PAIR:
Points (0.1, 2) and (6, 20) are taken from a straight line on a log-log graph.
Find the equation of the line.
BACK PAIR: Points (0.1, 2) and (6, 20) are taken from a
straight line on a semi-log graph. Find the equation(s) of the line.
75
Interpolation Interpolation is the process of estimating a
value for a point that lies on a curve between known data points Linear interpolation assumes a straight line
between the known data points One Method:
Select the two points with known coordinates Determine the equation of the line that passes
through the two points Insert the X value of the desired point in the
equation and calculate the Y value
76
Individual Exercise (5 min) Given the following set of points,
find y2 using linear interpolation.(x1,y1) = (1,18)(x2,y2) = (2.4,y2)(x3,y3) = (4,35)
77
A Baseball Problem A runner is on 3rd base, 90 ft from home plate. He
can run with an average speed of 27 ft/s. A ball is hit to the center fielder who catches it 310 ft from home plate. The center fielder can throw the ball no harder than 110 ft/s. The runner tags up and runs for home plate.
Can the center fielder throw him out? To do so, he must get the ball to the catcher at an appropriate height before the runner can get to home plate.
If so, at what angle and what velocity does he need to throw the ball in order to put the runner out?
78
Solving with Excel Open an Excel
Spreadsheet and create column heads like the example.
Rows 1 - 6 are for constants. Remember to use the $ notation when reference absolute address
79
Solution - Using a Chart One way to solve this problem is
with a graphical representation. Generate data representing the
position of the ball (x(t) and y(t)) and the position of the runner (r(t)).
The next slide shows an example of a completed chart. Notice that the red line shows the ball’s position reaches 310 ft before the runner (blue line) has traveled 90 ft.
80