| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Question
I have this small pivot table. Here is part of it. I hope this goes across
correctly as it's very hard to retain the columns as they should be once I post this: COL A COL B COL C COL D % DIFFERENCE YEAR MONTH WE PAID FROM LAST MONTH 2005 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% The last column (% Difference) has been added manually with the formula =SUM(C11-C10)/C10. I can't figure out how to enter this as a calculated field. Is this formula too advanced for pivot tables? Connie |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Question
The pivot tables have an option of showing a field as a % difference from
other fields, may be that will fit you. In this case, go to Layout of the pivot table, add the field "We paid" again, with the same aggregation. Open the field settings and select "Options", it will open a new area where there is a dropdown for "Show data as". Choose "% Difference From", then in the "Base field" box select "Month" and the "Base item" select "(previous)". Hope this helps, Miguel "Connie Martin" wrote: I have this small pivot table. Here is part of it. I hope this goes across correctly as it's very hard to retain the columns as they should be once I post this: COL A COL B COL C COL D % DIFFERENCE YEAR MONTH WE PAID FROM LAST MONTH 2005 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% The last column (% Difference) has been added manually with the formula =SUM(C11-C10)/C10. I can't figure out how to enter this as a calculated field. Is this formula too advanced for pivot tables? Connie |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Question
Thank you very much! This works wonderfully! Connie
"Miguel Zapico" wrote: The pivot tables have an option of showing a field as a % difference from other fields, may be that will fit you. In this case, go to Layout of the pivot table, add the field "We paid" again, with the same aggregation. Open the field settings and select "Options", it will open a new area where there is a dropdown for "Show data as". Choose "% Difference From", then in the "Base field" box select "Month" and the "Base item" select "(previous)". Hope this helps, Miguel "Connie Martin" wrote: I have this small pivot table. Here is part of it. I hope this goes across correctly as it's very hard to retain the columns as they should be once I post this: COL A COL B COL C COL D % DIFFERENCE YEAR MONTH WE PAID FROM LAST MONTH 2005 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% The last column (% Difference) has been added manually with the formula =SUM(C11-C10)/C10. I can't figure out how to enter this as a calculated field. Is this formula too advanced for pivot tables? Connie |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Question
This works quite well....just one little thing I found after working with it.
The % Difference does not show for January 2006. It should calculate from the previous month, which is December 2005. Here's my table (the % difference is calculated on the "we paid" column. COL A COL B COL C COL D COL E ROW 8 YEAR MONTH WE PAID CUST. PAID % DIFF ROW 9 2005 $53,502.66 $3,014.91 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% ROW 15 SEP $892.38 $474.06 -79% ROW 16 OCT $9,185.27 $1,609.77 929% ROW 17 NOV $1,096.57 $152.06 -88% ROW 18 DEC $1,089.07 $779.02 -1% ROW 19 ROW 20 2006 $10,467.73 $212.76 ROW 21 JAN $5,119.40 ROW 22 FEB $901.38 -82% ROW 23 MAR $343.12 -62% ROW 24 APR $4,103.83 $212.76 1096% ROW 25 MAY -100% ROW 26 ROW 27 Grand Total $63,970.39 $3,227.67 "Miguel Zapico" wrote: The pivot tables have an option of showing a field as a % difference from other fields, may be that will fit you. In this case, go to Layout of the pivot table, add the field "We paid" again, with the same aggregation. Open the field settings and select "Options", it will open a new area where there is a dropdown for "Show data as". Choose "% Difference From", then in the "Base field" box select "Month" and the "Base item" select "(previous)". Hope this helps, Miguel "Connie Martin" wrote: I have this small pivot table. Here is part of it. I hope this goes across correctly as it's very hard to retain the columns as they should be once I post this: COL A COL B COL C COL D % DIFFERENCE YEAR MONTH WE PAID FROM LAST MONTH 2005 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% The last column (% Difference) has been added manually with the formula =SUM(C11-C10)/C10. I can't figure out how to enter this as a calculated field. Is this formula too advanced for pivot tables? Connie |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table Question
The % Difference from calculation ends with the outer field (Year). If
you want the difference to flow through the pivot table, you could add a field to the source data, in which you calculate the year and month. For example: =TEXT(A2,"yyyy-mm") Then, add that field to the pivot table, and use the % Difference from calculation. Connie Martin wrote: This works quite well....just one little thing I found after working with it. The % Difference does not show for January 2006. It should calculate from the previous month, which is December 2005. Here's my table (the % difference is calculated on the "we paid" column. COL A COL B COL C COL D COL E ROW 8 YEAR MONTH WE PAID CUST. PAID % DIFF ROW 9 2005 $53,502.66 $3,014.91 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% ROW 15 SEP $892.38 $474.06 -79% ROW 16 OCT $9,185.27 $1,609.77 929% ROW 17 NOV $1,096.57 $152.06 -88% ROW 18 DEC $1,089.07 $779.02 -1% ROW 19 ROW 20 2006 $10,467.73 $212.76 ROW 21 JAN $5,119.40 ROW 22 FEB $901.38 -82% ROW 23 MAR $343.12 -62% ROW 24 APR $4,103.83 $212.76 1096% ROW 25 MAY -100% ROW 26 ROW 27 Grand Total $63,970.39 $3,227.67 "Miguel Zapico" wrote: The pivot tables have an option of showing a field as a % difference from other fields, may be that will fit you. In this case, go to Layout of the pivot table, add the field "We paid" again, with the same aggregation. Open the field settings and select "Options", it will open a new area where there is a dropdown for "Show data as". Choose "% Difference From", then in the "Base field" box select "Month" and the "Base item" select "(previous)". Hope this helps, Miguel "Connie Martin" wrote: I have this small pivot table. Here is part of it. I hope this goes across correctly as it's very hard to retain the columns as they should be once I post this: COL A COL B COL C COL D % DIFFERENCE YEAR MONTH WE PAID FROM LAST MONTH 2005 ROW 10 APR $6,204.40 ROW 11 MAY $25,634.46 313% ROW 12 JUN $1,755.81 -93% ROW 13 JUL $3,435.61 96% ROW 14 AUG $4,209.09 23% The last column (% Difference) has been added manually with the formula =SUM(C11-C10)/C10. I can't figure out how to enter this as a calculated field. Is this formula too advanced for pivot tables? Connie -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) | |||
| Pivot Table Question | Excel Discussion (Misc queries) | |||
| Pivot Table Problems | Excel Discussion (Misc queries) | |||
| Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel | |||
| pivot table question, sum fields? | Excel Worksheet Functions | |||
Linear Mode
