Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivots - Auto calc % Sub total is of grand total
Hi, Is it possible in a pivot to take the subtotal lines e.g Customer 1 total is £35.60 and work out what % this is of the Grand total. In this Example Customer 1 would be 9% Customer 2 would be 68% Customer 3 would be 23% Customer summary ¦Yr 2006 Customer 1 Total ¦£35.60 Customer 2 Total ¦£261.50 Customer 3 Total ¦£87.60 Grand Total ¦£384.70 PS Trying to avoid paste special values and entering formulas manually VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569364 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivots - Auto calc % Sub total is of grand total
in this situation I would switch to a sum product solution - much easier in the long term - add up all the amounts for customer 1 and multiply by 100 then divide by all amounts for all customers - repeat for customer 2 etc. =100*(sumproduct((A1:A1000="customer1")*(B1:B1000) )/sumproduct((b1:b1000)*1))) assumes amounts are in column B -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=569364 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivots - Auto calc % Sub total is of grand total
Noob
One thing you will want to consider is the number of rows in your data. I had this same problem a few months ago and I tried the sumproduct and it worked, it just was not practical for me in this instance. The problem was my data had 20,000+ rows and was growing monthly. I found it quicker to take 10 minutes to manipulate my pivot table, copy paste the data into a new sheet then perform my % of subtotal calc. Rather than having my PC tied down for 30-60 minutes while Excel did the sumproduct. I was fortunate in that the users of my report were more concerned about seeing this in a graph than in the report. So I just based my graph off this new range. "VBA Noob" wrote: Hi, Is it possible in a pivot to take the subtotal lines e.g Customer 1 total is £35.60 and work out what % this is of the Grand total. In this Example Customer 1 would be 9% Customer 2 would be 68% Customer 3 would be 23% Customer summary ¦Yr 2006 Customer 1 Total ¦£35.60 Customer 2 Total ¦£261.50 Customer 3 Total ¦£87.60 Grand Total ¦£384.70 PS Trying to avoid paste special values and entering formulas manually VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569364 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivots - Auto calc % Sub total is of grand total
Thanks Mark for the warning. VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569364 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions | |||
How do I obtain total for auto filter value selected ? | Excel Discussion (Misc queries) | |||
Auto populate cells based on 2 cells division. | Excel Discussion (Misc queries) | |||
Auto Calc not working | Excel Worksheet Functions | |||
Auto Calc not working | Excel Worksheet Functions |