Excel

  • Thread starter Thread starter JillyB
  • Start date Start date
J

JillyB

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
 
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 said:
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
Click to expand...

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.
 
Thank you very much - I will ty it

Stephen said:
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.
Click to expand...
 
Thank you so much - will try it shortly

Anu said:
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
Click to expand...
 
Back
Top