1 / 55

Spreadsheet Tools for Engineers Using Excel

Spreadsheet Tools for Engineers Using Excel. CIVE 1331 Fall 2008 Hanadi Rifai. Chapter 1. Engineering Analysis and Spreadsheets. Engineering Analysis and Spreadsheets.

evette
Download Presentation

Spreadsheet Tools for Engineers Using Excel

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. Spreadsheet Tools for EngineersUsing Excel CIVE 1331 Fall 2008 Hanadi Rifai

  2. Chapter 1. Engineering Analysis and Spreadsheets

  3. Engineering Analysis and Spreadsheets • Engineering analysis is a systematic process for analyzing and understanding problems that arise in the various field of engineering • To carry out this process, we use problem solving techniques • Spreadsheet programs can be used to solve the problem once you have defined it and set it up properly

  4. Spreadsheets allow you to: • Import, export, store, process, and sort data • Display data graphically • Analyze data statistically • Fit algebraic equations through datasets • Solve single and simultaneous algebraic equations • Solve optimization problems

  5. Examples Book Example 1.1

  6. General Problem Solving Techniques • Think about the problem before you solve it • Draw a sketch to visualize it • Understand the overall purpose of the problem and its key points • Ask yourself: what information is known? And what information must be determined? • Ask yourself: what fundamental engineering principles apply?

  7. General Problem Solving Techniques – Cont’d • Think about how you will solve the problem • Develop your solution in an orderly and logical manner • Think about the solution: does it make sense? • Make sure solution is clear and complete Problem solving is a skill that takes time and practice

  8. Engineering Fundamentals • Equilibrium (e.g., force, flux or chemical equilibrium) • Conservation laws (mass, energy) • Rate phenomena

  9. Mathematical Solution Procedures • Data Analysis • Curve-fitting • Interpolation • Solving single algebraic equations • Solving simultaneous algebraic equations • Evaluating integrals • Engineering economic analysis • Optimization techniques

  10. Chapter 2. Creating an Excel Worksheet

  11. Spreadsheet Basics • basically a table containing numeric or alphanumeric values • Individual elements are called cells • Cells can contain a number or text • A cellreference is its column heading and row number, e.g., B3 • Tabular collection of cells is called a worksheet • Cells contain numbers resulting from formulas

  12. Definitions • Ribbon: upper portion of the window • Title Bar: top line • Office Button: replaced the File Menu • Ribbon Tabs: below title bar, replaced menu headings • Worksheet Tabs: beneath worksheet • Scroll bars: horizontal and vertical • Status bar: bottom line

  13. Skills to learn in Excel • Moving around the worksheet • Entering data • 2, -6, 3.33, 2.55e-12, -7.08e+6, 0.0, 0.004 • $25, 50%, 5/24/2006, 7:20 PM, 19:20:00 • Entering strings or label (text) • Correcting errors • Using formulas and functions • Naming a cell or worksheet • Saving, retrieving and printing worksheets

  14. Operators in Excel • Arithmetic: +, -, *, /, ^, % • String: & • Comparison: >, >=, <, <=, =, <> • Operator precedence: • 1 percentage (%) • 2 exponentiation (^) • 3 Multip/division (* and /) • 4 Add/subtract (+ and -) • 5 concatenation (&) • 6 comparisons (>, <, …) • Operations carried out from left to right

  15. Functions in Excel • Function consists of a: • Function name • Arguments Example: SUM(C1,C2,C3) The function is the sum of cells C1,C2,C3

  16. Examples Book Examples 2.4 &2.5

  17. Chapter 3. Editing an Excel Worksheet

  18. More skills to learn in Excel • Selecting a block of cells • Clearing a block of cells • Copying to adjacent cells by dragging • Copying to nonadjacent cells • Moving a block of cells • Undoing changes

  19. Copying and Moving Formulas Relative vs. Absolute addressing A1+B1 vs. $A$1+$B$1 Moving a formula will not change cell addresses but copying does C1=A1+B1 If A1 is moved to B5 then C1=B5+B1 If an object cell is moved, the formula is changed to reflect the move

  20. Yet more skills to learn in Excel • Inserting and deleting rows and columns • Inserting or deleting cells • Adjusting column width or row height • Formatting data items • Hyperlinks • Displaying cell formulas

  21. Chapter 4. Making Logical Decisions (If-Then-Else)

  22. The IF Function • Requires 3 arguments: logical expression, value for true, value for false • =IF(C1>100, “Too Big”, “Ok”) • Nested IF functions: =IF(A3<0, “Ice”, IF(A3<100, “Water”, “Steam”))

  23. Example Book Example 4.2

  24. Chapter 5. Graphing Data

  25. Examples Book Examples 5.1&5.3

  26. Chapter 6. Analyzing Data Statistically

  27. Data Analysis - Statistics • Engineers gather data to measure variability or consistency • Example: diameters of ball bearings off an assembly line • Another example: variation in sizes among customers to determine how many items of each size to manufacture Statistical data analysis tells us about data

  28. Data Characteristics • Mean or average: expected behavior • Median: a value such that half the data values lie above and half lie below 8, 10, 12, 14, 16, 18, 22, 25, 29 5, 8, 12, 16, 18, 22, 27, 29 • Mode: value that occurs the most in a data set 10, 5, 8, 9, 3, 10, 7 Mode is 10 Median is 17

  29. More Data Characteristics • Min and Max: smallest and biggest value in a dataset • Variance: an indication of the degree of spread in the data s2 = 1/(n-1)*(xi-xm)2 where xm is mean and the summation is for all I from 1 to n • The greater the spread in the data, the larger the variance • Standard deviation: square root of the variance

  30. Example Book Example 6.1

  31. Histogram or relative frequency plot • Describes how data are distributed within their range • Cumulative distribution allows us to estimate the likelihood that a data value associated with an item drawn at random is less than or greater than a specified value

  32. How to construct a histogram • Subdivide the range of the data into a series of adjacent equally spaced intervals • 1st interval begins at smallest value • Last interval extends to or beyond the largest data value (the max) • Fixed interval width • Detemine how many values fall in each interval fi = ni/n where ni is the # of points in the ith interval

  33. Examples Book Examples 6.3, 6.5, & 6.6

  34. Chapter 7. Fitting Equations to Data

  35. Fitting Equations to Data • Statistics and Histograms analyze a set of single-value data: x1, x2, etc. • Engineers need to analyze two-value or paired (x,y) data • Different Methods: • Linear Interpolation • Fitting data with a curve

  36. Linear Interpolation y2 P2 y y2-y1 y – y1 = x – x1 x2-x1 P1 y1 x1 x x2

  37. Example Book Example 7.2

  38. Curve Fitting • Fitting a line or curve through pairs of data • Concept is to represent data with an equation (y = f(x)) • Fit does not have to be exact • Goal is to minimize the error somehow between the line and the data (error between yi and y)

  39. Error in Curve Fitting • For each data point Pi = (xi, yi), the error is the difference between yi and F(xi) or the calculated value of yi • ei = yi – f(xi) • Strategy is to pick a function f(xi) that minimizes ei

  40. Straight Line Fit • Method of Least Squares • Y = ax +b • Two unknowns: a and b have to be chosen carefully to minimize the sum of the squares of the errors • Equations 7.7 and 7.8 in book • Two equations in 2 unknowns (a, b)

  41. Examples Book Examples 7.5 & 7.6

  42. Chapter 9. Transferring Data

  43. Even more skills to learn in Excel • Importing/exporting data from text files • Transferring data from and to Word or PowerPoint • Transferring graphs to Word or PowerPoint

  44. Chapters 11. Solving Single Equations

  45. Algebraic Equations • Linear – none of the unknowns are raised to a power or appear as arguments in a trig function, a log function, a square root etc. • Nonlinear – harder to solve

  46. Finding Numerical Solutions Using Excel • Goal Seek • Solver

  47. Examples Book Examples 11.5 & 11.7

  48. Chapters 12. Solving Simultaneous Equations

  49. n-linear Equations in n unknowns a11x1 + a12x2 +a13x3+….+a1nxn = b1 a21x1 + a22x2 + a23x3+….+a2nxn = b2 …. …. …. an1x1 + an2x2 + an3x3+….+annxn = bn i = row j = column aij’s and b’s are known xi’s are unknown

  50. Matrix • Is a two dimensional array of numbers • Elements characterized by a row number and a column number • A matrix with one column is called a vector • System of equations on previous slide can be written as: AX = B

More Related