1 / 26

Mastering Graphical Analysis and Excel for Problem-Solving

This training module focuses on using Excel for graphical analysis to enhance problem-solving skills. Participants will learn to utilize tables and various graph types, including linear, semi-log, and log-log scales, to visualize data effectively. Skills developed will include preparing and editing graphs, plotting data on log scales, and determining best-fit equations for linear, exponential, and power functions. Through hands-on exercises, learners will practice creating and modifying graphs, interpolating values, and analyzing results to ensure accurate data representation.

palmer-bolton
Télécharger la présentation

Mastering Graphical Analysis and Excel for Problem-Solving

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Class 7.2: Graphical Analysis 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 • Be able to plot data on log scale • Be able to determine the best-fit equations for linear, exponential and power functions

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

  4. 1 km Linear scale: Length (km) 1 km 10 km Log scale: Length (km) Axis Formats (Scales) • There are three common axis formats: • Rectilinear: Two linear axes • Semi-log: one log axis • Log-log: two log axes

  5. Use of Logarithmic Scales • A logarithmic scale is normally used to plot numbers that span many orders of magnitude

  6. Creating Log Scales in Excel • Exercise (2 min): Create a graph using x and y1 only.

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

  8. 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”

  9. Result: Graph is straight line.

  10. Exercise (8 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.

  11. Result:semi-log x

  12. Result: log-log New Graph 10000 1000 y1 100 y1 10 1 1 10 100 1000 10000 x

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

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

  15. Consider the data set: X Y 1 4 2 8 3 10 4 12 5 11 6 16 7 18 8 19 9 20 10 24

  16. Team Exercise (3 minutes) • Using only the data from the table, determine the equation of the line that best fits the data. • When your team has completed this exercise, have one member write it on the board. • How well do the equations agree from each team? • Could you obtain a better “fit” if the data were graphed?

  17. Which data points should be used to determine the equation of this best-fit line?

  18. Which data points should be used to determine the equation of this best-fit line?

  19. Comparing Results • How does this equation compare with those written on the board (i.e- computed without graphing) ? • CONCLUSION: NEVER try to fit a curve (line) to data without graphing or using a mathematical solution ( i.e – regression)

  20. What about semi-log graphs? • Remember, straight lines on semi-log graphs are EXPONENTIAL functions.

  21. What about log-log graphs? • Remember, straight lines on log-log graphs are POWER functions.

  22. Example • Points (0.1, 2) and (6, 20) are taken from a straight line on a rectilinear graph. • Find the equation of the line, that is use these two points to solve for m and b. • 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

  23. 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, ie - solve for m and b. • 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, ie - solve for m and b.

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

  25. 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)

  26. Assignment #13 • DUE: • TEAM ASSIGNMENT • See Handout

More Related