Calculated Fields and Items in Pivot Tables in Excel

i have a pivot table that calculates resource availability. each resource
may work on multiple projects, and each of these projects require a certain
number of hours. the grouping is by person, by project. for each person, i
need to show hours allocated toward each project (detail) and then total
number of hours allocated (subtotal). i also need to show a calculated field
of % hours available (subtotal only) for each person.

John project 1 30 hours (detail)
project 2 15 hours (detail)
Sum of hours 45 hours (subtotal)
Caculated field (1-sum(hours)/40) (subtotal)

how can i show this calculated field only as a subtotal but hide its detail?

thanks much.

--
mt

Hi,

Given a set of raw data shaped like this:

Cat Item Value
--------------------
1 1 45
1 2 37
1 3 40
2 1 38
2 2 34
2 3 31
3 1 36
3 2 39
3 3 40
.......

I created a pivot table that uses "Cat" as a row field and "Value" as a data
field. I then change the summary function for the "Value" field to use
"Average". Next, I add the "Value" field as a data field again and this time
change the summary function to "StDev". This all works ok.

I'd like to add a calculated item to my pivot table that allows me to take
the standard deviation of "Value" and divide that by the average of "Value"
to calculate the coefficient of variance.

When I try to add a calculated item to calculate the coefficient of variance
(i.e. STDEV(value) / AVG(value) , I get the following error from Excel:

"Multiple data fields of the same field are not supported when a pivottable
report has calculated items"

However, even if I eliminate this problem, I get another message from Excel
when trying to add a calculated item as follows:

"Averages, standard deviations, and variances are not supported when a
pivottable report has calculated items"

Can anyone suggest how I would go about adding this calculation (i.e.
STDEV(value) / AVG(value) to my pivot table?

Thanks in advance,
Jim

I've got a huge stack of procedural data and I'm using a pivot table to get control of it. When all i wanted to do was MEAN and COUNTIF type functions, this was great, since it allowed me to divide up the data into meaningful clumps.

Now I need to find the 95th Percentile in the data [ equivalent to PERCENTILE (array, .95) ] but that isn't a default operation in a pivot table.

I'm already using VBA in this project, so I'm not afraid to add some more code if required, but I'm really stumped.

Thanks,

Andre

How can I create a calculated field that includes formulas with MIN, MAX or
AVERAGE?
Becasue the summary form calculated field of a pivot table cannot be changed
(is always SUM), Excel returns a different number when I try to insert a
field that includes such a formula.
I am trying to include a field like this to calculate minimum possible cost:
=MIN('Price')*SUM(Order Quantity)
Any suggestion on how to do it?

Hi,

I'm using Excel 2000. My pivot table contains calculated fields, the problem
I'm experiencing is that the grand total for the field is almost 2 times
higher than it should be. However, if I look at the individuals rows that
calculation is correct, if I sum the calculated field column (with exception
to the grand total) the sum is correct but the highlighted column doesn't
match it's grand total....not sure why.

I'm not user if the problem is with my source data worksheet, the only thing
I'm doing which maybe an issue is- for the rate column, I have it equal a
cell that's resides on another work sheet.
--
Linda