Average if(s)

[Bioinfo]

Hi All,
I wanted to calculate averages in column D between a range of values given in column B and C from the values in A.
e.g. in the following, I wanted to average of 6,7,9 (D2,D3,D4) based on B1 (i.e.10) and C1 (i.e. 30) which are given in A2 to A4.
A B C D

1	10	30	3
10	50	60	6
20	90	100	7
30			9
40			8
50			4
60			6
70			2
80			5
90			6
100			3

<tbody>
</tbody>

Thank you so much.

 

[BobUmlas]

In E1:
=AVERAGE(OFFSET(INDEX(D:D,MATCH(B1,A:A,0)),0,0,MATCH(C1,A:A,0)-MATCH(B1,A:A,0)+1,1))
fill down to E3.

 

[Bioinfo]

In E1:
=AVERAGE(OFFSET(INDEX(D:D,MATCH(B1,A:A,0)),0,0,MATCH(C1,A:A,0)-MATCH(B1,A:A,0)+1,1))
fill down to E3.

Thanks a lot Bob,
It works excellent. But I wanted the average values in E below the range, may be in a new row. Which means, is it possible to have the first average (A2 to A4) in E5, second average subsequently in E9 and so on. I have a large dataset.
Thanks again.