Microsoft Office Tutorials and References
In Depth Information
MAX and MIN—Recording Highs and Lows
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:
=D5+7
in cell H2 and copy it down one row, I’ll get:
 
Search JabSto ::




Custom Search