Free Microsoft Excel 2013 Quick Reference

calculated field - Pivot Table

Is it possible to add a calculated field into a Pivot Table where the Calced
field is a percentage of the subtotal for a column. For column A, I have five
values; four of these values are subtotaled to equal 20:

A
2
5
4
9
_
20
12
_
32

How do I add a calculated field (or item) which will show 2/20=10%,
5/20=25%,...and finally 12/32=37.5%

thank you
Matt


Hello all,

I'm having an issue creating a calculated field in a Pivot Table, and I'm desparately hoping someone can help me.

I have data in columns that looks like the following:
Item_Num Yield_Item
1234 0.999993012
1234 0.999998289
1234 0.999986024
1234 1
1234 1
1234 0.999994371
1234 0.999979875
5679 0.999997148
5679 0.999979294
5679 0.999992842
etc...

I'd like to calculate the Product of Yield_Item for each Item. I want that to be a calculated field in a PivotTable, as I need to do something else with the product of that data. However, when I create a calculated field and enter as the formula =Product('Yield_Item') the calculated field returns 6.999951571 (for item_num = 1234).

This is actually the sum of the field, and not the product of the field. By product, I want all numbers multipled by each other, which would give me 0.999951571. The similarity of the numbers after the decimal is a coincidence.

Now, if just place Yield_Item as a data item, and change the summarize by option to Product, it DOES produce the correct number (0.999951571).

Can anyone tell me how to set the calculated field to summarize by Product and not Sum? Am I completely missing something?

If anyone have any suggestions, I'd greatly appreciate it.

Thanks,
Jay

I have several calculated fields in a pivot table. Sometimes there's a division by zero, which returns #DIV/0!

In a regular formula, I can use an IF statement to return a blank or a zero, but it's not working in the calculated field.

I've tried both of these, but continue to get #VALUE! errors.

=IF(ISERROR('ACD Calls' /'-NCO' ),"",'ACD Calls'/'-NCO')
=IF('ACD Calls'+'Aban Calls' =0,"",'ACD Calls'/'-NCO')

EDIT: So can anyone tell me how to modify my calculated field to not return an error?

Hello to all you excel Gods,

I have created a pivot table to show the total gross and average gross of
sold items. I have included the amount of stock items and on order items.

My problem is that my average gross is showing for a total of the sold
new,sold used , stock and on order items as my worksheet has a seperate
column for sold, stock and on order.

Therefore I thought "Calculated fields" would work. I does for one instance
but I can't seem to have more than one calculated field show on the pivot
table.

I wanted to show a field for the total sold new and used stock then another
showing the total gross divided by a total of new and used sold.

Sorry for the essay.

Gross New Sold Used Sold Stock On order
5721.82 7 0 2 0
10569.21 29 3 3 9

Average gross I want to be Gross divided by New sold plus Used sold.

Thanks in advance

I need to define a calculated element (field) inside a pivot table from an OLAP cube

I know this is not possible, I saw this from another thread, so I am trying to add them in the cube.

Where and how do I do this?

the problem is the following:

i am having data records in a table with a name column, value columns, etc.
creation of pivot table works fine - sums are shown. No i would like to
insert a calculated field - calculating a sum of values from two different
columns - works fine but the subtotal/grandtotal figure below the new column
is not the sum, but a product as well.

Is there any way to get a sum instead for the subtotal/grandtotal of the
calculated column? Is there any other way to provide the same results?

thanks in advance

Hello all, new to the forum and I've got a question that I don't have the time to research anymore.

Basic question:
Is there a way to perform calculations, in this case basic subtraction, within pivot tables?

Background:
My company currently uses Excel as a Resource Management tool. We're not entirely concerned with the granularity offered by Project and other project or time management tools. We basically need to say that Employee A is on Project 1 from Start Date to End Date. We do that by inputting a decimal value (corresponding to percent of Employee A' time) between 0 and 1 into a column that corresponds with an individual week in the year. With this method we create two different reports using pivot tables. The Individual Project breakdown shows all employees on all projects and the percentages associated with each. The Headcount only shows an employee as '1' for whichever Project they are being counted for in the monthly headcount numbers. If an employee is 50/50 on two projects, he will only be shown as a '1' on whichever one is his Headcount for the month. Reflecting an employee on two projects requires a line for each project he's currently on.

I've attached an example, and you'll quickly see that this is a really sucky way of doing things. My best excuse is that it was handed down from on high and I've made it work as best I can.

My current problem is doing calculations within Pivot Tables (if possible with the current setup). For various reasons we have a 'Multiple' Project designation that is basically a sum of all the employee's current time. The idea is that it will show availability and other information, but for other reasons it has to stay the sum of all the employee's current utilization. I want to be able to capture that line in a Pivot table, subtract it from 1, and show availability. IE, if Employee A is only 75% or .75 utilized for a week, I want a Pivot Table or some sort of automatic display that shows him as 25% or .25 available for that week.

Also, feel free to point out glaring problems in the design of this spreadsheet and any other issues or questions you may have. At this point I'm so far down the rabbit hole that I'm not sure it I'm looking at daylight or an oncoming train... Once I get a few more details with this spreadsheet hammered out, my next task is to migrate it all to a robust Access database.... Thanks for what help may come!

I have already calculated a pivot table that has Total Volumes and Total
Costs as fields in it. Can I do a further calculation within my pivot table
on these calculated figures to calculate the Unit Cost = Total Volumes/Total
Costs? Or can you only do calculations on the orginal data not on the
calculated fields?

Thanks,
Fenella

Maybe someone can help me with this one.

I would like to create a pivot table in which i would like to compare spendings against a preseted plan for various categories and the subsegments of each category.

To do this I intended to add a manual field calculation which subtracts the actual spendings against the plan. Now when I inserted the calculation my pivot table reflects all subsegments for each category. Is there a way to avoid this artificial blow up?

I get the concept of the custom calc in a pivot table. I just cannot make it work. In this case, I just want to get a Count of the number of records that match my summarized data. I click on Formulas | Calculated Field and I enter a name "Count of Cases", then in the formula, I enter =COUNT(Account) where Account is the AccountID. My std fields are summing by Sales Rep just fine. But I only get a '1' in the Count of Cases field for each Sales Rep.

I consider myself a pretty good excel user, but this problem has me stumped!

Thanks,
Dave

Hi,

I am attaching a sample file. This is the sort of calculation I want to do on the result of the Pivot Table in a large number of data.

I have the volume, Month, Day of week and Week Number fields in the data. I plan to use Count of Week Number to give me the number of Mondays / Tuesdays in the Month. This I will use to calculate average VOL in Column H.

When I try to do a custom calculated field, I am not able to use the (sum of VOL / Count of Weeknum) - somehow the calculation defaults to (SUM of VOL / Sum of Weeknum).

Any ideas are always appreciated.

Thank you

Chandra

Dear All,
Please find below the Pivot table that has been generated based on the data that i have

******** ******************** ************************************************************************>Microsoft Excel - Dev Status sheet - BTC1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2=
ABCDE2Development TeamBTC CD   3Year Received2007   4Process 
AreaFICO   5     6   Data 7Year/MonthForecastWKDevelopments ReceivedTransmitted
Days82007/125049'07417.59  50'072110  51'0732111 50 Total 939.512Grand Total  939.5Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Currently, beside the Column "Transmitted days" i would like to have a coulmn "Percentage" which is a % figure of = Transmitted Days (E)/Forecast (B). Will Formulas --> "Calculated field" work for this?

Regards,
-Sravan

Hi there

I need to get the % of totals in the pivot table and don't know the formula
to use when using the Insert calculated field option in the pivot table.

Can someone assist

Hi -

How do I make a formula in a pivot table based on the value of a column?
I have a pivot table with the results of totals by employee number of 4
types of loans, type 1, type 2, type 3 and type 4. I get the count and
amount of the loans by employee. I want to add type 1 to type 3 for each
employee and get a total count and total amount. I am stuck.

Example Data

Employee
Number Type Amount
457 Type 1 $578,459.00
922 Type 1 $4,578,884.00
922 Type 3 $588,900.50
311 Type 4 $657,844.00
457 Type 2 $9,587.00
457 Type 3 $654,789.00
311 Type 1 $159,753.00
922 Type 2 $258,456.00
311 Type 3 $951,753.00
457 Type 4 $987,357.00
311 Type 1 $111,111.00
311 Type 1 $544,852.00
311 Type 3 $54,897.00
922 Type 4 $95,187.00
922 Type 2 $175,844.00
311 Type 3 $247,544.00
457 Type 1 $9,875.00
311 Type 2 $10,079.00
922 Type 3 $600,457.00
311 Type 4 $578,155.00

I get a pivot similar to this

Emp # Type 1 Type 2 Type 3
Type 4
Count $ Count $ Count $
Count $
311 3 815716 1 10079 3 1254194 2 1235999
457 2 588334 1 9587 1 654789 1 987357
922 1 4578884 2 434300 2 1189357.5 1 95187

How do I use the calculated field to add Type 1 and 3 together?

Thanks so much!

I am trying to calculate within a pivot table that has consolidated multiple
worksheets. I need to add two item fields together and then divide the
answer by the third item field. Does anyone know how to do this?

I have an EXCEL spreadsheet where one colum is populated with either YES or
NO as a result of comparing two dates in two other fields. I am building a
PIVOT table and would like to count ONLY the "yes", but, the pivot is
counting the yes and no. So the total is off. Does anyone know how I can
set up a calculation in the pivot table so that it ONLY counts the Yes's?
--
T

Hello,

I have a pivot table containing sales data in which the products are
in row headers and each month's net sales and cost of goods sold
(cogs) are the column headers.

eg.
January 05 January 04
Product Net Sales CoGS Net Sales CoGS

101 $5000 $2000 $4000 $1800

..
..
..
etc.

I have subtotals after every set of products in every product group
that simply add the Net Sales and CoGS data. I want to make a third
column heading named "Margin %" for each month which is equal to (Net
Sales - CoGS)/Net Sales. So, in each subtotal, I would like the sum of
Net Sales and CoGS but I would like the formula (Net Sales - CoGS)/Net
Sales to appear in the row subtotal under Margin %.

Is it possible to have different calculations in different fields of a
subtotal?

Any info/advice would be appreciated.
H

Is there any way to calculate a weighted average of data in a field as part of the pivot table function? The only alternative I find is a simple average calculation, but I would like to calculate the weighted average of the data in a field based on the data in another field. Specifically, I have a table that accumulates data regarding commissions paid, sales amount and income generated by each sale. The table includes the sales amount and the income earned on each sale, as well as other data. The pivot table I created shows the average of the income earned as a percentage of sales. However, each sale is different and a simple average calculation does not give me an accurate picture of the overall percent of income to sales.

Thanks for your help.

Hi. I asked this awhile back but didn't receive much feedback. But I'm still dealing with this and am hoping someone can help out.

I have a pivot table with several Value fields. I want multiple calculations to be displayed in the pivot table for that Value...for example, not only do I want "count", but also an average as well as standard dev.

How can I get the final pivot table to display multiple calculations of that Value? I'd rather not have to duplicate that column in the raw data, since I have many columns.

Thanks!

I am sorry to bother you all with another question about pivot tables. But i really need this one too to be answered. I created again some pivots and now i want to make calculations between the pivot items and pivot totals. Is this possible. And if not how can a use other Excel trick to overcome this problem? Please take a look to my attached file for more details.

I wonder if it is possible to make calculations between two pivot tables which have the same number of columns and rows and put the outcomes in a third pivot. For example Pivot 1 contains revenue data, Pivot 2 contains cost data and Pivot 3 provides the calculated outcome of revenues minus costs. I hope i explained well, if not i can attach an example. Thanks already.

My code works fine but its doesn't give me the Item value in Page
Field Pivot Tables. The code is supposed to show the 1998 for Time
dimension value but it just shows [Time].

********* SAMPLE PIVOT TABLE ****************

Time is in PageField
County is in RowField
Unit Sales is a Measure
Product is in column Field

Time 1998
Unit Sales
Product Category Country Total
Canned Tuna Mexico 1422
Canned Tuna Total 1422
Vegetables Mexico 24784
Vegetables Total 24784
Grand Total 26206
+++++++++++++++++++++++++++++++++
VBA CODE


	VB:
	
 pvttable 
    c = 1 
    For i = 1 To .PivotFields.Count 
        r = 1 
        Cells(r, c) = .PivotFields(i).Name 
        r = r + 1 
        For x = 1 To .PivotFields(i).PivotItems.Count 
            Cells(r, c) = .PivotFields(i).PivotItems(x).Name 
            r = r + 1 
        Next 
        c = c + 1 
    Next 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks

i'm having issues with page fields. i am creating a pivot table for attendance tracking, meaning the data consists of multiple names, dates and percentages. everything is fine except i'd like to have my dates as the page field. It will not allow me to group any dates, so lets say i have 30 days listed as options, i can either select all, or only 1, no mulitples. i cannot figure out why i cannot group mulitple dates together.

example, what was bobs average from the 12th, 16th & 19th. instead of allowing me to select the 3 dates i want, i can only either view 1 at a time, or the entire 30 day range.

any ideas on how to fix this? when i have my dates in the columns field, i can select mulitple items, but when dates is in the page field, i cant. i know it can be done, im looking at a pivot table that has it!! but no one can figure out who made it [/img]

Hi,
I am trying to insert PERSENTILE or QUARTILE Calculations in Excel Pivot table, Present In Exce I have some columns including a column named Days....I have to calculate 25th, 50th and 75 th persentiles for that column and have to put in pivot and need some dropdowns in pivot, not getting these values dynamically.

Please some one help me to get this happen.....

I have a pivot table that displays different summary volumes of the different types of lumber that has been cut in one day. What I need is to have the pivot table calculate the percentage of one product of the total. Here is some sample data. I would also like those percentages to refresh when the pivot table is refreshed.

Specie SM Grade SM Grade Product SM Grade Width Data Total
SP COM 4/4 04" Sum of SM Grade Volume 1327
Sum of SM Grade Pieces 342
06" Sum of SM Grade Volume 1998
Sum of SM Grade Pieces 392
08" Sum of SM Grade Volume 10248
Sum of SM Grade Pieces 1189
10" Sum of SM Grade Volume 8413
Sum of SM Grade Pieces 736
12" Sum of SM Grade Volume 39332
Sum of SM Grade Pieces 2697
4/4 Sum of SM Grade Volume 61318
4/4 Sum of SM Grade Pieces 5356
SP Sum of SM Grade Volume 212834
SP Sum of SM Grade Pieces 15826

Any help would be greatly appreciated.

Thank you,
Liz