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

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

=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

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