Excel
 

I have a column of scheme codes, with the same codes repeated through the
list eg. 123, 456, 123, 678, 456, 123 and a column of rateable values. I
need to work out the average rateable value for same group of codes e.g. for
all the 123 codes. Can you help please if this is possible.

Thank you

Excel
 

Use a Pivot table for the same. In the row area place the scheme code. In the
data area place the rateable values. Double click to change sum to average.
Finish the wizard and you will have what you want
Anu

"JillyB" wrote:

I have a column of scheme codes, with the same codes repeated through the
list eg. 123, 456, 123, 678, 456, 123 and a column of rateable values. I
need to work out the average rateable value for same group of codes e.g. for
all the 123 codes. Can you help please if this is possible.

Thank you

Excel
 

"JillyB" wrote in message
...
I have a column of scheme codes, with the same codes repeated through the
list eg. 123, 456, 123, 678, 456, 123 and a column of rateable values. I
need to work out the average rateable value for same group of codes e.g.
for
all the 123 codes. Can you help please if this is possible.

Thank you

Suppose codes are in A1:A999 and rateable values in B1:B999.
Average = Total / Number, so
=SUMPRODUCT(--(A1:A999=123),B1:B999)/SUMPRODUCT(--(A1:A999=123))
will give you the average for code 123.

You could make a list of codes in, say, E1:E10.
Then, in F1 you could put the formula
=SUMPRODUCT(--(A1:A999=E1),B1:B999)/SUMPRODUCT(--(A1:A999=E1))
and copy it down as far as F10 to get averages for each code.

Excel
 

Thank you very much - I will ty it

"Stephen" wrote:

"JillyB" wrote in message
...
I have a column of scheme codes, with the same codes repeated through the
list eg. 123, 456, 123, 678, 456, 123 and a column of rateable values. I
need to work out the average rateable value for same group of codes e.g.
for
all the 123 codes. Can you help please if this is possible.

Thank you

Suppose codes are in A1:A999 and rateable values in B1:B999.
Average = Total / Number, so
=SUMPRODUCT(--(A1:A999=123),B1:B999)/SUMPRODUCT(--(A1:A999=123))
will give you the average for code 123.

You could make a list of codes in, say, E1:E10.
Then, in F1 you could put the formula
=SUMPRODUCT(--(A1:A999=E1),B1:B999)/SUMPRODUCT(--(A1:A999=E1))
and copy it down as far as F10 to get averages for each code.

Excel
 

Thank you so much - will try it shortly

"Anu" wrote:

Use a Pivot table for the same. In the row area place the scheme code. In the
data area place the rateable values. Double click to change sum to average.
Finish the wizard and you will have what you want
Anu

"JillyB" wrote:

I have a column of scheme codes, with the same codes repeated through the
list eg. 123, 456, 123, 678, 456, 123 and a column of rateable values. I
need to work out the average rateable value for same group of codes e.g. for
all the 123 codes. Can you help please if this is possible.

Thank you