% of total
 

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!

% of total
 

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

% of total
 

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!

% of total
 

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

% of total
 

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!

% of total
 

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