cetg 122 excel

• Electronic spreadsheets


  A tool that can be used to solve an engineering problem

  
• Microsoft Excel


  • Used to solve simple engineering problems instead of using computer programs
  • Commonly used to record, organize, and analyze data using formulas
  • Used to present results of an analysis in chart form
• Cell


  Represents the box that you see as the result of the intersection of a row and a column
• Range


  Cells that are selected simultaneously
• Inserting cells, columns, and rows


  1. Select the cell(s) where the new cell(s) are to be inserted

  2. From the Insert menu choose the Cells option

  3. Indicate whether you want the selected cells to be shifted to the right or down

  4. Select as many column (or row) indicator buttons as necessary to the right of where you would like to have the columns (or rows) inserted

  5. Click on the right button of the mouse and choose Insert
• Absolute cell reference


  Does not change when the Fill command is used to copy the formula into other cells
• Relative cell reference


  Changes the formula when the Fill command is used to copy the formula into other cells
• Mixed cell reference


  Combination of absolute and relative cell references
• Creating formulas in Excel

  1. Use the basic Excel arithmetic operations (+, -, *, /)

  2. Substitute values in the formula equation

  3. Use the Fill command to copy the formula into other cells
• Absolute Reference


  Content of $A$3
• Relative Reference


  Relative cell reference changes the formula when the Fill command is used to copy the formula into other cells
• Mixed Reference


  Keep the column absolute and row relative (e.g. $A3) or keep the column relative and row absolute (e.g. A$3)
• Example 14.2 - Cell Reference

  1. Type cell formula, note the use of mix reference

  2. Use Fill command to copy formula from cell B3 to cells C3:E3

  3. Use Fill command to copy formulas from cells B3:E3 to cells B4:E11
• Excel Functions


  • Mathematical
  • Trigonometric
  • Statistical
  • Financial
  • Logical
• Excel Functions


  Can be entered by typing the name of the function or using the Insert Function (fx) button
• Now and Today Functions

  Values update automatically
• Example 14.4 - Excel Functions

  1. Type headings

  2. Type density values

  3. Type AVERAGE formula

  4. Type STAND. DEV. formula
• Excel's Logical Functions


  • IF
  • AND
  • OR
  • NOT
• Excel's Relational or Comparison Operators


  • >
  • <>
  • =
  • <=
  • =
• Example 14.5 - Excel Logical Functions

  1. Type the formula =IF (A3 >= 20, "open", "closed")

  2. Use Fill command to copy the formula in cells B4 through B10
• Workbook


  Collection of one or more worksheets
• Range


  A group of cells in a worksheet
• Absolute Cell Reference


  Cell reference that does not change when a formula is copied to another cell
• Relative Cell Reference


  Cell reference that changes when a formula is copied to another cell
• Mixed Cell Reference


  Cell reference that has one part absolute and one part relative
• Logical Function


  Excel function that tests various conditions when programming formulas to analyze data
• Example 14.6 - Plotting

  1. Select the data range

  2. Pick the Insert tab and select Scatter with the Smooth Lines and Markers button

  3. Add X-axis and Y-axis titles, modify the chart title and gridlines as desired

  4. Place the chart in an appropriate location
• Plotting


  • Example 14.6
• Plotting


  1. Add X-axis and Y-axis titles

  2. Modify the chart title

  3. Modify gridlines as desired
• Layout tab


  Where to find options to add axis titles, chart title, and modify gridlines
• You can plot more than one set of data on the same chart
• Plotting two data sets with different ranges on the same chart

  • Air temperature
  • Wind speed
• Plotting of two data sets

  • Example 14.7
• Plotting of two data sets

  1. Compute the fuel consumption in km/l and liters per km for the given range of speeds

  2. Plot the fuel consumption in km/l versus speed

  3. Add the second data series (liters per km) to the chart
• Secondary axis


  Used to plot the second data series with different units
• Curve fitting deals with finding an equation that best fits a set of data
• Curve fitting with Excel

  1. Plot data

  2. Add trendline or best fit

  3. Choose trend/regression type

  4. Toggle on set intercept and display equation on chart
• Curve fitting


  • Example 14.8
  • Example 14.9
• Matrix


  An array of numbers, variables, or mathematical terms
• Matrix size


  Defined by number of rows and columns