Class 4.2: Tables, Graphs and Excel

1
Class 4.2 Tables, Graphs and Excel

• Solving Problems Using
• Graphical Analysis

2
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

3
Proper Use of Tables Graphs

• Engineers record and present data in two primary
  formats Tables and Graphs

4
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.

5
Table Example
6
Exercise

• Enter the following table in Excel
• You can make your tables look nice by formatting
  text and borders

7
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

8
Graphs - Types
2. Bar Graph
9
Graphs - Types
3. 3-D Graph
4. Line Graph
Body Temperature (0C
Distance (m)
Speed (m/s)
10
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.

11
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

12
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.

13
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.

14
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

15
Example
16
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

17
Building the Graph

• Choose XY (Scatter), with data connected by lines
  if desired.
• Click Next

18
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

19
Building the Graph

• Fill in Title and Axis information
• Next

20
Building a Chart

• Select As new sheet to create the chart on its
  own sheet in your Excel file, or As object in
  to create the chart on an existing sheet
• Finish

21
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

22
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

23
Result
24
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 faster 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?

25
Graphic Translation
Runner
90 ft
V0
Center Fielder
q
310 ft
26
A Solution-Formula

• The position of a projectile can be described
  with the following equation
• where V0 represents initial velocity, q
  represents the angle of launch, and t represents
  time. Both i and j are unit vectors.

27
Solution - What I Need to Know.

• To solve this problem, we need to determine a
  value for q and V0 that returns a value of 310
  for the coefficient of (the x distance) and 0
  for the coefficient of (the y distance).
• We also need to find the values where t is
  minimized.
• Then, compare t to the time it takes the runner
  to reach home plate.

28
Team Exercise

• Note q can range from 0 to 45 degrees, whereas
  V0 has a range from 0 to 110 ft/s and t has a
  range from 0 to 3.333 seconds.
• As a team, decide if these ranges are correct and
  justify your conclusions.
• Also, as a team, develop an algorithm for solving
  this problem.

29
Solutions - Possibilities

• Because we have 3 unknowns (q, V0, and t), we try
  to find three equations and solve for the 3
  unknowns.
• Because t is any value less than 3.333 we can
  assign it an arbitrary value of 3.25

30
Solutions - Possibilities

• This reduces the problem to two equations and two
  unknowns
• While these are solvable they are not very
  attractive equations. Can Excel do any better?

31
Solving with Excel-Iteration Method

• 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

32
Solutions - Building a Table

• Rows 7 and above can be used to calculate the x
  and y positions at different times t using the
  formula for projectile motion. For example,
  under x(t) in Cell B8 enter the formula
• B4cos(C2)A8
• What formula would be entered for y(t)
  (height)and r(t) (runner position)?
• Is there an easy way to enter the values for time
  beginning in Cell A8?

33
Solution - iterations

• Notice how changing Cell B2 effects the rest of
  the spreadsheet, especially x(t) and y(t)
  columns.
• By watching the results in those columns, you can
  get arbitrarily close to 310 and 0. Also Cell B4
  can be changed for even finer tuning.

34
Solution - Using a Chart

• Another way to solve this problem is with a
  graph. This method will use the data generated
  on the previous slides but will use a chart to
  show the result.
• The next slide shows a completed chart. Notice
  that the line shows the ball position reaches 310
  ft before the runner has traveled 90 ft.

35
(No Transcript)
36
Building a Chart (Step 1)

• Select the data that you want to include in the
  chart by dragging through it with the mouse.

37
Building a Chart (Step 2)

• click the chart wizard.

38
Building a Chart (Step 3)

• Choose XY (Scatter)
• Then choose Next

39
Building a Chart (Step 4)

• Make sure that the series is listed in columns.
• Choose Next

40
Building a Chart (Step 5)

• Fill in Title and Axis information
• Next

41
Building a Chart (Step 6)

• Choose As new sheet, then Finish

42
Building a Chart (Step 7)

• Creating a Secondary axis.
• Right click on the data series that you want to
  associate with a secondary axis.
• Right click and choose format data series.
• Select Plot series on secondary axis

43
Building a Chart (Step 8)

• Select Chart, then Chart Options
• Fill in the title for secondary value (Y) axis.
• Click OK
• This should complete the chart.

44
Using Solver

• Select and copy the first 8 rows of the first 4
  columns of the spread sheet.
• Remember that Row 8 contains the formulas for
  calculating the x, y and r positions.

45
Using Solver

• Select another worksheet from the bottom of the
  spreadsheet
• Right click on its label and rename if desired.
• Select Cell A1
• Paste.

46
Using Solver

• Pull down Tools, then select solver.
• Set Target Cell
• Desired Value
• Manipulated
• Cells
• Constraints
• Select Solve

47
Using Solver

• Solver arrives at a solution that is within the
  constraints.
• q 28.31 degrees
• V0 110 ft/s
• t 3.20 seconds.
• The ball is at home plate two feet off the ground
  while the runner is still 3.58 feet away.

48
Helpful Hint

• Note that any cell can be assigned a name. This
  can be done by first clicking on the cell (say
  B4) and then typing the name in the name box
  (above the column A label). This can be very
  useful when that cell is used as an absolute
  address.

49
Helpful Hint

• The name can then be used when typing formulas.
  This creates a formula that looks more like the
  actual equation making it easier to type and to
  verify. In this example the cell names,
  Vo_solver, theta_solver, and time were used
  instead of B4, C2, A8.

50
Assignment 7

• DUE
• INDIVIDUAL ASSIGNMENT
• See Assignment 7 Handout