Mathematical transpose in excel

Question

I'm currently working in excel, and I have to mathematically transpose a few cells (10*10 or 5r*10c):

-------------------------------
| .. | .. | .. | .. | .. | .. |
| 21 | 22 | 23 | 24 | 25 | .. |
| 11 | 12 | 13 | 14 | 15 | .. |
|  1 |  2 |  3 |  4 |  5 | .. |
-------------------------------

Must become

-------------------------------
| .. | .. | .. | .. | .. | .. |
|  3 | 13 | 23 | 33 | 43 | .. |
|  2 | 12 | 22 | 32 | 42 | .. |
|  1 | 11 | 21 | 31 | 41 | .. |
-------------------------------

Now I'm not a mathematician (I'm more ore less a programmer at the moment), but I came up with: F(y)=((MOD(x,10)-1)*10)+(1+((x-MOD(x,10))/10)) (x is the value in the pre-block a the top, y is the value in the pre-block below.) Now this works fine up to a certain point. Can somebody help me?

Please note that I'm not comfortable reading fancy/graphical formulas ;-)

i think you're not at the right place to ask this question – Applied mathematician Feb 14 '13 at 11:16 — Applied mathematician, Feb 14 '13 at 11:16
@JoyeuseSaintValentin, agree with you 100% – Vikram Feb 14 '13 at 11:21 — Vikram, Feb 14 '13 at 11:21

score 3 · Answer 1 · answered Feb 14 '13 at 10:41

3

I think that, alternatively, you can simply use the TRANSPOSE() function. Just make sure that, after entering your formula, you press <SHIFT><CTRL><ENTER> instead of the usual <ENTER>. Does this help?

answered Feb 14 '13 at 10:41

Řídící

3,210

I came here to say the same, and to confirm that you can indeed do this. Be sure that you select the appropriate size range when you author the function. That is, if you're transposing a $3 \times 4$ range, select a $4 \times 3$ range. – Jakob Streipel Feb 14 '13 at 10:43
Thanks. I do know of this function but thing is that I really need to do it mathematically; I might not have a range of cells but just the number '5', which would be '41'. So it is not really a transpose, it should be pure math. – Feb 14 '13 at 10:48
what JvN is trying to do is not "Transpose"!, he wants matrix rotated about y=x line instead of x+y=1 line. And also this question has got nothing to do with calculus or linear algebra. – Vikram Feb 14 '13 at 11:07
1

This answer has been superseded by the one addressing the OP's cleverly disguised actual needs. – Řídící Feb 14 '13 at 12:36

score 0 · Accepted Answer · edited Feb 14 '13 at 12:03

0

y=(MOD(x-1,10))*10+INT((x-1)/10)+1

(By the way, what you are doing is not matrix transposition, but this does do what you do, only better.)

edited Feb 14 '13 at 12:03

answered Feb 14 '13 at 11:12

Řídící

3,210

That seems to do the trick! Thanks! It's not mathematically perfect, but neither was mine and like you said, it does do what I want! Thank you! Small edit: +1-1 is 0 so we can drop that ;-) – Feb 14 '13 at 12:04
@JvN Please note that beyond 10 rows or columns you will certainly run into trouble. The simple reason is that, e.g., the number 11 appears in two places and its "transpositions" are the numbers 2 and 101. So, for larger matrices the formula you seek can't exist. – Řídící Feb 15 '13 at 09:42

Mathematical transpose in excel

2 Answers2