Lesson 13

Getting Started (Continuation of Spreadsheet, continued)

Recall from lesson 8 that a spreadsheet is made up of rows and columns, and each small part of the grid is called a cell. Each cell is named or addressed by its column letter and row number. We can use a cells address to reference it from within another cell. We will begin PART I by exploring what is meant by cell referencing.

Part I

In this part, we will review entering formulas and then you will learn about two special types of cell referencing.

1. Inputting a Formula [formulas must begin with an = sign]

2. Relative Cell References

A relative cell reference changes according to its location in the spreadsheet. Let’s experiment.

3. Another Example of Relative Cell Referencing

Hint: You can press and drag on the line dividing columns to resize them.

Important...Please Read

Position is the key to understanding relative cell referencing. You can think of “relative” as meaning where you currently are.

For example, if we have A1 = 2xB1, then A1 is referencing a cell that is one column to the right. The one column to the right is what is maintained when we copy cell A1 to another location. If you copy A1 to cell C3, C3 will contain =2xD3 (a reference to the cell that is one column to the right).

Another example: If D5 = 6xB2, then D5 is referencing a cell that is two columns to the left and three rows up. Now, if you copy D5 to C4, C4 will contain = 6xA1. Does this help you understand example 3 above?

4. Errors in Referencing

5. Data Type Errors

6. Getting Ready to Work with Percents

If we divide one number by another number and then multiply by 100, we have what percent the first number is of the second number. If we do not multiply by 100 then we have just the decimal equivalent of the percent.

7. Working with Percents

What percentage of the total is red? What about green? We need to calculate (# of each color)/(Total #) x 100.

Important...Please Read

We would like to have our formula change the cell reference for B2 but keep the reference to B6. So, we will change B6 to $B$6. This is called an absolute reference. Wherever we move $B$6, it will always reference cell B6.

We can tell a spreadsheet to keep the same row and/or column in a reference address by placing a $ sign in front of what we want to keep absolutely the same. For example, the column of A$6 will change when we copy it to a new location, but the row will always be 6.

8. Absolute Cell References

To keep an exact reference to cell B6 when we drag our formula, we just need to change B6 to $B$6.

Part I


Practice Exercises

Before beginning the practice exercises, open a word document, type in the following information and then save it as Lesson13 in your CASIO folder within My Documents:

  1. Please open the eActivity application.
  2. Open the eActivity named L13_PartI_a in the Lesson13 folder.
  3. Read the instructions inside the eActivity.
  4. Once you accomplish dragging cell C10 so that it creates a new reference to A2, get a screen capture. Paste it into your Lesson13 document (under a title of PART I).
  5. Save your work as an eActivity named L13_PartI_a_your initials here.
  6. Open the eActivity named L13_PartI_b in the Lesson13 folder.
  7. In L13_PartI_b, you will need to do the following:
    1. Type in the headings shown in the figure below and make them bold.
    2. Type in the hours worked (cells B3 through B7).
    3. Enter a formula to find the total hours.
    4. Enter a formula to find the percentage each day's hours are of the total number of hours.
    5. Enter a formula to find the total of the percentage formula.
  8. When you are finished, select your cell C7 (its formula should show in the status bar).
  9. Get a screen capture while the Spreadsheet is full screen. Add two blank spaces following the first screen capture and then paste this one.
  10. Save your work as an eActivity named L13_PartI_b_your initials here.