Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of duplicate values in a column
EG:
A B Result in C Answer = 1 xx1 5 =b1/(sum of all xx) 0.2777 2 xx2 6 =b2/(sum of all xx) 0.3333 3 ff1 10 =b3/(sum of all ff) 0.3333 4 xx2 3 =b4/(sum of all xx) 0.1666 5 ff3 8 =b5/(sum of all ff) 0.2666 6 ff2 12 =b6/(sum of all ff) 0.400 7 xx4 4 =b7/(sum of all xx) 0.2222 etc etc What I am trying to do is have a macor that starts with A1 & looks at all the codes down a1 to see if they match only the 'text part of the string" then gets the value in column B & sums it, & places the answer in c1 & so on. The reason for the b colum values to be divided by this is that they will be relative values against the total of that same code Hoepfully this makes sense. John Young |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of duplicate values in a column
John,
I would split the data up first, using DataText to columns which will put it in 3 separate columns, and then use this formula =C1/SUMPRODUCT(--(LEFT($B$1:$B$100,2)=LEFT(B1,2)),($C$1:$C$100)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "John Young" wrote in message ... EG: A B Result in C Answer = 1 xx1 5 =b1/(sum of all xx) 0.2777 2 xx2 6 =b2/(sum of all xx) 0.3333 3 ff1 10 =b3/(sum of all ff) 0.3333 4 xx2 3 =b4/(sum of all xx) 0.1666 5 ff3 8 =b5/(sum of all ff) 0.2666 6 ff2 12 =b6/(sum of all ff) 0.400 7 xx4 4 =b7/(sum of all xx) 0.2222 etc etc What I am trying to do is have a macor that starts with A1 & looks at all the codes down a1 to see if they match only the 'text part of the string" then gets the value in column B & sums it, & places the answer in c1 & so on. The reason for the b colum values to be divided by this is that they will be relative values against the total of that same code Hoepfully this makes sense. John Young |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum of duplicate values in a column
John Young,
If the 'text part of the string" is only the first two characters then this formula will work =B2/SUMPRODUCT((LEFT($A$2:$A$8,2)=LEFT(A2,2))*($B$2:$B $8)) HTH Cecil "John Young" wrote in message ... EG: A B Result in C Answer = 1 xx1 5 =b1/(sum of all xx) 0.2777 2 xx2 6 =b2/(sum of all xx) 0.3333 3 ff1 10 =b3/(sum of all ff) 0.3333 4 xx2 3 =b4/(sum of all xx) 0.1666 5 ff3 8 =b5/(sum of all ff) 0.2666 6 ff2 12 =b6/(sum of all ff) 0.400 7 xx4 4 =b7/(sum of all xx) 0.2222 etc etc What I am trying to do is have a macor that starts with A1 & looks at all the codes down a1 to see if they match only the 'text part of the string" then gets the value in column B & sums it, & places the answer in c1 & so on. The reason for the b colum values to be divided by this is that they will be relative values against the total of that same code Hoepfully this makes sense. John Young |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show only rows with duplicate values in a column? | Excel Discussion (Misc queries) | |||
duplicate numbers in column a and diferent values in b | Excel Worksheet Functions | |||
Highlighting duplicate values in a column | Excel Discussion (Misc queries) | |||
can i not allow duplicate values in the same column? | New Users to Excel | |||
Removing duplicate values in a column | Excel Worksheet Functions |