1. Class 4.2: Tables, Graphs and Excel Solving Problems Using
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:
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
9. Graphs - Types
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
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
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. Choose XY (Scatter), with data connected by lines if desired.
Click “Next” Building the Graph
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.
19. Building the Graph Fill in Title and Axis information
20. 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
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.
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.
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
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:
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.
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.
Choose XY (Scatter)
Then choose “Next” Building a Chart (Step 3)
39. Building a Chart (Step 4) Make sure that the series is listed in columns.
40. Building a Chart (Step 5) Fill in Title and Axis information
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.
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
46. Using Solver Pull down Tools, then select solver.
Set Target Cell
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 $B$4, $C$2, $A$8.
50. Assignment #7 DUE:
See Assignment #7 Handout