Not Just Your Average Function in Excel 2010 Part 3

The following table enumerates the relationship between the kinds of data I could enter in a cell and what I would see in that cell.  In every case, what I would copy is posted in the left column of Table 3–2 below: Table 3–2. Cell Entries and Cell Displays .

Again, what gets copied is what you’ve actually typed in the cell. And if you copy any expression which contains a cell  reference, what will happen is that the cell references in the destination cells—the cells to which you’ve copied—will change,  corresponding to the distance in either rows or columns from the original cell you’ve copied.

If I copy the number 1, either 1 or a 100 times, I’ll see nothing but 1’s in the range to which I’ve copied. But if I copy this: =C7 what I’ll see depends on where I’ve copied it. If that =C7 was positioned in cell A2 and I copy it to cell A3, I’ll see this in that  destination cell: =C8 By way of a more pertinent example, if I go to cell I10 in our grading worksheet and copy Alice’s formula: =AVERAGE(D10:H10) down one row to I11—Derek’s row—we’ll see: =AVERAGE(D11:H11) You’re probably starting to get  the idea. If I were to copy Alice’s formula all the way down to say, cell I20000—and there’s no reason why I couldn’t—I’d see: =AVERAGE(D20000:H20000) See what’s changed, and what’s remained the same? Remember that a cell address comprises a lettered column reference and a number row reference—and when you copy cell references down a column, only the original  row references change, commensurate with the distance you’ve traveled from the original cell. That’s because you’ve moved  down rows, and haven’t shifted any columns—and the destination results reflect the amount of movement from the source cell  reference.

If on the other hand, were I to copy Alice’s formula to cell L10, I’d see: =AVERAGE(G10:K10) And see why? In this case, I’ve  copied Alice’s original formula three columns to the right, such that only the column parts of its cell references—that is, the  letters—have changed, again corresponding to the degree of movement from I10. Thus G is three column letters “away” from  D, and K is three columns removed from H. And this time there’s been no change in the row references—the numbers, because we’ve copied across columns only, remaining on the same row as Alice’s average. We’re still on row 10 in this case.

A quick, acronymic way for nailing this row/column movement question is CARD, which stands for: Columns Across, Rows  Down. Copy a cell reference across, and the column letters change; copy it down—or up—and the rows numbers change. In  any event, we’ve encountered a foundational spreadsheet feature—relative references —which describes what happens by  default when you copy a cell reference to any other cell. We can see now that if I click on Alice’s original AVERAGE formula, I  should be able to copy it down the I column for as many rows as I need, confident in the knowledge that, as long as the original  formula is correct, I should be able to compute all the other students’ averages correctly. Put another way, I need only write  AVERAGE once—and then copy it; and so you can see why this tool is so potent.

And note, by the way, that cells can certainly team cell references with simple numeric values; just keep in mind that copying  such a cell will only change the cell reference. Thus if I write this: