Excel 2007 :: Pivot Table Calculated Field With Reference
Nov 18, 2012
I have a set of sales data and need to create a run rate which is simply = Total Sales/Selling Day
Selling day changes most days.
I put the calculation into my pivot data but it's summing up, (instead on 12 I get 720) so I changed this to Average so I get the right figure in the Pivot but when I then use this field, it doesn't use the Average amount, it uses the summed figure.
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
I'm having an issue getting the correct results from a calculated field in a pivot table in Excel 2007.
In column A I have "Business Name", in B I have a sum of the amount of lines a customer has, and C is a MAX of the number of employees the customer has at their location.
In one example I have a business with sum 50 lines and max 30 employees but when I try to make the calculated field 50/30 (should equal 166%) i get 9.2% instead.
The reason appears to be because there are 18 types of lines the customer has and 18 x max 30 = 540 and 50/540 is 9.2%. In the attached example it's all the same business location so the total number of employees (30) is the same for each row and each row in the raw data is a set of lines with similar features.
Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
Ihave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.
I have a pivot table with 2 row filters (dept and name) and then three columns - Year 1, Year 2. I need to add a calculated field inbetween Year 1 and 2 to show the variance in dollars from ( Year1-Year2) as well as an additonal a column that shows the percentage change between the tw o years . I know I can copy this over and add formuals in Excel but I need to retain the features of the pivot tables due to the 2 row filters?
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query.
From the exported data, I have the following:
Date opened (MM/DD/YYYY HH:MM:SS AM/PM) Date closed (MM/DD/YYYY HH:MM:SS AM/PM) calendar_stc (this exports in seconds, such as 136) (this equals date closed minus date opened for each record) assignment group
From this export, I'm trying to make a pivot table to show the average calendar_stc per assignment group, per month, per year; but in hours, not seconds.
I thought this was an easy process, create pivot to group row data by years then by months. Then add in assignment group to to the Rows as well. From there, I added to the values section of the pivot the calendar_stc. The default, of course, is to sum each row. But I need the average so I switched it to average. Now it is showing the correct average calendar_stc in seconds per group. To make it hours, I figured I could add in a calculated field. To do that I added the formula of =calendar_stc/60/60 for the calculated field. When I hit okay, it's giving me the SUM in hours for each row. Modifying the field settings to average does not change the numbers. So I went back into the formula and modified =average(calendar_stc/60/60). And that is not working either.
Here is an example to show the math with my formula.
I am trying to create a dynamic pivote table that can be update using a value in a cell. I can get everything updating and working correctly except I am unable to remove the one calculated feild from my pivot table. I am able to remove all other fields without problem. Code below: (lines 13 and 14 is the code that i cant get to work)
Private Sub CommandButton1_Click() Dim dt As String Dim dt2 As String On Error Resume Next dt = Sheets("Executive Summery").Range("M1") dt2 = dt & "2"
I am trying to calculate a ratio for Hours per Ticket by month. I have a pivot table that COUNTS tickets and SUMS hours per month. I was hoping to create a calculated field to the jist of COUNT(Tickets) / SUM(Hours), but this does not work because calculated fields aggregate data.... I was hoping to keep it all in a pivot table so that I can still use slicers.
I have a Table with 2 columns "Due Date" and "Completed Date". The pivot table from the table gives the count of each column. I want to have a calculated column giving the % complete, ie. "Count of Completed"/"Completed Date"*100. When I attempt to create this formula (using Pivot Table Options > Fields Items and Sets > Calculated field), I get a DivZero error, even though both columns are not zero. How do I create such a calculated column?
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
The other option would be how to set up conditional formatting to always take on the appearance of Column K of the pivot table. Thus, this calculated section would always appear as though a part of the pivot table and would adjust based on any pivot table items being expanded and collapsed.
I created a custom formula for a pivot table.the existing columns are weekly averages.I made a formula to calculate the average of 5 individual weeks.But when one of the value is blank.Blank cell is considered as zero.and hence the final average is wrong.
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
I have a Pivot table that pulls the Avg of two fields for two months, see example below.
Avg Gross $ Avg Net $ Jan 2014 20 10 Feb 2014 30 20
sample 1.png See sample attached.
The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".
On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.
I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.
I have data sheet that has a field minutes. I a pivot table I have the Average Minutes per category. How it the pivot table can I show the Average minutes as Days, Hrs, Minutes? I am working in Excel 2007.
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
'Pivot Table Calculated Fields" - Below is my pivot table.
I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).
I know the following steps:
1) Click on the Pivot Table
2) Choose "Formulas"
3) Choose "Calculated Field"
4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)
5)......then I don't know what to do
Sum of Quantitymonthsupplier idjanfebGrand Total 754466163150842519337568263901785022875682639410025035075682639500 75682639642523866375682639780012492049756826398209209756826399292292 756826400264264756826401178178757494037292292761034919209209867233456264264(blank) Grand Total245154236931
I have two fields where users enter a security identifier (cell P1) and the declaration date (cell P2) and i'd like to be able to pull all the records from the master table (A1:K10) where the security id matches (column H) and the request date (column C) is <= the declaration date (i've attached a sample file for your reference). I tried using a pivot table but it doesn't let me filter for "less than or equal to" values for the request date.
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I am trying to do some basic VBA in my spreadheet, I just want to create an auto-open statement that inputs then next sequential number in column A1 every time the s/s is opened i.e.1,2,3,4 .....
I am using excel 2007 and my knowledge is very limited. I used a code:
Sub AUTO_OPEN Range("A1") = Format(Range("A65536"), "YYYYMMDD") & Right(Range("A1"), 3) + 1 End Sub
(the first time you open the file you would need to add the first entry '20021122001)
But this does not work and I get a compile error when run thru debugger.
I'm getting an error message in when I attempt to create a pivot table calculated field formula using the SUMIF function. My questions are (A) Can SUMIF be used in a pivot table calculated field and (B) If Yes, then why am I getting an error message? To expand more on my second question(B). My scenerio is as follows.
I need to compute the Total Inventory Value (Quantity X Cost) of all our inventory that is aged over 60 days. I have a "Total Value" field that gives me the inventory value at a sku level. also have a "Days Since Last Receipt" (DSLR) field that gives the Days since the Inventory was last Received at a sku level. For Example:
SKU A --Total Value=$100 -- DSLR=50 SKU B --Total Value=$75 -- DSLR =70 SKU C --Total Value=$40 -- DSLR =80
Using the above scenerio and example I tried to execute this calculated field formula: =SUMIF(DSLR,">60",Total Value). However I get an formula error message.
I have my raw data in one Worksheet and have made multiple pivot tables based off this data. Since I needed to have the same calculated fields in all these Pivot Tables, I used to simply duplicate the pivot table sheet and edit the pivots to create a new table.
Now, I'm trying to create a new pivot table from scratch, by selecting the base data table and going to "Insert Pivot Table". However I see the same Calculated Fields and upon removing, the field is removed from all other Pivot Tables.
I have a Pivot table showing costs of several types of items.
The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).
So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.
Can this be done in Excel 2010?
I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.
