lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

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

lookup (match?) & average
 

Unfortunatley no, they are dummy data so I entered in 8.9, 9.8, etc...

"Bernard Liengme" wrote:

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

lookup (match?) & average
 

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

lookup (match?) & average
 

Glenn - here's the formula as copied

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

BD

"Glenn" wrote:

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

lookup (match?) & average
 

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

If you're using Excel 2003 you can't use entire columns as range references.
Use a smaller specific range.

=AVERAGE(IF((A1:A100=D1)*(A1:A100<=D2),B1:B100))

Also, the formula is an array formula. It will not calculate correctly if
you don't enter it as an array.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

--
Biff
Microsoft Excel MVP


"BD" wrote in message
...
Glenn - here's the formula as copied

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

BD

"Glenn" wrote:

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

lookup (match?) & average
 

Bingo! I goofed in both cases. Thanks so much to all for your help!

Brian

"T. Valko" wrote:

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

If you're using Excel 2003 you can't use entire columns as range references.
Use a smaller specific range.

=AVERAGE(IF((A1:A100=D1)*(A1:A100<=D2),B1:B100))

Also, the formula is an array formula. It will not calculate correctly if
you don't enter it as an array.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

--
Biff
Microsoft Excel MVP


"BD" wrote in message
...
Glenn - here's the formula as copied

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

BD

"Glenn" wrote:

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

lookup (match?) & average
 

Biff - Perhaps my example should have been more specific. The formula
worked for my dummy data but not the real data. Below is some "actual" data.
The values shown in column A are truncated. When I used this data I got a
#DIV/0! error. Any suggestions?

thanks,

Brian

A B C D
12.74279322 5.473 =A4
12.74241443 6.158 =A18
12.74203564 6.089
{=AVERAGE(IF((A1:A19=D1)*(A1:A19<=D2),B1:B19))}
12.74165705 6.417
12.74127826 6.351
12.74089947 6.432
12.74052068 6.451
12.74014189 6.46
12.73976311 6.485
12.73938432 6.539
12.73900553 6.075
12.73862693 5.941
12.73824814 6.361
12.73786936 5.973
12.73749057 6.105
12.73711178 6.215
12.73673299 6.277
12.7363542 6.233
12.73597561 6.57



"T. Valko" wrote:

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

If you're using Excel 2003 you can't use entire columns as range references.
Use a smaller specific range.

=AVERAGE(IF((A1:A100=D1)*(A1:A100<=D2),B1:B100))

Also, the formula is an array formula. It will not calculate correctly if
you don't enter it as an array.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

--
Biff
Microsoft Excel MVP


"BD" wrote in message
...
Glenn - here's the formula as copied

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

BD

"Glenn" wrote:

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

lookup (match?) & average
 

When I used this data I got a #DIV/0! error

Based on the posted sample data that is the correct result because no
entries meet both conditions:

=A4 and <=A18

What result do you expect?

--
Biff
Microsoft Excel MVP


"BD" wrote in message
...
Biff - Perhaps my example should have been more specific. The formula
worked for my dummy data but not the real data. Below is some "actual"
data.
The values shown in column A are truncated. When I used this data I got a
#DIV/0! error. Any suggestions?

thanks,

Brian

A B C D
12.74279322 5.473 =A4
12.74241443 6.158 =A18
12.74203564 6.089
{=AVERAGE(IF((A1:A19=D1)*(A1:A19<=D2),B1:B19))}
12.74165705 6.417
12.74127826 6.351
12.74089947 6.432
12.74052068 6.451
12.74014189 6.46
12.73976311 6.485
12.73938432 6.539
12.73900553 6.075
12.73862693 5.941
12.73824814 6.361
12.73786936 5.973
12.73749057 6.105
12.73711178 6.215
12.73673299 6.277
12.7363542 6.233
12.73597561 6.57



"T. Valko" wrote:

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

If you're using Excel 2003 you can't use entire columns as range
references.
Use a smaller specific range.

=AVERAGE(IF((A1:A100=D1)*(A1:A100<=D2),B1:B100))

Also, the formula is an array formula. It will not calculate correctly if
you don't enter it as an array.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets
{ }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

--
Biff
Microsoft Excel MVP


"BD" wrote in message
...
Glenn - here's the formula as copied

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

BD

"Glenn" wrote:

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