operating over a range in an array

  • Thread starter Thread starter ghengis_na'an
  • Start date Start date
G

ghengis_na'an

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.
 
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.
 
Back
Top