Excel: Incrementing Formula Row Copied Across Columns

November 7, 2009 1:38 PM

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.

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.

posted by Conrad Cornelius o'Donald o'Dell to Computers & Internet (12 answers total)

I don't have time to come up with the whole methodology just now, but I think use of the INDIRECT function would be helpful.
posted by cerebus19 at 2:08 PM on November 7, 2009

I'm not terribly familiar with Indirect, but to build on cerebus19's answer, I think you could use it with a data sheet to get what you want. By that I mean, on a different tab called "data," you have the numbers 0 to x, where x is the total number of rows you will work with, running across a row (1 would be in A1, 2 would be in B1, and so on). You would then use the Indirect function to get the value of the row number you want by getting the value in the column in the data tab. As you paste across columns in your primary tab, your formula will refer to a different column in the data tab whose value is row number you want.
posted by ifandonlyif at 2:56 PM on November 7, 2009

Do you know any kind of scripting language? I think this would be pretty trivial to implement in (python| perl|ruby). Export your data as a csv, pull it in, crunch the numbers and spit it back out.
posted by chrisamiller at 3:02 PM on November 7, 2009

Response by poster: A good suggestions, but I do not know any scripting languages.
posted by Conrad Cornelius o'Donald o'Dell at 3:11 PM on November 7, 2009

This won't be as quick as you might like, but it will be WAY quicker than manually editing each cell.

I've needed to do something similar and this is my solution.


Copy the entire contents of column V all the way across to column BQ (node 50)

Select all the cells from W50 to W51 (make sure these cells are highlighted before moving on)


Click "Edit" then "replace"
In the "Find what" box type $2
In the "Replace with" box type $3
Click "Replace all"

Now select all the cells in column X

Click "Edit" then "replace" again
leave $2 in the find what box
change the replace with box to $4
Click "Replace all"

Repeat for each column, increasing the number by one each time.

If you're quick with your keyboard (and know tricks like "shift, end down" to highlight all the cells in a column) this should take about 5 or 6 minutes.

Good luck.
posted by robverb at 5:40 PM on November 7, 2009

I'm afraid I haven't got the time to get this working exactly, but you need a combination of:

OFFSET(), COLUMN() and ROW()

Basically, you use the first one like this: OFFSET(A1, row_offset_number, column_offset_number)

e.g. OFFSET(A1, 2, 4)

This will give the value in E3. If you drag this down vertically, then the A1 will increment properly.

COLUMN() and ROW() return the current column and row in whatever cell is calling them. This is invaluable with OFFSET() for referencing different columns based on your current row, or vice versa.

In your example, in Column V, the anchor changes to $C$4, because Node 3 is the basis for comparison.

To access C4 programmatically from column V, you could do this:

=OFFSET(A1, COLUMN()-19, 2)

Why -19? Because V is the 22nd column, so 22-19=3, so a row offset of 3 from row 1, i.e. the fourth row.

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

Basically, as per the above, the way to change the row number as you change column is to use an OFFSET and set its row argument using a modification of the current cell's COLUMN().

Good luck!
posted by runkelfinker at 6:16 PM on November 7, 2009

I wanted to really be sure that my answer would work, so I went ahead and did it. I tried to upload it to Google Docs for you, but it said the file was too large. I uploaded it to my own website, so if the link works, you should be able to download it and go.


posted by robverb at 8:04 PM on November 7, 2009

Looks like the Link was missing from my response.
posted by robverb at 8:05 PM on November 7, 2009

Best answer: Try this:

Paste this

=(((ABS(INDEX($C$2:$C$51,T$52)-$C2))*0.1)+((ABS(INDEX($D$2:$D$51,T$52)-$D2))*0.1)+((ABS(INDEX($E$2:$E$51,T$52)-$E2))*0.1)+((ABS(INDEX($F$2:$F$51,T$52)-$F2))*0.1)+((ABS(INDEX($G$2:$G$51,T$52)-$G2))*0.1)+((ABS(INDEX($H$2:$H$51,T$52)-$H2))*0.1)+((ABS(INDEX($I$2:$I$51,T$52)-$I2))*0.1)+((ABS(INDEX($J$2:$J$51,T$52)-$J2))*0.1)+((ABS(INDEX($K$2:$K$51,T$52)-$K2))*0.1)+((ABS(INDEX($L$2:$L$51,T$52)-$L2))*0.1)+((ABS(INDEX($M$2:$M$51,T$52)-$M2))*0.1)+((ABS(INDEX($N$2:$N$51,T$52)-$N2))*0.1)+((ABS(INDEX($O$2:$O$51,T$52)-$O2))*0.1)+((ABS(INDEX($P$2:$P$51,T$52)-$P2))*0.1)+((ABS(INDEX($Q$2:$Q$51,T$52)-$Q2))*0.1)+((ABS(INDEX($R$2:$R$51,T$52)-$R2))*0.1)+((ABS(INDEX($S$2:$S$51,T$52)-$S2))*0.1))/15

into T2, then fill down to T51. Put

=MATCH(T$1,$A$2:$A$51,0)

into T52. Select all of T2:T52, then fill across to BQ2:BQ52.

This method matches the column headers in T1:BQ1 against the row headers in A2:A52, returning the relative index where the match occurs, then uses that to index the appropriate cell to subtract from. If you like, you can dispense with MATCH and just use an auto-incrementing auto-fill, or an incrementing formula like =s1+1 instead. You can hide row 52 to stop it crapping up the look of your sheet. I don't know whether you're ever going to have more than 50 nodes; if so, it would probably pay you to use row 2 where I've used row 52.

Tested in OpenOffice.org Calc. If I recall correctly, Excel has identical INDEX and MATCH functions.
posted by flabdablet at 3:52 AM on November 8, 2009 [1 favorite]

Seems to work in Google Docs.
posted by flabdablet at 4:10 AM on November 8, 2009

Response by poster: Thank you, everyone. I will try these out.
posted by Conrad Cornelius o'Donald o'Dell at 10:29 AM on November 8, 2009

Response by poster: Flabdablet, I wound up trying yours first. It seems to have worked like a charm! Very elegant, very simple. Thank you so much - and thanks to everyone else for their ideas as well.
posted by Conrad Cornelius o'Donald o'Dell at 2:56 PM on November 8, 2009