I have a data set in pivot table which has 12 months and each month has two
items Capital & Operating spend. I want to get percent % of total for each
item but not percent of whole year. I want each month to be the base, for
instance if I spent 12 hundered per year and 100 each month, in January i
spent 37 capital and 63 operating. % of total function should return 37% Vs
63% and not 3.08% and 5.25% which are based on total year. Thanks in advance
for your help!

Excel 2007 PivotTable
% of month
http://www.mediafire.com/file/mgmiyq...04_27_09b.xlsx

Thanks Herbert! I apologize not mentioning that I am using excel 2003. In
addition, each month have two type of charges by each department, one is
capital other is operating. I want to get percent by capital/operating by
department by month. I have my departments in the rows and month going
accross on the top (horizontal). Below is how I want my results layed on the
sheet. My data has month column, Cap/O&M Column, department column, and
amounts column.

Jan Feb
Cap Opr Total Jan Cap Opr Total
Feb
Department
1 37% 63% 100% 35% 65% 100%
2
3


"Atiq" wrote:

I have a data set in pivot table which has 12 months and each month has two
items Capital & Operating spend. I want to get percent % of total for each
item but not percent of whole year. I want each month to be the base, for
instance if I spent 12 hundered per year and 100 each month, in January i
spent 37 capital and 63 operating. % of total function should return 37% Vs
63% and not 3.08% and 5.25% which are based on total year. Thanks in advance
for your help!

Excel 2007
Added another level,
moved row fields into column fields.
Sorry about the wrong Excel version,
but Excel 2003 would not be aesthetically pleasing.
http://www.mediafire.com/file/jwiznz...04_27_09c.xlsx

Thanks! In "Pct" column it shows error "#NAME?". It might be due to excel
version.

"Atiq" wrote:

I have a data set in pivot table which has 12 months and each month has two
items Capital & Operating spend. I want to get percent % of total for each
item but not percent of whole year. I want each month to be the base, for
instance if I spent 12 hundered per year and 100 each month, in January i
spent 37 capital and 63 operating. % of total function should return 37% Vs
63% and not 3.08% and 5.25% which are based on total year. Thanks in advance
for your help!

"Pct" uses the functions Countifs() and Sumifs()
and Structured References,
all unique to Excel 2007.
Roger to the rescue...?