# Excel - Automatically Incrementing Row in Formula Copied Horizontally Across Columns

Asked By DavidNYC on 07-Nov-09 07:09 PM
```Excel question: I need a row number to automatically increment as I
copy a formula horizontally across columns. Sample spreadsheet

http://www.youshare.com/Guest/18365825fec3723c.xls.html

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
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.```

joel replied on 08-Nov-09 01:42 AM
```Your formula simplifies to the following without fixing the column
offset problem

Put into T2
=(0.1/15)*SumProduct(ABS(\$C\$2:\$S\$2-\$C2:\$S2))

To solve the offset problem use the following in cell T2

=(0.1/15)*SUMPRODUCT(ABS(\$C\$2:\$S\$2-OFFSET(\$C2,AVERAGE(COLUMN(T:T)-COLUMN(\$T:\$T)),0,1,17)))

--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229