Row reference and applying to another column.

[Pedro's PhD]

Hi everyone, <o></o>
<o> </o>
I'm looking for a function that will find a value somewhere in a column and then return the row reference. From two row references obtained, i then need to use them as my data range to calculate average values for a different column.<o></o>
<o> </o>
For example: <o></o>
Cell ‘A10’ the value is ‘1’ <o></o>
Cell ‘A11’ the value is ‘2’, <o></o>
Cell ‘A12’ the value is ‘3’ <o></o>
Cell ‘A13’ the value is ‘4’ .... and so forth for 1000 rows. <o></o>
<o> </o>
I then enter in cell ‘B1’ the value ‘2’ and in cell ‘B2’ the value ’4’. I now need a formula to calculate the average of values in column C between “C11:C13”. This range was determined as the input in 'B1' corresponds to row reference in ColumnA. So ‘B1’ had the value of ‘2’, and the value ‘2’ in column A was in Row11. Likewise, cell 'B2' had the value '4', and value '4' appears in Row13 in ColumnA.<o></o>
<o> </o>
Hope that makes sense,<o></o>
<o> </o>
Many thanks in advance.... Pedro.

 

[mathchick]

=average(offset(c10,match(b1,a10:a24,0)-1,0,match(b2,a10:a24,0)-match(b1,a10:a24,0)+1,1))

 

[Pedro's PhD]

Brilliant Mathchick! Thanks very much!

Pedro.

Mr Excel.... The best forum around!