Using EXCEL in Problem Solving

Document Sample
Using EXCEL in Problem Solving Powered By Docstoc

     EF 105
    Fall 2006
              EF 105
   Computer Methods in Engineering
          Problem Solving

Week 05: Algebra and Trig Review
            Use of EXCEL
Identify major components
of the Excel window
  Excel is a computerized spreadsheet, which is an
  important business tool that helps you report and
  analyze information.
  Excel stores spreadsheets in documents called
  Each workbook is made up of individual worksheets, or
  Because all sorts of calculations can be made in the
  Excel spreadsheet, it is much more flexible than a paper
  The Excel window has some basic components, such as
  an Active cell, Column headings, a Formula bar, a Name
  box, the mouse pointer, Row headings, Sheet tabs, a
  Task Pane, Tab scrolling buttons and Toolbars.
A sample Excel worksheet
Excel worksheets and workbooks

 When you set up calculations in a worksheet, if an
 entry is changed in a cell, the spreadsheet will
 automatically update any calculated values that
 were based on that entry.
 When you open Excel, by default it will open a
 blank workbook with three blank worksheets.
 When you save a workbook, you have a Save As
 option that can save the spreadsheet to earlier
 versions of Excel or to Quattro Pro, Lotus 123
 formats, dBase formats, and even to a comma or
 tab-delimited text file.
Identify Excel components
Descriptions of Excel components
Navigate within worksheets
 To navigate within a workbook, you use the arrow
 keys, PageUp, PageDown, or the Ctrl key in
 combination with the arrow keys to make larger
 The most direct means of navigation is with your
 Scroll bars are provided and work as they do in all
 Windows applications.
 To move to other Worksheets, you can:
   Click their tab with the mouse

   Use the Ctrl key with the Page Up and Page Down
    keys to move sequentially up or down through the
Navigation keystrokes
The Active Cell
Developing a Worksheet

 Determine the worksheet’s purpose.
 Enter the data and formulas.
 Test the worksheet and make any
 necessary edits / corrections.
 Document the worksheet and improve
 Save and print the complete worksheet.
Entering Data into a Worksheet

  To enter data, first make the cell in which you
  want to enter the data active by clicking it.
  Enter the data (text, formulas, dates, etc.) into
  the active cell.
  Use the Alt+Enter key combination to enter text
  on multiple lines within the same cell.
  Use TAB key, arrow keys, or ENTER key to
  navigate among the cells.

Columns are designated by
  alphabetical values such
  as A, B, C ….
Rows are designated by
  numerical values such as
  1, 2, 3 ….
Individual cells are
  designated by ordered
  pairs containing the row
  and column designation C2
  such as A1,C2, B15 ….

    You can also enter
    data using formulas.
    The formula can
    operations using data
    from other cells.
Arithmetic Operators
Order of Precedence Rules
Identify cell ranges

 A group of worksheet cells is known as a cell range, or
 Working with ranges in a worksheet makes working with
 the data easier.
 Ranges can be adjacent or nonadjacent.
     An adjacent range is a single, rectangular block of cells
     Select an adjacent range by clicking on a cell and dragging to
      an opposite corner of a rectangle of cells
     A nonadjacent range is comprised of two or more adjacent
      ranges that are not contiguous to each other
     To select a nonadjacent range, begin by selecting an
      adjacent range, then press and hold down the Ctrl key as you
      select other adjacent ranges
Select and move worksheet cells
 To select a large area of cells, select the first cell
 in the range, press and hold the Shift key, and
 then click the last cell in the range.
 Once you have selected a range of cells, you
 may move the cells within the worksheet by
 clicking and dragging the selection from its
 current location to its new one.
 By pressing and holding the Ctrl key as you
 drag, Excel will leave the original selection in its
 place and paste a copy of the selection in the
 new location.
 To move between workbooks, use the Alt key
 while dragging the selection.
Range selection techniques
Insert worksheet rows and columns

  You can insert one or many additional rows or
  columns within a worksheet with just a few
  steps using the mouse or menu options.
  You can insert individual cells within a row or
  column and then choose how to displace the
  existing cells.
  You can click the Insert menu and then select
  row or column, or right click on a row or column
  heading or a selection of cells and then choose
  Insert from the shortcut menu.
Delete worksheet rows and columns

 To delete and clear cells, rows, or columns, you
 can use the Edit menu, or right click on a
 heading or a selection of cells and choose Delete
 from the shortcut menu.
 Clearing, as opposed to deleting, does not alter
 the structure of the worksheet or shift uncleared
 data cells.
 What can be confusing about this process is that
 you can use the Delete key to clear cells, but it
 does not remove them from the worksheet as
 you might expect.
Use the Undo and Redo features

  Editing is an intrinsic task in any document, and
  especially useful are the Undo and Redo actions.
  The Undo feature allows you to sequentially
  back up to a certain action, such as a delete, a
  move, an entry, etc. and allows you to reverse
  those actions.
  Redo allows you to reapply actions one step at a
  time that you have previously undone.
Insert, move, and rename worksheets

  Worksheets are much like pages within a book; you
  peruse through them like you flip the pages of a
  There are several ways to move, copy and work
  with worksheets.
  Right click on the sheet tab and choose Move or
  Copy. Select a new position in the workbook for the
  worksheet or click the Create a copy checkbox and
  Excel will paste a copy of that worksheet in the
  The same shortcut menu for the sheet tab also
  gives you the option to insert, delete or rename a
Print a workbook

 To Print a worksheet, you can use:
     A menu
     The Print button on the standard toolbar
     The Ctrl-P keystroke to initiate a printout of the
 Excel uses the same basic methods for
 printing as other Windows and Microsoft
 Office applications.
The Print dialog box
Use Excel’s functions

 You can easily calculate the sum of a large number of
 cells by using a function.
 A function is a predefined, or built-in, formula for a
 commonly used calculation.
 Each Excel function has a name and syntax.
   The syntax specifies the order in which you must
    enter the different parts of the function and the
    location in which you must insert commas,
    parentheses, and other punctuation
   Arguments are numbers, text, or cell references used
    by the function to calculate a value
   Some arguments are optional
Work with the Insert Function button

 Excel supplies more than 350 functions
 organized into 10 categories:
     Database, Date and Time, Engineering, Financial,
      Information, Logical, Lookup, Math, Text and Data,
      and Statistical functions
 You can use the Insert Function button on the
 Formula bar to select from a list of functions.
 A series of dialog boxes will assist you in filling
 in the arguments of the function and this
 process also enforces the use of proper syntax.
Math and Statistical functions
Define functions, and functions within functions

  The SUM function is a very commonly used math
  function in Excel.
  A basic formula example to add up a small number of
  cells is =A1+A2+A3+A4, but that method would be
  cumbersome if there were 100 cells to add up.
  Use Excel's SUM function to total the values in a range
  of cells like this: SUM(A1:A100).
  You can also use functions within functions. Consider the
  expression =ROUND(AVERAGE(A1:A100),1).
     This expression would first compute the average of all the
      values from cell A1 through A100 and then round that result
      to 1 digit to the right of the decimal point
Copy and paste formulas and functions

 Copying and pasting a cell or range of cells is a
 simple, but highly effective means for quickly
 filling out a large worksheet.
 To copy and paste a cell or range:
     Select the cell or range to be copied and then click
      the Copy button on the standard toolbar
     Select the cell or range into which you want to
      copy the selection and then click the Paste button
      on the standard toolbar
     Once you are finished pasting, press the Esc key to
      deselect the selection
Copy and paste effects on cell references

 Copied formulas or functions that have cell
 references are adjusted for the target cell or
 range of cells.
 For example, if cell G5 contains the formula
 =F5*B5/B7, and you copy and paste this
 formula to cell G6, the formula in cell G6 will be
 This may or may not be correct for your
 worksheet, depending upon what you are trying
 to do.
 You can control this automatic adjusting of cell
 references through the use of relative and
 absolute references.
Problems using copy and paste with formulas

 When Excel does not have enough room to display an entire
 value in a cell, it uses a string of these # symbols to
 represent that value.
 For example, the formula in cell J5 is =F5-(H5+I5) and this
 was pasted into cell J6 by updating the cell references there
 to =F6-(H6+I6).
 Cell G5 has the formula =F5*B5/B7 and cell G6 contains
 =F6*B6/B8. This is where things went wrong. Sometimes this
 automatic update is very useful and other times it does not
 give you the desired result for your worksheet.
 In this case, cells B5 and B7 should be referenced in the
 formula in column G in all 240 payment period rows, but in
 column J, you want the cell references to be automatically
 updated. You can control this result using relative and
 absolute references.
Use relative references

 A relative reference is a cell reference that shifts
 when you copy it to a new location on a
 A relative reference changes in relation to the
 change of location.
 If you copy a formula to a cell three rows down
 and five columns to the right, a relative
 reference to cell B5 in the source cell would
 become G8 in the destination cell.
Use absolute references

 An absolute reference is a cell reference
 that does not change when you copy the
 formula to a new location.
 To create an absolute reference, you
 preface the column and row designations
 with a dollar sign ($).
 For example, the absolute reference for
 B5 would be $B$5.
 This cell reference would stay the same no
 matter where you copied the formula.
Use mixed references

 A mixed reference combines both relative and absolute
 cell references.
 You can effectively lock either the row or the column in
 a mixed reference.
     For example, in the case of $B5, the row reference would
      shift, but the column reference would not
     In the case of B$5, the column reference would shift, but the
      row reference would not
 You can switch between absolute, relative and mixed
 references in the formula easily in the edit mode or on
 the formula bar by selecting the cell reference in your
 formula and then pressing the F4 key repeatedly to
 toggle through the reference options.

 Numerical data is
 dynamically linked.
 Therefore, changing
 the value of one cell
 propagates the
 changes throughout
 the spreadsheet.
 Notice the effect of
 changing one cell on
 the spreadsheet.
Relative vs. Absolute Addressing


                                    D3 * $B$10
                                    D4 * $B$10
                                    D5 * $B$10
                                    D6 * $B$10


1. In an Excel spreadsheet cell, what formula
    would you enter to calculate the following
          100 x 20 / (15 + 2)
2. Name an Excel built-in function.
3. How would you designate cell B10 as an
    absolute address in a spreadsheet
Do the Tutorial

the practice problems in an excel
file. Save the file as netidDATE,
where netid is your UT netid and
DATE is the month and day (e.g.

Shared By: