Transpose-relative cell references would be useful.

[Guest]

I use multiplication tables to give new users a sense of the power of mixed
absolute and relative references; with a row of numbers along the top and a
column along the side, a single formula can easily produce the table.

But sometimes I don't want to copy/paste-special-transpose to produce the
source row from the source column. I'd like a new symbol, let's say "%", to
mean "transpose-relative" as "$" means "absolute". So if my source was in
A1:A10, I could put "=$A1*$A%1" into B1, fill right and down, and be done.

The "%" would mean that as the formula's _column_ changes, the _row_ of the
reference changes, or vice versa if the % precedes the column reference.

For extra credit, make double-clicking the grow-handle of such a cell expand
to the appropriate square and fill along both axes.

 

[Myrna Larson]

If I could only understand what you mean, perhaps there's a way to do it. But
you won't get anywhere re the % symbol. It already has a meaning: percent.

 

[Harlan Grove]

carlmanaster wrote...
....

But sometimes I don't want to copy/paste-special-transpose to produce the
source row from the source column. I'd like a new symbol, let's say "%", to
mean "transpose-relative" as "$" means "absolute". So if my source was in
A1:A10, I could put "=$A1*$A%1" into B1, fill right and down, and be

done.

Wouldn't you need your source column in A2:A11 in order for there to be
room in row 1 to hold the source row in B1:K1? Once that correction to
your specs is made, you could use =OFFSET($A$1,COLUMN()-1,0) in B1 and
fill right. Or you could select B1:K1 and enter the array formula
=TRANSPOSE(A2:A11).

The "%" would mean that as the formula's _column_ changes, the _row_ of the
reference changes, or vice versa if the % precedes the column

reference.

And you expect this to be easier for new users to understand?

Unfortunately, Excel is not APL, so no A jot-dot-times A. But also not
so bad *IF* you use array formulas. Select B2:K11 and enter the array
formula

=A2:A11*TRANSPOSE(A2:A11)

Perhaps not straightforward, but also not rocket science.

For extra credit, make double-clicking the grow-handle of such a cell expand
to the appropriate square and fill along both axes.

If you want extraneous functionality, there are several open source
spreadsheets you could modify to satisfy your whims. Earn your own
extra credit.

 

[carl.manaster]

Thanks, Myrna,

I'm not particularly fussy about the symbol. And my post wound up here
rather inadvertently; I submitted it as a suggestion to MS, but their
suggestion form evidently routes things to the newsgroup. So...

Since I obviously didn't explain myself very well for this audience,
let me try again.

If I Fill Right from B2 into C2 this formula:

=$A2*B$1

I get

=$A2*C$1

If I Fill Down into B3, I get

=$A3*B$1

And that's how I would fill out a multiplication table, filling column
A and row 1 with the initial values.

But let's say, for whatever reason, I didn't want to duplicate the
values from column A into row 1. I can still put

=$A2*A2

into B2, and Filling Down will still work as desired. But I also want
to Fill Right - I still want a multiplication table - but when I Fill
Right, I get

=$A2*B2

But I _want_

=$A2*A3

I want the _row_ of the second term to change, although I have copied
the cell to another _column_.

That's what I'm proposing, and if you've got a better name for it than
"transpose-relative references," I'd love to hear it.

Is that any clearer?

Thanks,
--Carl

 

[carl.manaster]

Hi, Harlan,

Thanks for the various suggestions with TRANSPOSE, etc.; I was making
this suggestion to MS and it got to the newsgroup without my planning
it.

And you expect this to be easier for new users to understand?

Well - let's put it this way. I've introduced a lot of people to
relative and absolute reference, and I would guess somewhere between 10
and 20% of them "get" it, or anyway keep it. My _guess_ is that
something like 80% of those who "get" absolute & relative references
could also "get" transpose-relative references, and some fraction of
them would actually have a use for it.

I occasionally find myself doing a lot of pairwise comparisons, and I
know there are fields in which that's common; this notation seems
nicer, to me, than the kinds of formulas you propose. Look, it's not
really much of a hassle to do copy-paste-special-transpose; it just
seems like there are times I would prefer to be able to do it this way.

If you want extraneous functionality, there are several open source
spreadsheets you could modify to satisfy your whims. Earn your own
extra credit.

Well, actually, I'm writing my own (closed-source) spreadsheet, and
it's in the course of developing and testing it that I came up with
this idea. I figure the idea will benefit a whole lot more people if
Microsoft puts it into Excel than if I put it into mine!

Peace,
--Carl

 

[Harlan Grove]

(e-mail address removed) wrote...
....

If I Fill Right from B2 into C2 this formula:

=$A2*B$1

I get

=$A2*C$1

If I Fill Down into B3, I get

=$A3*B$1

And that's how I would fill out a multiplication table, filling column
A and row 1 with the initial values.

But let's say, for whatever reason, I didn't want to duplicate the
values from column A into row 1. I can still put

=$A2*A2

into B2, and Filling Down will still work as desired. . . .

Only if you desire something different. If A2:A11 contains
{1;2;3;4;5;6;7;8;9;10}, and if B1:K1 would have contained
{1,2,3,4,5,6,7,8,9,10}, then =$A2*A2 would give the same result as
=$A2*B$1, or 1 * 1 = 1, but filling it down makes A3 contain =$A3*A3,
which would be 2 * 2 = 4 rather than equal to =$A3*B$1, which would
have been 1 * 2 = 2.

. . . But I also want to Fill Right - I still want a multiplication
table - but when I Fill Right, I get

=$A2*B2

But I _want_

=$A2*A3

I want the _row_ of the second term to change, although I have copied
the cell to another _column_.

That's what I'm proposing, and if you've got a better name for it than
"transpose-relative references," I'd love to hear it.

Is that any clearer?

Clear, to which the only response is you can't always get what you
want, but if you try some times you just might find you get what you
need . . . See the TRANSPOSE function in online help.

 

[Harlan Grove]

(e-mail address removed) wrote...

Thanks for the various suggestions with TRANSPOSE, etc.; I was making
this suggestion to MS and it got to the newsgroup without my planning
it.

....

The functionality already exists via TRANSPOSE or OFFSET/COLUMN/ROW
functions. Syntactically, it'd be awkward to add, and speaking from my
own experience not particularly useful but potentially confusing.

But lets explore the syntax. Let's say you had the following formula in
B2,

=$A2*$A%2

which is equivalent to A2*A2 in cell B2 because it's equivalent to the
R1C1 expression RC[-1]*RC[-1]. Fill B2 right into C2 and down into B3.
Presumably the formulas in C2 and B3 should look like

C2: =$A2*$A%3
B3: =$A3*$A%2

You'd need to support A%2, %A2 and %A%2. If B2 contained =A%2 and were
filled into B3 and C2, the formulas in those cells would become

C2: =B%3 (column changes normally, row changes transpositionally)
B3: =A%2 (no change)

If B2 had contained =%A2,

C2: =%A2 (no change)
B3: =%B3 (column changes transpositionally, row changes normally)

If B2 had contained =%A%2,

C2: =%A%3 (column doesn't change, row changes transpositionally)
B3: =%B%2 (column changes transpositionally, row doesn't change)

While this syntax is well-defined, I'll bet you most people would find
it confusing to opaque.

 

[carl.manaster]

Hi, Harlan,

=$A2*$A%2

You have it exactly right. And it's not _terribly_ hard to work out -
the columns must be absolute, because the only source data is in column
A; one of the row terms must be relative, so that it changes normally;
the other must therefore be transpose-relative.

While this syntax is well-defined, I'll bet you most people would find
it confusing to opaque.

I'm sure they would - most people, after all, find absolute references
confusing. But I think it may be useful enough in enough situations
that a number of people would be happy to have it. With regard to
supporting %A1, A%1, and %A%1 - I agree, for purposes of symmetry and
simple rules. But for the purpose I have in mind - pairwise comparison
among items in a column, all you really need to know is $A%1. It's
hard for me to imagine a need for %A%1, although I guess it could give
you a "transpose-in-place" kind of functionality that might be nice if
you're pasting data into a template.

Thanks for all the feedback.

Peace,
--Carl