Excel question: I need a row number to automatically increment as I
copy a formula horizontally across columns. Sample spreadsheet
Or viewable as a Google Doc here:
To elaborate, the formula in question starts in Column T. In T2, the
formula compares Node 1 to itself - hence the zero. In T2, it compares
Node 1 to Node 2, in T3, Node 1 to Node 3, and so on. Column U does
the same thing, except it compares Node 2 to Node 1, Node 2 to Node 2,
Node 2 to Node 3, and so on. I want to copy out my formula so that all
50 nodes in the matrix are filled in, in this manner.
The formula increments properly when copied vertically. Take a look at
the representative portion of the formula right after the ABS in cell
T2: ($C$2-$C2). The formula stays "anchored" on Node 1 via the $C$2.
The second part, $C2, increments as you copy it down, to $C3, $C4 and
so on. (The same is true for the rest of the formula - it just goes ($D
$2-$D2), ($E$2-$E2) and so on.)
Now take a look at Column U. The "anchor" changes from $C$2 to $C$3.
That's because Node 2 is now the basis for comparison. In Column V,
the anchor changes to $C$4, because Node 3 is the basis for
The thing is, the anchors in Columns U and V were updated by hand.
That is to say, if you copy the formula in Column T one cell to the
right, nothing increments. I realize that this is "proper" Excel
behavior - when you copy formulas horizontally, generally only the
column will increment. But I would really love a way to force the row
number of the anchor to automatically increment as I copy the formula
to the right. (Note that the anchor has to retain the $ before the row
number because when I copy the formula down, that portion of the
formula must stay fixed.)
Please let me know if you need any further clarifications. And if you
have any suggestions, I would be very grateful. Thank you.