EIN%204905/ESI%206912%20Decision%20Support%20Systems%20%20Excel - PowerPoint PPT Presentation

About This Presentation
Title:

EIN%204905/ESI%206912%20Decision%20Support%20Systems%20%20Excel

Description:

Spreadsheet-Based Decision Support Systems Chapter 3: Referencing and Names Prof. Name name_at_email.com Position ... – PowerPoint PPT presentation

Number of Views:71
Avg rating:3.0/5.0
Slides: 35
Provided by: Michell436
Category:

less

Transcript and Presenter's Notes

Title: EIN%204905/ESI%206912%20Decision%20Support%20Systems%20%20Excel


1
Spreadsheet-Based Decision Support Systems
Chapter 3 Referencing and Names
Prof. Name
name_at_email.com Position
(123) 456-7890 University Name
2
Overview
  • 3.1 Introduction
  • 3.2 Referencing Cells
  • 3.3 Names for Cells, Ranges, and Worksheets
  • 3.4 Summary

3
Introduction
  • Referencing a cell or range of cells
  • Different types of referencing affect how cell
    addresses are copied
  • Use R1C1 notation
  • Avoid circular referencing
  • Various ways to name cells
  • Creating basic formulas and constants using cell
    names

4
Referencing Cells
  • Relative Referencing and Absolute Referencing
  • R1C1 Notation
  • Referencing Other Worksheets and Workbooks
  • Circular Referencing

5
Relative Referencing and Absolute Referencing
  • There are four basic types of referencing
  • relative
  • absolute
  • row absolute
  • column absolute referencing
  • Relative Referencing Row and column value will
    change (B2).
  • Absolute Referencing Neither row nor column
    value will change (B2).
  • Row Absolute Row value does not change, but
    column value will change (B2).
  • Column Absolute Column value does not change,
    but row value will change (B2).

6
Figure 3.1
  • The SUM function is entered in column B16 using
    relative referencing SUM(B4B13)
  • When the function is copied to cell C16, the
    function values shift relative to the new position

7
Figure 3.2(a)
  • In cell E12, the sum from cell B16 is multiplied
    by the value in cell E4 E4B16
  • Copying this formula to cell E13, does NOT yield
    E4C16, but rather F4C16

8
Figure 3.2(b)
  • Absolute referencing will keep E4 constant in
    both formulas E4B16 and E4C16.

9
Referencing (contd)
  • Row absolute referencing places the in front of
    the row number
  • column letter row number
  • A1
  • Column absolute referencing places the in front
    of the column letter
  • column letter row number
  • A2

10
Figures 3.3(a) and 3.3(b)
  • Numbers in row headings are same as column
    headings and we want one row number multiplied by
    one column number.
  • When the formula is copied, only the column of
    the row absolute value will change and only the
    row of the column absolute value will change

11
R1C1 Notation
  • R1C1 notation Refers to a cells position
    relative to the origin cell where the formula is
    entered.
  • The cell in which the formula is entered is
    considered to have position R0C0.
  • A formula entered in A1 to refer to A2 would be
  • R1C0 or R1C
  • A formula entered in B1 which refers to A3 would
    be
  • R-1C2
  • To switch row and column titles to R1C1 notation
  • Click on the Excel Options command listed in the
    File tab of the Ribbon
  • In the Excel Options dialog box, select the
    Formulas tab.
  • Check R1C1 reference style from the list of
    Working with formulas options on that tab.

12
Figure 3.4
  • The first value of the table is referenced in
    cell R1C1 (or A1)
  • The sum of the first two table values is
    calculated in cell R2C1 (or A2)

13
Figure 3.5
  • The formula from cell R2C1 is copied to the three
    cells below.
  • Notice the actual formula is identical in each
    cell.

14
Figure 3.6
  • Excel offers a Formula View that shows cells by
    their formulas, instead of their calculated
    values.
  • Click on
  • Formulas tab gt Formula Auditing group gt Show
    Formula command

15
Referencing Other Worksheets and Workbooks
  • Cells can also contain formulas which reference
    cells outside of the current worksheet or
    workbook.
  • Worksheet in the same workbook Sheet1!A1
  • Workbook Data.xlsSheet1!A1
  • Workbook with spaces in the title Collected
    Data.xlsSheet1!A1
  • Workbook not currently open in Excel
  • C\My Documents\Project\Collected
    Data.xlsSheet1!A1

16
Figures 3.7 and 3.8
  • Data is in one worksheet and calculations are
    made in another worksheet

17
Circular Referencing
  • A referencing loop in a spreadsheet creates a
    circular reference.
  • Example cell A1 has the value B1, cell B1 has
    the value C1, and cell C1 has the value A1.
  • This referencing loop causes an error in Excel.
  • The first possible solution to this problem
    requires us to rearrange our references or to
    modify our formula.
  • However, if neither can be done, Excel offers
    another tool to aid in sequential calculations.
  • Select File tab, click Options command, select
    the Formulas tab of Excel Options dialog box, and
    check the Enable iterative calculations option.
  • Excel performs a specified number of iterations,
    or repetitions, of the calculations, to try to
    find a solution applicable to all equations.

18
Names for Cells, Ranges, and Worksheets
  • The Name Window
  • Define
  • Apply
  • Create
  • Formulas and Constants

19
Name Window
  • Highlight a cell or range of cells
  • Type a name in the name window in the upper
    left-hand part of your window (just above cell
    A1)
  • View drop-down list of current object names in
    workbook
  • This is the simplest and most common way to
    assign names

20
Define
  • Defining Names Names cells, ranges, constants,
    and formulas.
  • Click on
  • Formulas tab gt Defined Names group gt Define Name
    drop-down menu gt Define Name option

21
Figure 3.9
  • Rename the cell with the length value as Length
  • The name will refer to Sheet1!C3

22
Figure 3.12
  • You can also use Define to name a range of cells
  • After naming each of the first three table
    values, we can name the entire range of table
    values Sheet1!C3C5 as Parameters.

23
Figures 3.13 and Figure 3.14
  • Name references can be modified using the Name
    Manager dialog box
  • Click on
  • Formulas tab gt Defined Names group gt Name Manager
    command
  • Select a name and then click on the Edit command

24
Apply
  • Applying Names Updates formulas with new cell
    and range names.
  • If you have previously referenced cells or ranges
    in some formulas before naming them, the names
    will not be shown in the formulas.
  • To update these formulas with the new names
  • Click on
  • Formulas tab gt Defined Names group gt Define Name
    drop-down menu gt Apply Names option
  • The window in Figure 3.16 then appears.
  • Here, we can select the names that we wish to
    apply and leave the default options selected

25
Figure 3.15
  • The volume was calculated before the Length,
    Width, and Depth names were given
  • Original formula C3C4C5
  • After Applying the defined names, the formula is
    updated to LengthWidthDepth

26
Figures 3.16
  • Selecting the names desired to apply to the
    formula cell.
  • Other options are also shown in this dialog box.

27
Create
  • Creating Names Used when row and column labels
    are already given in a table.
  • Highlight the entire table
  • Click on
  • Formulas tab gt Defined Names group gt Create from
    Selection command
  • Select one of the following options to determine
    which table name should be used
  • Top row
  • Left column
  • Bottom row
  • Right column
  • These names will be given to the entire row or
    column of data

28
Figure 3.17
  • We select Left column from the Create Names
    screen (Figure 17.a)
  • The names have been created for cells C3 through
    C5 (Figure 17.b)

29
Figure 3.18
  • In this Parameters table, the rows have titles
  • Therefore, the Left column option is used to
    Create the range names for each row in the table
    (Figure 18.a)
  • The columns also have titles
  • Therefore, we can use the Top Row option to
    Create the range names for each column in the
    table (Figure 18.b)

30
Figure 3.19
  • To name each element of our table completely
  • Select a cell from the table (C3E5),
  • Click on
  • Formulas tab gt Defined Names group gt Define Name
    command
  • Type an appropriate cell name in the New Name
    dialog box

31
Formulas and Constants
  • Names can be used to refer to formulas and
    constants
  • Use the Define method
  • For constants
  • Click on
  • Formulas tab gt Defined Names group gt Define Name
    command
  • Instead of referring to a cell or range, type an
    followed by a number value in the Refers to
    area at the bottom of the New Name dialog box
  • A constant value can be used to make calculations
    with a common multiplier value.

32
Figures 3.21
  • For formulas
  • Click on
  • Formulas tab gt Defined Names group gt Define Name
    command
  • Instead of referring to a cell or range, type a
    formula in the Refers to area at the bottom of
    the New Name window
  • The product formula calculates the product of the
    values in cells D3, D4, and D5 of Sheet 1
  • The sum formula calculates the sum of the values
    in the range D3D5 of Sheet 1

33
Summary
  • There are four basic types of referencing.
  • In relative referencing (B2), row and column
    values change.
  • In absolute referencing (B2), neither the row
    nor column value changes.
  • For row absolute (B2), the row value does not
    change, but the column value does.
  • For column absolute (B2), the column value does
    not change, but the row value does.
  • R1C1 notation refers to a cells position
    relative to the origin cell where the formula is
    entered.
  • Can also reference worksheets and workbooks.
  • Circular referencing is a referencing loop
    between cells.
  • There are three basic ways to name cells.
  • Use defining names to name cells, ranges,
    constants, and formulas.
  • Use creating names when row and column labels are
    already given in a table.
  • Applying names is necessary to update formulas
    with new cell and range names.
  • Names can also be created for formulas and
    constants to make referencing in longer formulas
    clearer.
  • A simpler way to create range names are using the
    name window.

34
Additional Links
  • (place links here)
Write a Comment
User Comments (0)
About PowerShow.com