OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How can I turn around a table (or a row)?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
xarmin
Newbie
Newbie


Joined: 18 Apr 2006
Posts: 4
Location: Japan

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

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
Back to top
View user's profile Send private message Visit poster's website
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Thu Apr 20, 2006 5:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

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

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.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

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

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

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

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

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

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.
Back to top
View user's profile Send private message
davel
Power User
Power User


Joined: 18 Oct 2005
Posts: 62
Location: PA, USA

PostPosted: Fri Apr 21, 2006 3:57 pm    Post subject: Reply with quote

NIce going David Idea 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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Apr 21, 2006 5:52 pm    Post subject: Reply with quote

davel wrote:
NIce going David Idea 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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

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

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
View user's profile Send private message
xarmin
Newbie
Newbie


Joined: 18 Apr 2006
Posts: 4
Location: Japan

PostPosted: Sun Apr 23, 2006 6:23 pm    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group