Automatically Incrementing Row in Formula Copied Horizontally AcrossColumns

[DavidNYC]

Excel question: I need a row number to automatically increment as I
copy a formula horizontally across columns. Sample spreadsheet
downloadable here:

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

Or viewable as a Google Doc here:

http://spreadsheets.google.com/ccc?key=0An0gAhGgwloLdDQtdlJldDF1bzdIRW93VVdSMFdHdkE

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]

Your formula simplifies to the following without fixing the colum
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))