operating over a range in an array
 

I have a two column array, let's call the values in these columns X and Y. I
want to perform a function (e.g. average, sum, etc.) on the Y values from a
given approximate starting value of X (i.e. X1) to a given approximate ending
value, X2. I have Excel 2003. I get close to a solution using INDEX and
MATCH, but I can't figure out how to specify the range of cells I want within
the math function. Additionally, the number of Y values between X1 and X2 is
unknown and must be arbitrary.

operating over a range in an array
 

SUMPRODUCT is often an appropriate solution.

=SUMPRODUCT(--(A2:A100=X1),--(A2:A100<=X2),B2:B100) would give you the sum.
=SUMPRODUCT(--(A2:A100=X1),--(A2:A100<=X2),B2:B100)/SUMPRODUCT(--(A2:A100=X1),--(A2:A100<=X2))
would give you the average.
--
David Biddulph

"ghengis_na'an" wrote in message
...
I have a two column array, let's call the values in these columns X and Y.
I
want to perform a function (e.g. average, sum, etc.) on the Y values from
a
given approximate starting value of X (i.e. X1) to a given approximate
ending
value, X2. I have Excel 2003. I get close to a solution using INDEX and
MATCH, but I can't figure out how to specify the range of cells I want
within
the math function. Additionally, the number of Y values between X1 and X2
is
unknown and must be arbitrary.