Help with averages please
 

Hi Guys,

This is probably really easy but I have been struggling to average the values in 2 linked collumns.

eg

A B
2 3
2 4
2 3
3 2
3 2
4 1
5 2
5 3

What I want to happen is it to calculate the average of the values in the second column that match the same variable in the first column, and paste them into a seperate row, so the formula would calculate
A B
2 (Average(3+4+3))
3 (Average(2+2))
4 1
5 (Average(2+3))

to produce these columns

A B
2 3.33
3 2
4 1
5 2.5

I have been trying to write a macro but with no luck.

Please help.

Many thanks in advance for looking.

Amer Karim

Hi!

List the unique numbers from column A in another column. If there are a lot
of them you can use an advanced filter to do this for you.

Assume that unique list is in the range E1:E4.

In F1 enter this formula and copy down:

=SUMIF(A$1:A$8,E1,B$1:B$8)/COUNTIF(A$1:A$8,E1)

An alternative:

Array entered in F1 and copied down:

=AVERAGE(IF(A$1:A$8=E1,B$1:B$8))

Biff

"amerkarim" wrote in message
...

Hi Guys,

This is probably really easy but I have been struggling to average the
values in 2 linked collumns.

eg

A B
2 3
2 4
2 3
3 2
3 2
4 1
5 2
5 3

What I want to happen is it to calculate the average of the values in
the second column that match the same variable in the first column, and
paste them into a seperate row, so the formula would calculate
A B
2 (Average(3+4+3))
3 (Average(2+2))
4 1
5 (Average(2+3))

to produce these columns

A B
2 3.33
3 2
4 1
5 2.5

I have been trying to write a macro but with no luck.

Please help.

Many thanks in advance for looking.

Amer Karim


--
amerkarim

One way is to use a pivot table (only a couple of clicks will get us there
!)

Assuming the posted data is in A1:B9, with headers "A", "B" in A1:B1

Select any cell within A1:B9

Click Data Pivot Table Report
Click Next Next

In Step 3 of the wizard:
Drag and drop "A" within the ROW area

Drag and drop "B" within the DATA area
(It'll appear as "Sum of B")

Double-click on "Sum of B" and in the dialog:
Select "Average" under "Summarize by"
Click "Number.." Number 2 d.p. (say) OK

Click OK at the main dialog
("Sum of B" will have changed to "Average of B")

Click Finish

The pivot table will be created in a new sheet to the left,
and yield the desired results:

Average of B
A Total
2 3.33
3 2.00
4 1.00
5 2.50
Grand Total 2.50
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"amerkarim" wrote in message
...

Hi Guys,

This is probably really easy but I have been struggling to average the
values in 2 linked collumns.

eg

A B
2 3
2 4
2 3
3 2
3 2
4 1
5 2
5 3

What I want to happen is it to calculate the average of the values in
the second column that match the same variable in the first column, and
paste them into a seperate row, so the formula would calculate
A B
2 (Average(3+4+3))
3 (Average(2+2))
4 1
5 (Average(2+3))

to produce these columns

A B
2 3.33
3 2
4 1
5 2.5

I have been trying to write a macro but with no luck.

Please help.

Many thanks in advance for looking.

Amer Karim


--
amerkarim

On Wed, 28 Sep 2005 01:17:00 +0100, amerkarim
wrote:


Hi Guys,

This is probably really easy but I have been struggling to average the
values in 2 linked collumns.

eg

A B
2 3
2 4
2 3
3 2
3 2
4 1
5 2
5 3

What I want to happen is it to calculate the average of the values in
the second column that match the same variable in the first column, and
paste them into a seperate row, so the formula would calculate
A B
2 (Average(3+4+3))
3 (Average(2+2))
4 1
5 (Average(2+3))

to produce these columns

A B
2 3.33
3 2
4 1
5 2.5

I have been trying to write a macro but with no luck.

Please help.

Many thanks in advance for looking.

Amer Karim

Ensure the two columns have labels at the top as in your example (A & B)
Select some cell in your table.
Data/Subtotals
At each change in "A"
Use function "Average"
Add subtotal to "B"

If need be, you can easily shift the cells over


--ron

Thanks Max,

It worked a treat.

How you guys get so good at this stuff just kills me.

Many, many thanks again with my kind regards,

Amer Karim.

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--