Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|