B
Bony Pony
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
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