Formula to take average from column range matching another
 

I am trying to write a formula that returns a calculation, lets say an
average, of a range of numbers down a column that match up with a value in
another column. In the example below, I have multiple occurances of a
certain number study in a long list of studies that have values associated
with them in another column. If I run a pivot table to get one row per
study, can I write a formula (to the right, outside the pivot table) to go to
my data table and return the average for all Values in Study 1, 2 and so on?

Study Value
1 2
1 4
2 3
2 6

Formula to take average from column range matching another
 

How about using the sumproduct formula to get the sum divided by the count
(you could also use SumIf and CountIf...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"dmasch" wrote:

I am trying to write a formula that returns a calculation, lets say an
average, of a range of numbers down a column that match up with a value in
another column. In the example below, I have multiple occurances of a
certain number study in a long list of studies that have values associated
with them in another column. If I run a pivot table to get one row per
study, can I write a formula (to the right, outside the pivot table) to go to
my data table and return the average for all Values in Study 1, 2 and so on?

Study Value
1 2
1 4
2 3
2 6

Formula to take average from column range matching another
 

=AVERAGE(IF(A2:A20=1,B2:B20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"dmasch" wrote in message
...
I am trying to write a formula that returns a calculation, lets say an
average, of a range of numbers down a column that match up with a value in
another column. In the example below, I have multiple occurances of a
certain number study in a long list of studies that have values associated
with them in another column. If I run a pivot table to get one row per
study, can I write a formula (to the right, outside the pivot table) to go
to
my data table and return the average for all Values in Study 1, 2 and so
on?

Study Value
1 2
1 4
2 3
2 6

Formula to take average from column range matching another
 

Bob,

That worked perfectly. Thanks so much you don't even know how much time
this saves me.

Best regards,
Doug - "dmasch"

"Bob Phillips" wrote:

=AVERAGE(IF(A2:A20=1,B2:B20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"dmasch" wrote in message
...
I am trying to write a formula that returns a calculation, lets say an
average, of a range of numbers down a column that match up with a value in
another column. In the example below, I have multiple occurances of a
certain number study in a long list of studies that have values associated
with them in another column. If I run a pivot table to get one row per
study, can I write a formula (to the right, outside the pivot table) to go
to
my data table and return the average for all Values in Study 1, 2 and so
on?

Study Value
1 2
1 4
2 3
2 6