SpreadsheetBased Decision Support Systems Chapter 3 Referencing and

Spreadsheet-Based Decision Support Systems Chapter 3: Referencing and Names Prof. Name Position University Name name@email. com (123) 456 -7890

Overview v v 3. 1 Introduction 3. 2 Referencing Cells 3. 3 Names for Cells, Ranges, and Worksheets 3. 4 Summary 2

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

Referencing Cells v Relative Referencing and Absolute Referencing v R 1 C 1 Notation v Referencing Other Worksheets and Workbooks v Circular Referencing 4

Relative Referencing and Absolute Referencing v There are four basic types of referencing – – relative absolute row absolute column absolute referencing v Relative Referencing: Row and column value will change (B 2). v Absolute Referencing: Neither row nor column value will change ($B$2). v Row Absolute: Row value does not change, but column value will change (B$2). v Column Absolute: Column value does not change, but row value will change ($B 2). 5

Figure 3. 1 v The SUM function is entered in column B 16 using relative referencing: =SUM(B 4: B 13) v When the function is copied to cell C 16, the function values shift relative to the new position 6

Figure 3. 2(a) v In cell E 12, the sum from cell B 16 is multiplied by the value in cell E 4: =E 4*B 16 v Copying this formula to cell E 13, does NOT yield E 4*C 16, but rather F 4*C 16 7

Figure 3. 2(b) v Absolute referencing will keep E 4 constant in both formulas: $E$4*B 16 and $E$4*C 16. 8

Referencing (cont’d) v Row absolute referencing places the $ in front of the row number – column letter $ row number – A$1 v Column absolute referencing places the $ in front of the column letter – $ column letter row number – $A 2 9

Figures 3. 3(a) and 3. 3(b) v Numbers in row headings are same as column headings and we want one row number multiplied by one column number. v 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 10

R 1 C 1 Notation v R 1 C 1 notation: Refers to a cell’s position relative to the origin cell where the formula is entered. v The cell in which the formula is entered is considered to have position R[0]C[0]. – A formula entered in A 1 to refer to A 2 would be: § R[1]C[0] or R[1]C – A formula entered in B 1 which refers to A 3 would be: § R[-1]C[2] v To switch row and column titles to R 1 C 1 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 R 1 C 1 reference style from the list of Working with formulas options on that tab. 11

Figure 3. 4 v The first value of the table is referenced in cell R 1 C 1 (or A 1) v The sum of the first two table values is calculated in cell R 2 C 1 (or A 2) 12

Figure 3. 5 v The formula from cell R 2 C 1 is copied to the three cells below. v Notice the actual formula is identical in each cell. 13

Figure 3. 6 v Excel offers a Formula View that shows cells by their formulas, instead of their calculated values. – Click on: Formulas tab > Formula Auditing group > Show Formula command 14

Referencing Other Worksheets and Workbooks v Cells can also contain formulas which reference cells outside of the current worksheet or workbook. v Worksheet in the same workbook: Sheet 1!A 1 v Workbook: [Data. xls]Sheet 1!A 1 v Workbook with spaces in the title: ‘[Collected Data. xls]Sheet 1’!A 1 v Workbook not currently open in Excel: ‘C: My DocumentsProject[Collected Data. xls]Sheet 1’!A 1 15

Figures 3. 7 and 3. 8 v Data is in one worksheet and calculations are made in another worksheet 16

Circular Referencing v A referencing loop in a spreadsheet creates a circular reference. – Example: cell A 1 has the value “=B 1, ” cell B 1 has the value “=C 1, ” and cell C 1 has the value “=A 1. ” – This referencing loop causes an error in Excel. v The first possible solution to this problem requires us to rearrange our references or to modify our formula. v 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. 17

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

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

Define v Defining Names: Names cells, ranges, constants, and formulas. – Click on: § Formulas tab > Defined Names group > Define Name drop-down menu > Define Name option 20

Figure 3. 9 v Rename the cell with the length value as Length v The name will refer to Sheet 1!$C$3 21

Figure 3. 12 v You can also use Define to name a range of cells v After naming each of the first three table values, we can name the entire range of table values: Sheet 1!$C$3: $C$5 as “Parameters. ” 22

Figures 3. 13 and Figure 3. 14 v Name references can be modified using the Name Manager dialog box – Click on: § Formulas tab > Defined Names group > Name Manager command – Select a name and then click on the Edit command 23

Apply v Applying Names: Updates formulas with new cell and range names. v If you have previously referenced cells or ranges in some formulas before naming them, the names will not be shown in the formulas. v To update these formulas with the new names: – Click on: § Formulas tab > Defined Names group > Define Name drop-down menu > 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 24

Figure 3. 15 v The volume was calculated before the Length, Width, and Depth names were given v Original formula: =C 3*C 4*C 5 v After Applying the defined names, the formula is updated to: = =Length*Width*Depth 25

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

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

Figure 3. 17 v v We select Left column from the Create Names screen (Figure 17. a) The names have been created for cells C 3 through C 5 (Figure 17. b) 28

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

Figure 3. 19 v To name each element of our table completely: – Select a cell from the table (C 3: E 5), – Click on: § Formulas tab > Defined Names group > Define Name command – Type an appropriate cell name in the New Name dialog box 30

Formulas and Constants v Names can be used to refer to formulas and constants v Use the Define method v For constants: – Click on: § Formulas tab > Defined Names group > 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 v A constant value can be used to make calculations with a common multiplier value. 31

Figures 3. 21 v For formulas: – Click on: § Formulas tab > Defined Names group > 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 v The product formula calculates the product of the values in cells D 3, D 4, and D 5 of Sheet 1 v The sum formula calculates the sum of the values in the range D 3: D 5 of Sheet 1 32

Summary v There are four basic types of referencing. – – v v In relative referencing (B 2), 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 ($B 2), the column value does not change, but the row value does. R 1 C 1 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. v v Names can also be created formulas and constants to make referencing in longer formulas clearer. A simpler way to create range names are using the name window. 33

Additional Links v (place links here) 34