Chapter 3: Referencing and Names

1. Spreadsheet-Based Decision Support Systems Chapter 3: Referencing and Names Prof. Name name@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 • 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 absolutereferencing • 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 FormulaView 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 Leftcolumn 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)