Hi,

For 2003 Bernard gave you the correct solution:

AVERAGE(IF((A:A=D1)*(A:A<=D2),B:B))

This is an array formula so it must be entered by pressing Shift+Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"BD" wrote:

What if I'm using 2003?

"Shane Devenshire" wrote:

Hi,

If you are using 2007:

=AVERAGEIFS(B:B,A:A,"="&D1,A:A,"<="&D2,B:B)

where D1 contains the lower value and D2 the upper value.

Regarding "near" you can use < or or you will need to tell us in more
detail how you define near.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"BD" wrote:

I have two columns of data as shown below. I'd like to write a formula where
I can take the average of the values from column B that are between two
corresponding values in column A. For example, the average from 1.1 to 1.5 =
9.44. Typical data contains about 20k rows and the values in column A are
not always in the same row when doing this in multiple worksheets. What if
I'd like my reference in column A to be near a value but not necessarily
equal to it?

thanks,

BD

A B
1.0 8.9
1.1 9.8
1.2 9.5
1.3 9.2
1.4 9.1
1.5 9.6