lookup (match?) & average

[BD]

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

 

[Bernard Liengme]

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

 

[Shane Devenshire]

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.

 

[BD]

What if I'm using 2003?

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

 

[Shane Devenshire]

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

 

[BD]

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?

 

[BD]

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

 

[Bernard Liengme]

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

 

[BD]

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

 

[Glenn]

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]

Glenn - here's the formula as copied

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

BD

 

[T. Valko]

=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.

 

[BD]

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

Brian

 

[BD]

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]

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?