|
Pivot Table Percentage of Sub-Total
I have searched through all the posts and can't find the answer I am looking
for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Hi,
Please show us the source data. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Michelle" wrote in message ... I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Hi,
Pivot tables don't give us access to the Subtotal fields. You could create this in the spreadsheet using formula: Assume your data is in A1:C100 where column A contains the Car 1, column B contains A, B, C and column C contains the values. Assume you set up the following report area in the range E1:H5: Car 1 A 10 10% Car 1 B 10 10% Car 1 C 80 80% Car 2 A 20 20% Car 2 B 80 80% The formulas in G1 and H1 are =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7) =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)/SUMIF(A$2:A$7,E1,C$2:C$7) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michelle" wrote: I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Hi
I finally was able to find my post. What does the -- represent in your formula? "Shane Devenshire" wrote: Hi, Pivot tables don't give us access to the Subtotal fields. You could create this in the spreadsheet using formula: Assume your data is in A1:C100 where column A contains the Car 1, column B contains A, B, C and column C contains the values. Assume you set up the following report area in the range E1:H5: Car 1 A 10 10% Car 1 B 10 10% Car 1 C 80 80% Car 2 A 20 20% Car 2 B 80 80% The formulas in G1 and H1 are =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7) =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)/SUMIF(A$2:A$7,E1,C$2:C$7) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michelle" wrote: I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Hi
I was finally able to find my post. The source data would be like the following. We have a number of different vehicles that can be checked out by various departments. When it comes time to calculate the insurance cost each department will pick up, I need to calculate the percentage for each department. It doesn't give me the correct percentages unless I manually calculate it...well there are like 50 some vehicles. Vehicle Department Miles Dodge1 Dept1 20 Dodge1 Dept2 10 Dodge1 Dept1 10 Chevy1 Dept3 20 Chevy1 Dept3 15 Chevy1 Dept2 20 Chevy2 Dept3 20 Chevy2 Dept4 25 "Ashish Mathur" wrote: Hi, Please show us the source data. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Michelle" wrote in message ... I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Double unary minus:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html -- David Biddulph "Michelle" wrote in message ... Hi I finally was able to find my post. What does the -- represent in your formula? "Shane Devenshire" wrote: Hi, Pivot tables don't give us access to the Subtotal fields. You could create this in the spreadsheet using formula: Assume your data is in A1:C100 where column A contains the Car 1, column B contains A, B, C and column C contains the values. Assume you set up the following report area in the range E1:H5: Car 1 A 10 10% Car 1 B 10 10% Car 1 C 80 80% Car 2 A 20 20% Car 2 B 80 80% The formulas in G1 and H1 are =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7) =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)/SUMIF(A$2:A$7,E1,C$2:C$7) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michelle" wrote: I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
Ok...I have a problem though as I don't know what the percentage is. That is
what I am trying to calc. The source data would be like the following. We have a number of different vehicles that can be checked out by various departments. When it comes time to calculate the insurance cost each department will pick up, I need to calculate the percentage for each department. It doesn't give me the correct percentages unless I manually calculate it...well there are like 50 some vehicles. Vehicle Department Miles Dodge1 Dept1 20 Dodge1 Dept2 10 Dodge1 Dept1 10 Chevy1 Dept3 20 Chevy1 Dept3 15 Chevy1 Dept2 20 Chevy2 Dept3 20 Chevy2 Dept4 25 I hope this makes sense...thanks for your help. "David Biddulph" wrote: Double unary minus: http://www.mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html -- David Biddulph "Michelle" wrote in message ... Hi I finally was able to find my post. What does the -- represent in your formula? "Shane Devenshire" wrote: Hi, Pivot tables don't give us access to the Subtotal fields. You could create this in the spreadsheet using formula: Assume your data is in A1:C100 where column A contains the Car 1, column B contains A, B, C and column C contains the values. Assume you set up the following report area in the range E1:H5: Car 1 A 10 10% Car 1 B 10 10% Car 1 C 80 80% Car 2 A 20 20% Car 2 B 80 80% The formulas in G1 and H1 are =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7) =SUMPRODUCT(--(A$2:A$7=E1),--(B$2:B$7=F1),C$2:C$7)/SUMIF(A$2:A$7,E1,C$2:C$7) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michelle" wrote: I have searched through all the posts and can't find the answer I am looking for. I have a pivot table where I am trying to get the percentage of the subtotal for each item, but it only lets me get the percentage of the grand total. I saw a previous example which basically summed up the percentages which not work in my case. The following is what I would like to see...but I keep getting either #n/a or percentages of the grandtotal which are not going to work. Vehicle Dept Miles Percentage Car 1 A 10 10% B 10 10% C 80 80% Car 1 Subtotal 100 100% Car 2 A 20 20% B 80 80% Car 2 Subtotal 100 100% Grandtotal 200 |
Pivot Table Percentage of Sub-Total
|
| All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com