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

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. 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 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: • 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 formulasoptions 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 FormulaView that shows cells by their formulas, instead of their calculated values. • Click on: Formulas tab > Formula Auditing group > 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.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

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 > Defined Names group > Define Name drop-down menu > Define Name option

21. Figure 3.9 • Rename the cell with the length value as Length • The name will refer to Sheet1!$C$3

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

25. Figure 3.15 • The volume was calculated before the Length, Width, and Depth names were given • Original formula: =C3*C4*C5 • After Applying the defined names, the formula is updated to: = =Length*Width*Depth

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 > Defined Names group > 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 Leftcolumn 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 (C3:E5), • Click on: • Formulas tab > Defined Names group > 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 > 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 • A constant value can be used to make calculations with a common multiplier value.

32. Figures 3.21 • 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 • 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 D3:D5 of Sheet 1

33. 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. • A simpler way to create range names are using the name window.

34. Additional Links • (place links here)