Excel: Incrementing Formula Row Copied Across Columns
November 7, 2009 1:38 PM Subscribe
Excel question: I need a row
number to automatically increment as I copy a formula horizontally across columns. Sample spreadsheet downloadable here
, or viewable as a Google Doc here
. Further details inside.
posted by Conrad Cornelius o'Donald o'Dell to computers & internet (12 answers total) 1 user marked this as a favorite
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 comparison.
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.