# 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

Hello everyome; thank you for taking your time to check out this problem.

Im a intermediate excel user, been playing with pivot charts. If im using numbers I can make the pivot chart do alot of things, but using the words YES and NO, i just cant figure out.

I have an Excel Sheet.
Basically Looks like:

MEDICAL OFFICE | Region | YEAR & QUARTER | PCP/S |Met Standard?

Under Met Standard there will be a YES or a no.

Now i need to organize the pivot chart in such a way that I can basically see
PER Office what the Percentage of YES Are for a specific appointment type.

Since a pivot table changes in size, I can't use a formula referencing a specific cell. I'm trying to figure out a way to write a formula that will seach a column of a pivot table for a specific text string, and if it exists, use the value in the next cell in a calculation. The pivot table has two columns and resides in column L and M.

So basically, what formula would I use if the word "Special" is found anywhere in COLUMN L of a pivot table, and if so, it will take the value found in the next cell in COLUMN M, and divide it by the value found in J25.

Hi there!

I've created a document with many pivot tables. After creation of the entire document, a new addition was requested, which called for a calculated field.

I went to the first Pivot Table, created the calculated field and inserted it. Worked great.

But now I need the same field in every pivot table in the workbook.

This calculated field shows up in the field list, but will not allow me to drag it into new pivot tables. Error message is "The field you are moving can not be placed in that Pivot Table Area". But it is a calculation/field created for that area. (And worked perfectly in the first pivot table)

I've tried going to the pivot table toolbar, finding the custom calculation and hitting ok. The pivot table refreshes, but does not add the calculation.

I've even tried recreating the custom calculation in each successive pivot table, but of course it will not let you name them the same. So even this time-waster will not work.

Am I approaching this wrong? Any suggestions?

Thanks!

I have a pivot table grouped by month. It has the numner of customers and the number of cancellations in the cells, by machine. i want to calculate a cacellation rate (number cancelled machnes/total machines) per month in a pivot that can be refreshed, can this be done? eg:

jAN FEB
A CANC 10 1
COMP 20 10
ALL 30 11
B CANC 15 5
COMP 20 10
ALL 35 15

i WANT A FIELD SHOWING THE CANCELLATION RATES OF

A 10/30 1/11
B 15/35 5/15

ETC ETC?

I'm trying to enter a variance column to compare data from April versus May. I figured out how to calculate the data by using Pivot Table Tools > Options > Formulas > Calculated Item. However, after I enter the formula, I get a pop-up error that says "There are too many records to complete this operation."

The pivot table is 185 rows and the source table is 473 rows. It doesn't seem like the table is so large that Excel wouldn't be able to handle calculating the data. Any ideas on what could be going wrong?

-K

Hi - need help with creating a CALCULATED field using a VLOOKUP inside a pivot table - simplified version of what I need to do is below (assume tables start at A1 in the top left corner)

I'm OK with the calculated field (if its possible) but its the VLOOKUP in a Pivot Table I'm struggling with.

PIVOT TABLE Count of 'dog or cat columns' Column Labels Row Labels dog cat AB1 34 54 AB2 55 22 AB3 22 17 AB4 11 34 AB5 76 22 AB6 45 87

From which I want to create the following CALCULATED FIELD:

=VLOOKUP(CONCATENATE(\$A4,B\$3),A2:B13,2,FALSE)

And lookup from these two fields - which I need to place in the pivot table.

LOOKUP FIELDS (need to go into Pivot Table) AB1DOG \$5.00 AB2DOG \$10.00 AB3DOG \$15.00 AB4DOG \$20.00 AB5DOG \$25.00 AB6DOG \$30.00 AB1CAT \$2.00 AB2CAT \$4.00 AB3CAT \$6.00 AB4CAT \$8.00 AB5CAT \$10.00 AB6CAT \$12.00

Thanks in advance for your comment!

I have an Excel 2007 Pivot table that has a calculated field called Margin. I have the Margin field in the "Values" part of the pivot that is set to "sum" in the value field settings.

I want to be able to filter out all the positive margins and see only the negative margins. For the life of me I can't seem to figure a good way to do this. Below is a shot of the data.

http://www.beachtomountains.com/images/MarginFilter.jpg

TIA

My pivot table is setup like this:

Name.........Pass..........Fail.........Total
Joe.............3...............1.............4
Nancy.........4...............1.............5

Where the Pass and Fail numbers are actually one field (QC Result) broken out by the two values that populate that field, Pass or Fail. The pivot table then added the Total column at right as a simple sum.

In trying to add a calculated field, Excel is only letting me divide one existing field in the list by another existing field in the list. But the data on which I want to calculate is actually from one field, QC Result. I would like to do the following formula: (Count=Pass / Total Count).

And ideally would like it to show as a single column to the right of the "Total" column.

Am I missing something here, or do I need to setup my pivot table another way?

Hello,

I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.

=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))

The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.

The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.

Else, if neither condition exist, WMAPE is Abs Error / Actual.

It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.

I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?

Any suggestions?

I receive following errors when I try to sort calculated field of a Pivot Table:

"AutoSort and AutoShow cannot be used with custom calculations that use positional references. Do you want to turn off AutoSort/Show?"

Are there any workarounds?

This forum won't let me upload a sample file but this is what I have:

SOURCE DATA:

Header Row: Asset, MV and Date
Data Row 1: BOAMS 2010, 200, 6/25/2010
Data Row 2: MLMI, 100, 6/25/2010
Data Row 3: MLMI, 600, 6/26/2010
Data Row 4: BOAMS 2010, 50, 6/26/2010

PIVOT
Row Label: Asset
Values: Sum of MV and Sum of Change (Calculated Field shows difference of MV between two dates)
Column Labels: Sum of Values and Date

I am using Excel 2007.

I'm trying to insert a calculated field into a pivot table and I'm having A LOT of trouble. Everything I read says to just click on the pivot table and the Pivot Table Tools tabs appear (excel 2007). No problem so far. Now you're supposed to click on the "Options" tab. Again, no problem.

From here you are supposed to select the "Formulas" option. But it's ALWAYS greyed out. No matter where I click on the pivot table or the PivotTable Field list, it's always greyed out. Any help...anybody?

I created a calculated field in a pivot table with the intent of creating a custom formula for that field which would calculate an average of 40+ other pivot table fields. When i go to the "Pivot Table ->Options->Formulas->Calculated Field" window and enter the formula as follows Excel limits me to 255 characters:
Average ('field 1', 'field 2', etc.)

When I tried using a colon in the formulas to indicate a range of fields to be averaged Excel would not accept the formula:
Average ('field 1':field 40')

Any ideas on how I can accomplish this?

I need to format a pivot table calculated field to a %. When I right click on the field name and go to field settings the "number" option is not listed as it is on the other fields. I can manually format the whole column but then whenever I refresh the pivot table it reverts back to a #. Is there any fix for this?
Thanks

I have an Excel pivot table using an Access table as the external data source. In the Access table a particular numeric field (Field1) has many null values.

In the pivot table, I have added a calculated field called "Upper" with the formula being Field1 + Field2. If the Field1 value is null, I want the Upper value to be null, but at the moment the pivot table seems to treat the null values as zero, and a value is displayed. For example, if Field1 is null, and Field2 is 320, I want the calculated field to return null, but it is returning 320.

I've tried changing the calculated field formula to: IF(Field1="","",Field1+Field2)

But the pivot table still sees the null value as zero (not "") and returns a value.

Is there any way to make the pivot table return null (or an error) in the 'Upper' calculated field if Field1 in the Access table is null?

And does anyone know why the pivot table treats Field1's null values as zero when used in the formula of a calculated field, but as null when actually brought into the pivot table as a data field?

Hello, All
I have a budget that built in a form pivot table. Some of items in this
table are calculated from primary accounts listed in the table. When
I've tried to add one more calculated item which has to summ total of
the previous subtotals (they are also calculed item) excell did not
want to add in the new calculated item formula bar of all of this
subtotal names.
I suppose that the problem is that lenght of a formula bar is limited.
Does anybody face the problem like this? May be there is the way to
avoid this? Thanks for assistance.

I've got a pivot table based on a large s/s with lots of blank rows. I could write a macro to strip rows out but I'd rather no resort to that for various reasons.

Before it was only showing items where there was non-zero data

My problem is that now I've added some calculated items, it's showing them for all the combinations including those that have all zero values.

How do I stop it doing that? It makes the PT a bit too big, I was hoping to fit it onto a single page but at the moment it's taking up 10 pages of mostly zeroes.

TIA

Good Day,

I'm trying to create a calculated field in a pivot table that will tell me the slope of my data. For the y-values, I'm using my data and for the x-vales, I've tried both a time value and an automated index, since the times are evenly incremental. With both cases, I get a Divide by Zero error.

Has anyone tried this before or does anyone know why I am getting the error or how to fix it?

My data is normalized, as it has been collected automatically, so I'm pretty sure that is not the issue.

Thanks in advance

Can any one help me with a calculated item in a pivot table. I have a simple pivot table with:

Acct, Type, Amt for Today, Amt for Yesterday, Net Change

The Net Change is a calculated item based on difference from today and yesterday.

Results are giving me a report of all accts and types regardless if they have data. If I remove the net change, I get just the rows that have data not zeros or blansk, when I add the net change back in, I get a row for every acct and type combination regardless if there is data

Help!!
I have a pivot table field as follows:
one column shows company sales by product
one column shows market sales by product
one column is a calculated field that shows company sales / market sales (market share)
Each column has a grand total

I want to add another column that shows market sales * "average market share"

How can I reference the Grand total of market share in another calculated field. I have tried using getpivotdata in the calc field but it doesn't seem to be allowed.

Thanks in advance for any help

Hi

I'm trying to calculate the percentage change between values on a pivot table.

I have attached a sample. What I want is for each column to know the %change for each day ie by how much was the new value up/down based on the previous day. I can do this the normal way by calculating %change and get the results but when I come to copy the formula (by dragging it down the rest of the columns) it just repeats the same number as in say E5 for when trying to find the % change of 1129 over 1107.

I can do this manually for every line but that will take a lot of time. Is there any way in a pivot table to still calculate the differences and have this done automatically for all rows (by dragging it down)?

In my real table I have hundreds of rows which will take a long time to find the % change for each one.

Is this possible? If so please tell me how.

Thanks