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: