I have the following table
[TABLE="width: 421"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="6"> <tbody>[TR]
[TD="width: 102"]Average of APE[/TD]
[TD="width: 90"]Date[/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[/TR]
[TR]
[TD]nlist[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1208[/TD]
[TD="bgcolor: transparent, align: right"]119[/TD]
[TD="bgcolor: transparent, align: right"]149[/TD]
[TD="bgcolor: transparent, align: right"]121[/TD]
[TD="bgcolor: transparent, align: right"]108[/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent, align: right"]162[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1209[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]136[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: transparent, align: right"]117[/TD]
[TD="bgcolor: transparent, align: right"]128[/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent, align: right"]225[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1210[/TD]
[TD="bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]124[/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent, align: right"]129[/TD]
[TD="bgcolor: transparent, align: right"]129[/TD]
[TD="bgcolor: transparent, align: right"]179[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]189[/TD]
[/TR]
</tbody>[/TABLE]
these are volume of accounts that are in each of the lists (1208-1210) over the 7 months indicated (1/1/18 - 7/1/18).
so in January individual lists had 119, 127, and 114 list volume respectively. If you average these together you get the 120 that is in the grand total line.
I want to be able to show how far each the values from the grand total (using percentage) for each month.
[TABLE="width: 151"]
<colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="2"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody>[TR]
[TD="width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]nlist[/TD]
[TD="width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]1/1/2018[/TD]
[TD="width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]variance off average volume[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1208[/TD]
[TD="bgcolor: transparent, align: right"]119[/TD]
[TD="bgcolor: transparent, align: right"]0.01%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1209[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]0.06%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1210[/TD]
[TD="bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent, align: right"]0.05%[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Grand Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]120[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0.04%[/TD]
[/TR]
</tbody>[/TABLE]
any thoughts? I know calculations inside the pivot table can do lot.
[TABLE="width: 421"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" span="6"> <tbody>[TR]
[TD="width: 102"]Average of APE[/TD]
[TD="width: 90"]Date[/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[/TR]
[TR]
[TD]nlist[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]7/1/2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1208[/TD]
[TD="bgcolor: transparent, align: right"]119[/TD]
[TD="bgcolor: transparent, align: right"]149[/TD]
[TD="bgcolor: transparent, align: right"]121[/TD]
[TD="bgcolor: transparent, align: right"]108[/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent, align: right"]162[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1209[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]136[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: transparent, align: right"]117[/TD]
[TD="bgcolor: transparent, align: right"]128[/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[TD="bgcolor: transparent, align: right"]225[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1210[/TD]
[TD="bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]124[/TD]
[TD="bgcolor: transparent, align: right"]106[/TD]
[TD="bgcolor: transparent, align: right"]129[/TD]
[TD="bgcolor: transparent, align: right"]129[/TD]
[TD="bgcolor: transparent, align: right"]179[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]189[/TD]
[/TR]
</tbody>[/TABLE]
these are volume of accounts that are in each of the lists (1208-1210) over the 7 months indicated (1/1/18 - 7/1/18).
so in January individual lists had 119, 127, and 114 list volume respectively. If you average these together you get the 120 that is in the grand total line.
I want to be able to show how far each the values from the grand total (using percentage) for each month.
[TABLE="width: 151"]
<colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="2"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody>[TR]
[TD="width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]nlist[/TD]
[TD="width: 68, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]1/1/2018[/TD]
[TD="width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]variance off average volume[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1208[/TD]
[TD="bgcolor: transparent, align: right"]119[/TD]
[TD="bgcolor: transparent, align: right"]0.01%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1209[/TD]
[TD="bgcolor: transparent, align: right"]127[/TD]
[TD="bgcolor: transparent, align: right"]0.06%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1210[/TD]
[TD="bgcolor: transparent, align: right"]114[/TD]
[TD="bgcolor: transparent, align: right"]0.05%[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Grand Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]120[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0.04%[/TD]
[/TR]
</tbody>[/TABLE]
- I will not know how many lists or months will be induced (so hard referencing the grand total cell will lot help)
- I am ok with using a separate table to show he percentages
- I want this to automatically update so I don't have to do it manually every month
any thoughts? I know calculations inside the pivot table can do lot.
Last edited: