Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct puzzler
Hello Lords of Excel!
I know this can be solved in one cell but am going round in circles. I have 20 Volume Descriptions with varying volumes over 160 months. Further down on the shet, I have a user selectable range of these Volumes of up to 5 catagories. What I can do over two ranges is index match the volume to fit the selection by row no problem. I can then sum those 5 cells in the column and create another range which states each row as a % of the subset. example: A B C D E 1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160 2 Vol A 100 3 Vol B 105 4 Vol C 110 5 Vol D 115 6 Vol E 120 7 Vol F 125 .... etc 20 Vol T 60 User Selects: 25 Vol B Index Match returns 105 26 Vol E 120 27 Vol F 125 28 Vol T 60 Next Range 35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60)) 36 Vol E % of Selected Volumes = 29% 37 Vol F % = 30% 38 Vol T% = 14% I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row. Can this be done? Thank you so much for taking the time to read this far! best regards, Bony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct puzzler
=VLOOKUP(A35,$A$25:$B$28,2,FALSE)/SUM($B$25:$B$28)
-- __________________________________ HTH Bob "Bony Pony" wrote in message ... Hello Lords of Excel! I know this can be solved in one cell but am going round in circles. I have 20 Volume Descriptions with varying volumes over 160 months. Further down on the shet, I have a user selectable range of these Volumes of up to 5 catagories. What I can do over two ranges is index match the volume to fit the selection by row no problem. I can then sum those 5 cells in the column and create another range which states each row as a % of the subset. example: A B C D E 1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160 2 Vol A 100 3 Vol B 105 4 Vol C 110 5 Vol D 115 6 Vol E 120 7 Vol F 125 ... etc 20 Vol T 60 User Selects: 25 Vol B Index Match returns 105 26 Vol E 120 27 Vol F 125 28 Vol T 60 Next Range 35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60)) 36 Vol E % of Selected Volumes = 29% 37 Vol F % = 30% 38 Vol T% = 14% I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row. Can this be done? Thank you so much for taking the time to read this far! best regards, Bony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct puzzler
Many thanks Bob.
"Bob Phillips" wrote: =VLOOKUP(A35,$A$25:$B$28,2,FALSE)/SUM($B$25:$B$28) -- __________________________________ HTH Bob "Bony Pony" wrote in message ... Hello Lords of Excel! I know this can be solved in one cell but am going round in circles. I have 20 Volume Descriptions with varying volumes over 160 months. Further down on the shet, I have a user selectable range of these Volumes of up to 5 catagories. What I can do over two ranges is index match the volume to fit the selection by row no problem. I can then sum those 5 cells in the column and create another range which states each row as a % of the subset. example: A B C D E 1 Mth 1 Mth 2 Mth 3 Mth 4 etc to Mth 160 2 Vol A 100 3 Vol B 105 4 Vol C 110 5 Vol D 115 6 Vol E 120 7 Vol F 125 ... etc 20 Vol T 60 User Selects: 25 Vol B Index Match returns 105 26 Vol E 120 27 Vol F 125 28 Vol T 60 Next Range 35 Vol B % of Selected Volumes = 25% (105/sum(105,120,125,60)) 36 Vol E % of Selected Volumes = 29% 37 Vol F % = 30% 38 Vol T% = 14% I want the % to be calc'd in rows 25 - 28 as the Vols are selected by Row. Can this be done? Thank you so much for taking the time to read this far! best regards, Bony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct puzzler - apologies for incorrect post to Programming | Excel Discussion (Misc queries) | |||
Conditional formatting puzzler | Excel Worksheet Functions | |||
Concatenate Puzzler! | Excel Worksheet Functions | |||
Pivot Table Question - a puzzler | Excel Discussion (Misc queries) | |||
Another puzzler | Excel Worksheet Functions |