chapter 3: referencing and names spreadsheet-based decision support systems prof. name...

Chapter 3: Referencing and Names

Spreadsheet-Based Decision Support Systems

Prof. Name name@email.comPosition (123) 456-7890University 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 ($B$2).

Row Absolute: Row value does not change, but column value will change (B$2).

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(B4:B13)

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: =E4*B16

Copying this formula to cell E13, does NOT yield E4*C16, but rather F4*C16

8

Figure 3.2(b)

Absolute referencing will keep E4 constant in both formulas: $E$4*B16 and $E$4*C16

9

Referencing (cont’d)

Row absolute referencing places the $ in front of the row number– column letter $ row number

– A$1

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 cell’s position relative to the origin cell where the formula is entered.

The cell in which the formula is entered is considered to have position R[0]C[0].– A formula entered in A1 to refer to A2 would be:

R[1]C[0] or R[1]C

– A formula entered in B1 which refers to A3 would be: R[-1]C[2]

To switch row and column titles to R1C1 notation:– choose Tools > 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 > 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.xls]Sheet1!A1

Workbook with spaces in the title: ‘[Collected Data.xls]Sheet1’!A1

Workbook not currently open in Excel:

‘C:\My Documents\Project\[Collected Data.xls]Sheet1’!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 > Options > 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 > 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!$C$3

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!$C$3:$C$5

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 > Name > 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: =C3*C4*C5

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 > Name > Create from the menu

28

Create (cont’d)

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 > Name > Define from the menu

– Instead of referring to a cell or range, type a numerical value

For formulas:– Click Insert > Name > 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 D4:D6 of Sheet 1

35

Natural Range Names

Convert column and row titles of data tables into corresponding range names. – Tools > Options > 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 ($B$2), neither the row nor column value changes. – For row absolute (B$2), 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 cell’s 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)