[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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:
 Code: 1  2  3 4  5  6 7  8  9

and want to generate from it
 Code: 9  8  7 6  5  4 3  2  1

The best I can find is the transpose option in "paste special", which gives me
 Code: 1  4  7 2  5  8 3  6  9

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
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(4-ROW(A1);4-COLUMN(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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 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 pre-multiply 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 post-multiply 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 CTRL-SHIFT-ENTER. David.
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 CTRL-SHIFT-ENTER
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 post-multiplication 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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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 [CTRL-SHIFT-ENTER] 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.
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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
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!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum