operating over a range in an array

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.
 
D

David Biddulph

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable statement 3
Variable substitution 1
Summing cells based on mulitiple criteria over mulitple columns 1
Sorting a string array containing combinations 4
Inernal function for finding y 1
Excel Iteration Program 5
Condensing data from 3D to 2D 7
Dynamic Range Using INDEX instead of Offset 4

Top