Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Percentages Using Subtotals???
I am trying to get dynamic percentages with my subtotals. My problem is
hard to explain in words, so I'll try to visually lay it out. I can easily lay out the logic, but my syntax needs a lot of help. As a novice to VB and macros- I'm trying my best. I'm running Excel XP on Windows XP. I export the following data from Access XP into an excel worksheet where I have one macro that I use to easily format everything. I have the following data (a sample) Bob 2 Bob 3 Bob 5 Jill 1 Jill 1 Jill 8 I then use the subtotal thing to get Bob 2 Bob 3 Bob 5 Bob total 10 Jill 1 Jill 1 Jill 8 Jill total 10 I am trying to get another column going- percentage from total. A final copy would look like the following: A B C 1 Bob 2 20% 2 Bob 3 30% 3 Bob 5 50% 4 Bob total 10 100% 5 Jill 1 10% 6 Jill 1 10% 7 Jill 8 80% 8 Jill total 10 100% The amount of data under any particular name is always changing, so I need some kind of dynamic way to do this. I can manually write out the code but as this information changes on a daily basis it is very time consuming. Ex code: C1 -- =C1/$C$4 C2 -- =C2/$C$4 C3 -- =C3/$C$4 C4 -- =C4/$C$4 I usually type the code out in C1 and drag it down to the subtotal line. I then click the percentage button to format the data. This works well but unfortunately my sheet has hundreds of people in it. I had planned on writing a macro to do all of this for me but I've been unable to find anything remotely related to what I want to do (spent the last 24 hrs straight checking everything I have- including books, internet, people). Can someone please help me? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Percentages Using Subtotals???
This formula, put in C2 (under column headers) and copied down, should do
what you are asking: =$B2/SUMIF($A:$A,$A2,$B:$B) " wrote: I am trying to get dynamic percentages with my subtotals. My problem is hard to explain in words, so I'll try to visually lay it out. I can easily lay out the logic, but my syntax needs a lot of help. As a novice to VB and macros- I'm trying my best. I'm running Excel XP on Windows XP. I export the following data from Access XP into an excel worksheet where I have one macro that I use to easily format everything. I have the following data (a sample) Bob 2 Bob 3 Bob 5 Jill 1 Jill 1 Jill 8 I then use the subtotal thing to get Bob 2 Bob 3 Bob 5 Bob total 10 Jill 1 Jill 1 Jill 8 Jill total 10 I am trying to get another column going- percentage from total. A final copy would look like the following: A B C 1 Bob 2 20% 2 Bob 3 30% 3 Bob 5 50% 4 Bob total 10 100% 5 Jill 1 10% 6 Jill 1 10% 7 Jill 8 80% 8 Jill total 10 100% The amount of data under any particular name is always changing, so I need some kind of dynamic way to do this. I can manually write out the code but as this information changes on a daily basis it is very time consuming. Ex code: C1 -- =C1/$C$4 C2 -- =C2/$C$4 C3 -- =C3/$C$4 C4 -- =C4/$C$4 I usually type the code out in C1 and drag it down to the subtotal line. I then click the percentage button to format the data. This works well but unfortunately my sheet has hundreds of people in it. I had planned on writing a macro to do all of this for me but I've been unable to find anything remotely related to what I want to do (spent the last 24 hrs straight checking everything I have- including books, internet, people). Can someone please help me? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Percentages Using Subtotals???
Your formula worked perfectly! Thanks a lot!
Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentages of Subtotals for Pivot Tables | Excel Discussion (Misc queries) | |||
percentages of subtotals | Excel Worksheet Functions | |||
vba code for subtotals and percentages | Excel Programming | |||
vba code for subtotals and percentages | Excel Programming | |||
vba code for subtotals and percentages | Excel Programming |