Re: [users] Calc Challenge, Average using an array lookup not working...

[Rogier van Vlissingen]

Interesting... what you're suggested will work, though it becomes clumsy... as I have to do that for lines 11,12, 18,19, and 22,23, and therefore each time have to compute the references as an interim step.

Thanks

So, I appreciate the fact that you got me working, but the solution fails the elegance test by a mile, and I'll keep exercising my brain on that one for a while before I give up... in short there has to be a better way. Beginning with the fact I had an easier way to generate those addresses the other day, but now I can't remember/reproduce it. I attached the spreadsheet, and you'll see the issue in cells Y,Z14-15 and Y17 On Mon, 12 Jan 2009 13:49:57 -0500, Erik Remmelzwaal <jhf.remmelzw...@gmail.com> wrote:

2009/1/12 Rogier van Vlissingen <vlisc...@gmail.com>

Well, you got me experimenting, but this isn't it yet, but I did some
editing to clarify the problem:

ConsBillArray is an 8x25 array, with the first row being 1-8 (periods)
I have renamed X14 "FromPeriod" and X15 "ToPeriod" These two variables

error prone, so I want to specify just the starting and ending periods
(columns), and have the average computed based on that.


is that:
Hlookup(FromPeriod,ConsBillArray,11) produces a value, so in that case
=AVERAGE() looks for a list of values, separated by commas,

specify the starting and ending period from which an average is computed in certain rows. Or at least that's the idea. I can do it manually, but that's to write =AVERAGE(A2:A7) requires two addresses, not values, so the problem

I can't seem to find an elegant way of specifying this.

Hi Rogier,

I think that you can not use the HLookup to get an array/range result. So

is to construct the hlookup ourselves and let it return a range.

The idea is to calculate the left and right boundaries( assign them to
fields B22 and C22 resp.):
=ADDRESS(ROW(ConsBillArray)+(11-1);COLUMN(ConsBillArray)+FromPeriod-1)
=ADDRESS(ROW(ConsBillArray)+(11-1);COLUMN(ConsBillArray)+ToPeriod-1)
Now get the average by using these left/right bound values:
AVERAGE(INDIRECT(CONCATENATE(B22;":";C22);1))

Of cause you can replace the B22 and C22 cell references by their resp.
formula, which is not done to make things more clear.


easily apply the formula in a column.


an end users' :-).

Erik



\

you have to construct it manually. I also expect that the number 11 in your example is the 11th row = 10th 'year' or alike. The only solution I see now When you need to have this in place for all rows of ConsBillArray you might introduce a 'year' column and replace the '11' with this column. Now you can I think this is very elegant from a programmers perspective, but less from

--
Rogier van Vlissingen
www.acimnthomas.com
www.xanga.com/RogierFvV

EverGreen_576E165_ECR+evalV0.50.ods
Description: application/vnd.oasis.opendocument.spreadsheet

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org