View previous topic :: View next topic 
Author 
Message 
xarmin Newbie
Joined: 18 Apr 2006 Posts: 4 Location: Japan

Posted: Thu Apr 20, 2006 3:43 pm Post subject: How can I turn around a table (or a row)? 


I need to turn around a table. Let's make a simple one:
and want to generate from it
The best I can find is the transpose option in "paste special", which gives me
which isn't what I need.
Probably it's the same, but I also need to know how to convert a row into a colum (copying cell A1 to say C1, A2 to D1, A3 to E1 etc.) and how to turn around a row or column (eg. copy A1 to B9, A2 to B8, A3 to B7, .. A9 to B1)
Why I want to do this? I stuck a microtiter plate wrong way into the reader, so the results come out upside down and do not fit my template for data analysis.
Thanks a lot in advance!  Armin 

Back to top 


Dale Super User
Joined: 21 Feb 2005 Posts: 1440 Location: Australia

Posted: Thu Apr 20, 2006 5:48 pm Post subject: 


Assume your example is in the range A1:C3
In some other cell type =INDIRECT(ADDRESS(4ROW(A1);4COLUMN(A1))) then fill across and down to complete the new grid.
Read help on the functions to see how they work, you'll then be able to adapt this to your real spreadsheet. _________________ Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!
OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Thu Apr 20, 2006 7:08 pm Post subject: Re: How can I turn around a table (or a row)? 


I'm not familiar with using the spreadsheet for matrix multiplication, but if you are, this should do it:
First premultiply the original by ...
0 0 1
0 1 0
1 0 0
This will result in ...
7 8 9
4 5 6
1 2 3
Then postmultiply the result by that same matrix for the final result.
EDIT: A look brought up MMULT() function, but it still requires some repetitive typing. That is, I couldn't see how to express it simply as the product of two arrays., either singularly or as an array formula using CTRLSHIFTENTER.
David. 

Back to top 


Dale Super User
Joined: 21 Feb 2005 Posts: 1440 Location: Australia

Posted: Thu Apr 20, 2006 9:15 pm Post subject: Re: How can I turn around a table (or a row)? 


David wrote:  A look brought up MMULT() function, but it still requires some repetitive typing. That is, I couldn't see how to express it simply as the product of two arrays., either singularly or as an array formula using CTRLSHIFTENTER  Did you try:
Type
=MMULT(A7:C9;A1:C3)
finish with Ctrl+Shift+Enter
makes the formula look like {=MMULT(A7:C9;A1:C3)} and completes the array  so that's it for the 9 cells.
Of course you have to do that again for the postmultiplication phase:
{=MMULT(<result array>;A7:C9)}
If Armin is clever with his relative and absolute references, this will copy and paste nicely and might be less work than my suggestion. _________________ Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!
OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Fri Apr 21, 2006 4:50 am Post subject: Re: How can I turn around a table (or a row)? 


Dale wrote:  Did you try:
Type
=MMULT(A7:C9;A1:C3)
finish with Ctrl+Shift+Enter

Thanks, no I didn't, since I don't have enough practice using that in the spreadsheet, and always seem to be in a rush to do something else these days. I'm going to find time some day to do a thorough research of all of the spreadsheet functions ... I should live so long.
Anyhow, that would be the appropriate approach I'd think.
David. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Fri Apr 21, 2006 5:15 am Post subject: Re: How can I turn around a table (or a row)? 


Dale wrote:  Did you try:
Type
=MMULT(A7:C9;A1:C3)
finish with Ctrl+Shift+Enter
. 
Might as well finish it? ... No need to worry about absolute references, I think:
In A1:C3, I put the matrix
0 0 1
0 1 0
1 0 0
In A7:C9 there is the original matrix
1 2 3
4 5 6
7 8 9
In another cell, F3, I put the array formula as you suggested
{=MMULT(A7:C9;A1:C3)}
In another, F7, I put
{=MMULT(A1:C3;F3:H5)}
Voila.
Xarmin: If you have trouble, just copy/paste from here, but remove, or don't include the array brackets {}, and enter that with the array control keys [CTRLSHIFTENTER] the first two held while pressing ENTER to form the array. Once entered, you can move the matrices to cells of your choice, and the formulas will change in the spreadsheet accordingly.
David. 

Back to top 


davel Power User
Joined: 18 Oct 2005 Posts: 62 Location: PA, USA

Posted: Fri Apr 21, 2006 3:57 pm Post subject: 


NIce going David I haven't dealt with matrices for about 30 years. Your solution prompted me to dust off some old text books to try to understand how it works.
Dave L 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Fri Apr 21, 2006 5:52 pm Post subject: 


davel wrote:  NIce going David I haven't dealt with matrices for about 30 years. Your solution prompted me to dust off some old text books to try to understand how it works.
Dave L 
You're welcome. I taught the stuff, but that was about the same time ago.
David. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Sat Apr 22, 2006 4:40 am Post subject: Re: How can I turn around a table (or a row)? 


xarmin wrote:  Probably it's the same, but I also need to know how to convert a row into a colum (copying cell A1 to say C1, A2 to D1, A3 to E1 etc.) and how to turn around a row or column (eg. copy A1 to B9, A2 to B8, A3 to B7, .. A9 to B1)

Didn't address this: Look for transpose() in the Help file. That is also to be entered as an array function.
David. 

Back to top 


xarmin Newbie
Joined: 18 Apr 2006 Posts: 4 Location: Japan

Posted: Sun Apr 23, 2006 6:23 pm Post subject: 


Thanks a lot, guys. Both work fine, but the "magic" solution requires that all fields are numeric (which in my case they are) and the number of rows is equal to the number of column (which is not true in my problem, as micro titer plates come with 8x12 wells; my example was not well chosen in this respect. Of course, I suppose I could fill in zeroes). So, I'll take Dale's initial address shuffling solution.
Appreciate it! 

Back to top 


