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.
On Sun, 11 Jan 2009 16:29:31 -0500, Erik Remmelzwaal
<[email protected]> wrote:
-------- Originele bericht --------
Van: Rogier van Vlissingen <[email protected]>
Aan: [email protected] <[email protected]>
Datum: sat, 10 jan 2009 23:28:41 -0500
This is what I'm trying to do:
=AVERAGE( HLookup(X14, ConsBillArray, 11) : HLookup(x15, ConsBillArray,
11) )
and in this form it returns an error.
What happens is, the HLookups produce a number, not a cell reference,
and
therefore AVERAGE is now looking for a list of numers, separated by
commas, and I can't use the array specification.
Rogier,
I assume that your lookups return both corner coordinates of the array
as a string like "B2" and "C5"
You can than write a formula like:
=AVERAGE(INDIRECT("B2:C5"))
But you have two arguments i.s.o. one. The following step is:
=AVERAGE(INDIRECT(CONCATENATE("B2"; ":"; "C5")))
The last step is to replace the "B2" and the "B3" with your HLookup() I
did not test that, but I'm convinced this works.
Success,
Erik
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
--
Rogier van Vlissingen
www.acimnthomas.com
www.xanga.com/RogierFvV
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
