Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
I have a pivot table displayed in monthly columns.
I have rows grouped by a vehicle make, then by a vehicle model. First value: Under each vehicle make, vehicle model, and month I have a row value displayed as a whole number. This data is supplied from separate worksheet. Second value: I then inserted a calculated field to display a percentage (this percentage is displayed in each month directly below each whole number) Fields for the calculation is supplied from the same separate worksheet as first value. Third value: I then inserted a calculated field to display the result of the percentage*whole number. (First value * second value) Individually by vehicle make and vehicle model the calculations work great, however, when the subtotals are listed, the % and the totals DO NOT add up at the bottom. The right hand totals add up fine. Note: The % displayed by model are consistent across the monthly columns, but are different for each model. Sample of one model: 0804 0805 0806 0807 0808 0809 Total 66 54 69 59 83 47 378 65% 65% 65% 65% 65% 65% 65% 43 35 45 38 54 31 246 First line is the monthly dates: The top (1) whole value will add correctly at the subtotal and total value under each model, but the % and the calculated (%*top value=) bottom value does not add up correctly. Any help would be greatly appreciated. -- B Rager |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
Instead of a calculated field,
add a column to your source data: http://www.freefilehosting.net/download/3e4b0 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
I tried that as well. My totals work for the first and third values, but the
% values are not accurate in the totals/subtotals. Thank you for the example, but the months have to be displayed from left to right in order for me to give totals for each month for all models. The table is very large (I only provided a small piece of the information) sorted by: Auction/model/make The data to the right is fine when totaled (the % remain the same across the months, so the math works). It's when it starts subtotaling/totaling at the bottom of each month (when the % are then different) it goes a little crazy. Its trying to "Average" all the different % at the auction level. I realize this is probably confusing. Its a little difficult to explain. Maybe this will help: Here's the data columns: Auction Make Model VIN Count Date (yymm) 3 Month Return Rate % Forecasted Returns I need grouped by Auction, Make, Model to display by month the VIN count, 3 Month Return Rate %, and Forecasted Returns. VIN Count * 3 Month Return Rate % = Forecasted Returns At the end of each Auction, the VIN count should add up to a total. The Forecasted Returns should add up to a total which is a % of the VIN Count. This part actually works, but when it gets to the overall total of all the auctions, the % appears to be "average of average of average" BUT the total Forecasted Returns value is the total. If you do math for the overall total, it does not work. (VIN Count * 3 Month Return Rate % = Forecasted Returns) so when I show the totals on a separate report, not good that it doesn't match. -- B Rager "Herbert Seidenberg" wrote: Instead of a calculated field, add a column to your source data: http://www.freefilehosting.net/download/3e4b0 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
Can you upload the file?
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
I think I have it. I have calculated outside of the pivot:
VIN Count * 3 Month Return Rate % = Forecasted Returns (by month) In the pivot I plug in the values for "VIN Count" and "Forecasted Returns", however at that point I create a "calculated field" to back into the "3 Month Return Rate %" (instead of using the value from the data table. Eeven though its the same value, the pivot treats it differently). The totals now use the % by weighing the averages (??) and now my totals work. -- B Rager "B Rager" wrote: I tried that as well. My totals work for the first and third values, but the % values are not accurate in the totals/subtotals. Thank you for the example, but the months have to be displayed from left to right in order for me to give totals for each month for all models. The table is very large (I only provided a small piece of the information) sorted by: Auction/model/make The data to the right is fine when totaled (the % remain the same across the months, so the math works). It's when it starts subtotaling/totaling at the bottom of each month (when the % are then different) it goes a little crazy. Its trying to "Average" all the different % at the auction level. I realize this is probably confusing. Its a little difficult to explain. Maybe this will help: Here's the data columns: Auction Make Model VIN Count Date (yymm) 3 Month Return Rate % Forecasted Returns I need grouped by Auction, Make, Model to display by month the VIN count, 3 Month Return Rate %, and Forecasted Returns. VIN Count * 3 Month Return Rate % = Forecasted Returns At the end of each Auction, the VIN count should add up to a total. The Forecasted Returns should add up to a total which is a % of the VIN Count. This part actually works, but when it gets to the overall total of all the auctions, the % appears to be "average of average of average" BUT the total Forecasted Returns value is the total. If you do math for the overall total, it does not work. (VIN Count * 3 Month Return Rate % = Forecasted Returns) so when I show the totals on a separate report, not good that it doesn't match. -- B Rager "Herbert Seidenberg" wrote: Instead of a calculated field, add a column to your source data: http://www.freefilehosting.net/download/3e4b0 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Subtotals do not add properly
Using freefilehosting? Let me fudge with the numbers, titles, etc. due to
the sensitivity of the data and see if I can upload it. Looks easy. Give me about 2 hours. (I have a meeting) -- B Rager "Herbert Seidenberg" wrote: Can you upload the file? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Blanks not displaying properly? | Excel Discussion (Misc queries) | |||
Pivot Table subtotals | Excel Discussion (Misc queries) | |||
Subtotals in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Subtotals | Excel Discussion (Misc queries) | |||
Pivot Table subtotals | Excel Worksheet Functions |