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

[Solved] Using a formula to change reference cell

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


Joined: 18 Oct 2010
Posts: 5

PostPosted: Mon Oct 18, 2010 12:09 pm    Post subject: [Solved] Using a formula to change reference cell Reply with quote

So, I'm close but not quite there.....

I have a sheet which has chunks of 8 numbers in column AL. I would like to average those numbers in another sheet.

The first block of 8 starts at AL7, and goes to AL15. The next block starts AL18, goes to AL25, etc.

There are also other rows I would like to apply this to as well, but if I can figure this one out, I'm golden

So, here's the closest I got

"=AVERAGE(INDIRECT("Sheet1.AL7:"&ADDRESS((10*ROW()+5),3Cool))"

Which is pasted in Sheet2,A1. That returns the first number correctly, but I can't figure out how to do the same trick that I did with Address for the reference cell. I tried variations like "Sheet1.AL&formula:", which it doesn't like. I'm sure there is a clean way of doing this, because mine is way to convoluted to be useful or to debug.

Thanks for the help, and I'll post the answer if I get it first! I know I can get it with INDEX, but not sure quite how to format


Last edited by Julolidine on Mon Oct 18, 2010 2:57 pm; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Oct 18, 2010 12:45 pm    Post subject: Reply with quote

OFFSET should do what you want. INDIRECT is wrong or too clumsy in most cases.
OFFSET(start_ref ; num_row_off ; num_column_off ; num_resize_rows ; num_resize_columns)

OFFSET(X99 ; -9 ; 3 ; 4 ; 5) ==> returns a reference to AA90:E93
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Julolidine
General User
General User


Joined: 18 Oct 2010
Posts: 5

PostPosted: Mon Oct 18, 2010 1:01 pm    Post subject: Reply with quote

SO I'm getting close

This formula in A1 returns the right cell =ADDRESS((10*ROW()-3),38,,,"Sheet1"), and in A2, I paste =ADDRESS((10*ROW()+5),38,,,"Sheet1"), and similarly get the right cell.

However =AVERAGE(ADDRESS((10*ROW()-3),38,,,"Sheet1")&":"&ADDRESS((10*ROW()+5),38,,,"Sheet1")) returns #VALUE!, and if I just type them separately, aka =AVERAGE(A1:A2), I get Div0 warning.
Back to top
View user's profile Send private message
Julolidine
General User
General User


Joined: 18 Oct 2010
Posts: 5

PostPosted: Mon Oct 18, 2010 1:02 pm    Post subject: Reply with quote

Ok, got it....I know indirect is clumsy, but this is only one call to it

=AVERAGE(INDIRECT(ADDRESS((10*ROW()-3),38,,,"Sheet1")&":"&ADDRESS((10*ROW()+5),38,,,"Sheet1")))
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Oct 18, 2010 1:18 pm    Post subject: Reply with quote

Julolidine wrote:
SO I'm getting close

This formula in A1 returns the right cell =ADDRESS((10*ROW()-3),38,,,"Sheet1"), and in A2, I paste =ADDRESS((10*ROW()+5),38,,,"Sheet1"), and similarly get the right cell.

However =AVERAGE(ADDRESS((10*ROW()-3),38,,,"Sheet1")&":"&ADDRESS((10*ROW()+5),38,,,"Sheet1")) returns #VALUE!, and if I just type them separately, aka =AVERAGE(A1:A2), I get Div0 warning.
ADDRESS() returns a text string that looks like an address. You can't calculate the average of a text string. You need to use INDIRECT() to convert that string to a proper address (cell reference).
OK! You found that out for yourself while I was typing. Good job!

I suggest you take Villeroy's advice and use OFFSET() instead.
I believe this does what you intend:
=AVERAGE(OFFSET('Sheet 1'.<current cell>;9*ROW()-3;38-COLUMN();9;1))
If you indicate how your data/indirection connects (e.g. data layout and logic behind averaging), we may suggest an even less convoluted formula.
Back to top
View user's profile Send private message
Julolidine
General User
General User


Joined: 18 Oct 2010
Posts: 5

PostPosted: Mon Oct 18, 2010 1:42 pm    Post subject: Reply with quote

Thanks for the advice guys.

Its measurements from a bunch of data points over the course of many hours. There are 8 different replicates for each measurement, as the data is quite noisy, and biological. Each chunk represents one genotype, thus the average.

My boss wrote this program in Matlab just to spit out the individual measurements. So the first reference cell is in AL7, the next in AL18, then next in AL29, Then next in AL40.

This works pretty good, and I think is as clear as it gets

=AVERAGE(OFFSET(Sheet1.AL$7,11*(ROW()-1),0,8,1))

Thanks for the help guys!

My first formula is a bit off, it should be =AVERAGE(INDIRECT(ADDRESS((11*ROW()-4),38,,,"Sheet1")&":"&ADDRESS((11*ROW()+3),38,,,"Sheet1")))

OFFSET is much more elegant though, obviously
Back to top
View user's profile Send private message
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