2009/1/12 Rogier van Vlissingen <[email protected]> > 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 > 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 > error prone, so I want to specify just the starting and ending periods > (columns), and have the average computed based on that. > > to write =AVERAGE(A2:A7) requires two addresses, not values, so the problem > is that: > Hlookup(FromPeriod,ConsBillArray,11) produces a value, so in that case > =AVERAGE() looks for a list of values, separated by commas,
> > > 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 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 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. 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 easily apply the formula in a column. I think this is very elegant from a programmers perspective, but less from an end users' :-). Erik \
