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

With your numbers in A1:B6; and D1= 1.1 & D2 = 1.5
I used =AVERAGE(IF((A1:A6=D1)*(A1:A6<=D2),B1:B6))
Which gave 9.44 as expected
Note this is an array formula and must be committed with CTRL+SHIFT+ENTER
not a simple ENTER

Please expand on: What if I'd like my reference in column A to be near a
value but not necessarily equal to it?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BD" wrote in message
...
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

For some reason, I get a result of 9.35 and if I change the value of D1 to
1.0 or 1.2, it does not change the result. I hope that I entered the
suggested formula correctly?

"Bernard Liengme" wrote:

With your numbers in A1:B6; and D1= 1.1 & D2 = 1.5
I used =AVERAGE(IF((A1:A6=D1)*(A1:A6<=D2),B1:B6))
Which gave 9.44 as expected
Note this is an array formula and must be committed with CTRL+SHIFT+ENTER
not a simple ENTER

Please expand on: What if I'd like my reference in column A to be near a
value but not necessarily equal to it?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BD" wrote in message
...
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

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

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

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

Hi Shane

For some reason, I get a result of 9.35 and if I change the value of D1 to
1.0 or 1.2, it does not change the result. I hope that I entered the
suggested formula correctly?

Any further help?

thanks,

BD

"Shane Devenshire" wrote:

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

Maybe the actual values in the cells are not as advertized
Click on B1 and look in the Formula Bar ; do you see 8.9, or something like
8.88456 ?
In other words: are the values in column B formatted to show only one
decimal place?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BD" wrote in message
...
Hi Shane

For some reason, I get a result of 9.35 and if I change the value of D1 to
1.0 or 1.2, it does not change the result. I hope that I entered the
suggested formula correctly?

Any further help?

thanks,

BD

"Shane Devenshire" wrote:

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

Copy the formula you entered and paste it into a reply in the newsgroup. Also,
confirm that all the values are exactly what you have shown and not hiding
additional decimal places due to formatting. In other words, column A could
actually be:

0.96
1.12
1.21
1.29
1.43
1.46


BD wrote:
Hi Shane

For some reason, I get a result of 9.35 and if I change the value of D1 to
1.0 or 1.2, it does not change the result. I hope that I entered the
suggested formula correctly?

Any further help?

thanks,

BD

"Shane Devenshire" wrote:

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