EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 3 Referencing and Names
Prof. Name
name_at_email.com Position
[phone] 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
• Use natural range 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
Figure 3.3(a)

• Numbers in row headings are same as column
  headings and we want one row number multiplied by
  one column number

11
Figure 3.3(b)

• 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

12
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
• choose Tools gt Options from the menu
• Click on the General tab
• Check R1C1 reference style from the list of
  options

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

14
Figure 3.5

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

15
Figure 3.6

• Excel offers a Formula View that shows cells by
  their formulas, instead of their calculated
  values.
• Choose Tools gt Options from the menu
• Click on the View tab
• Select Formulas from the Windows Options

16
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

17
Figures 3.7 and 3.8

• Data is in one worksheet and calculations are
  made in another worksheet

18
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.
• Tools gt Options gt Calculations and select
  Iteration
• Excel performs a specified number of iterations,
  or repetitions, of the calculations, to try to
  find a solution applicable to all equations.

19
Names for Cells, Ranges, and Worksheets

• The Name Window
• Define
• Apply
• Create
• Formulas and Constants
• Natural Range Names

20
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
• View drop-down list of current object names in
  workbook
• This is the simplest and most common way to
  assign names

21
Define

• Defining Names Names cells, ranges, constants,
  and formulas.
• Insert gt Name from the menu
• Select Define

22
Figure 3.9

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

23
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

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 Insert gt Name gt Apply from the Excel menu
• Select the names you wish to apply
• (leave the default options selected)

25
Figure 3.13(a)

• The volume was calculated before the Length,
  Width, and Depth names were given
• Original formula C3C4C5

26
Figures 3.13(b) and 3.14

• After Applying the defined names, the formula is
  updated

27
Create

• Creating Names Used when row and column labels
  are already given in a table.
• Highlight the entire table
• Select Insert gt Name gt Create from the menu

28
Create (contd)

• 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

29
Figure 3.15

• 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

30
Figure 3.16(b)

• The columns also have titles
• Therefore, we can use the Top Row option to
  Create the range names for each column in the
  table

31
Formulas and Constants

• Names can be used to refer to formulas and
  constants
• Use the Define method
• For constants
• Click Insert gt Name gt Define from the menu
• Instead of referring to a cell or range, type a
  numerical value
• For formulas
• Click Insert gt Name gt Define from the menu
• Instead of referring to a cell or range, type a
  formula which uses cells or ranges in the workbook

32
Figure 3.18

• A constant value can be used to make calculations
  with a common multiplier value

33
Figure 3.19(a)

• A product formula is created to be used in the
  workbook
• This formula calculates the product of the values
  in cells D4, D5, and D6 of Sheet 1

34
Figure 3.19(b)

• The sum formula calculates the sum of the values
  in the range D4D6 of Sheet 1

35
Natural Range Names

• Convert column and row titles of data tables into
  corresponding range names.
• Tools gt Options gt Calculations and select Accept
  Labels in Formulas.
• Formulas or references that use these natural
  range names also automatically shift respectively
  when copied or moved.

36
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.
• Two simpler ways to create range names are using
  the name window or natural range names.

37
Additional Links

• (place links here)