.comment-link {margin-left:.6em;}
Location: San Diego, California, United States

Tuesday, March 15, 2005

Transpose-relative Cell References for Pairwise Comparisons

I've proposed to Microsoft a new modifier for cell references. In addition to relative references and absolute references, this modifier (for which I'm suggesting the percent symbol, but any symbol will do) would transpose the reference: if applied to the column, it would advance the column based on the row of the source cell; if applied to the row, it would advance based on the source's column.

With this formula in B1:

filled into C1 and B2, you get:
=$A1*$A%2 (C1)
=$A2*$A%1 (B2)

My desire for this stems from wanting to do a lot of pairwise comparisons, and wanting to do it without fancy formulas using TRANSPOSE() or (as I've always done) copy-paste-special-transposing the source data column into a row above the table.

I have implemented this in my own spreadsheet, and I like how it works.


Post a Comment

Links to this post:

Create a Link

<< Home