# Mathematical transpose in excel

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?

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

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

-
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 ;-) –  JvN 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. –  Glen The Udderboat Feb 15 '13 at 9:42

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?

-
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. –  tesc 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. –  JvN 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
This answer has been superseded by the one addressing the OP's cleverly disguised actual needs. –  Glen The Udderboat Feb 14 '13 at 12:36