Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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

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

2 Answers 2

up vote 0 down vote accepted
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.)

share|improve this answer
    
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?

share|improve this answer
    
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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.