[Solved] Using a formula to change reference cell

Julolidine · General User Joined: 18 Oct 2010 Posts: 5

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),3 Cool

))"

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

Villeroy · Posted: Mon Oct 18, 2010 12:45 pm Post subject:

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 http://forum.openoffice.org

Julolidine · General User Joined: 18 Oct 2010 Posts: 5

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.

Julolidine · General User Joined: 18 Oct 2010 Posts: 5

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

keme · Posted: Mon Oct 18, 2010 1:18 pm Post subject:

Julolidine · General User Joined: 18 Oct 2010 Posts: 5

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