# VBA - Create Calculated Field Based Upon Count Not Sum

May 26, 2012

Can I create a calculated field based upon the count of data items in fields as opposed to sums. My data is confidential so here is a dummy example:

Can I take the count of Cars per individual salesmen and divide by the total count of vehicles? Ultimately, I will need to code this into VBA once I know if it can be done.

Vehicles l salesman
car1 Bob
car2 Kelly
car3 Bob
car4 Bob
car5 Tim
car6 Tim
car7 John

With this result:

Salseman l Percentage of Vehicles
Bob 43%
John 14%
Kelly 14%
Tim 29%

## How To Create Calculated Field Based Off Two Avg Fields In Pivot Table

Feb 12, 2014

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.

## Pivot Table Calculated Field With Count

Jun 26, 2014

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.

Pivot Table:

Rows: Years, Months
Values: Tickets (Count), Hours (Sum)
Month
Tickets (count)
Hours(sum)
Throughput (Calculated)

2013

Apr
65
22
2.97

[Code] ..........

## Create Pivot Table That Has Calculated Field For The Difference In Amount

Feb 27, 2013

I have 3 fields:

Person, FinancialYear, AmountUSD
Pete, FY12, 8000
Pete, FY13, 7000
Jeff, FY12, 5000
Jeff, FY13, 5500
Sam, FY12, 4000
Sam, FY13, 3500

How to create a pivot table that has a calculated field for the difference in AmountUSD between FY12 and FY13 (for each person).

## Pivot Table Calculated Field Based On 2 Columns?

Jun 9, 2014

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?

## Displaying MIN Date Based On Criteria In Calculated Field Of PowerPivot?

Jan 23, 2014

I have a database with a list of transactions with multiple fields, including:

Customer ID
Store ID
Item ID
Date
Price

I am wanting to add a calculated field that displays the first (or MIN) Date from the date column, looking at all of the orders where the customer ID, store ID, item ID and price are the same.

In the attached file I have shown what I currently have, as well as a solution using a helper column, and formulas I would use if I wasn't using powerpivot.

## Excel 2010 :: Pivot Table Add Calculated Field Based On Certain Text In Report Filter

May 29, 2014

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.

=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)

I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.

## New Calculated Field In Powerpivot

Nov 14, 2013

I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .

## Lookup Value In Calculated Field

Jun 21, 2014

Take a look at the attached spreadsheet: Value_Lookup.xlsx

We have a pivot table with the "Product" on the Row context and a date ("FullDate") selected on the filter context.

The FactTable is related to the Calendar table by "FullDate" (which is marked as Date Column in Powerpivot)

In the Calendar Table, next to "FullDate" we have 2 more dates in the same row: "1 Month Back" and "1 Quarter Back".

We just need 2 calculated fields that return the "Price" value from the selected "Product" on the Calendar dates "1 Month Back" and "1 Quarter Back".

## Referencing Sum With Calculated Field

Dec 14, 2011

I have a pivot table that looks like this:

Code:

productname impressions cost CPM
product1 1,000,000 4523 4.7
product2 5,000 2400 48.45
product3 3,000,500 6,000 3

CPM is a calculated field that uses the formula =cost/(impressions/1000)

The problem is that the numbers in the CPM field are not, in practice, the results of that formula. They are averages (since it's a data field, I had to choose whether it was to be a sum, average, etc.). This means that, for example, the CPM 4.5 on the first row is not a calculation using the two numbers to its left. It is the average of the output of thousands of calculations done on the raw data, in which every line item with a productname of product1 had its CPM calculated, and then all those output CPMs were averaged together.

This is not what I want. I want a field that calculates based on what's already in the pivot table. So that the formula would be more like:

=(sum of cost)/(sum of impressions)/1000

## Display Calculated Field

Mar 4, 2012

I have a calculated field - if it is 0 I would like to now show the 0 - just a blank cell - can I do that?

## Message Box Calculated Field VBA

Oct 12, 2012

I have created a macro to run different imports on the system and I need a message box calculated at the en displaying the message "Write Total Charges to Import: xxxx".

The total is the Sum of the cells listed on Column B. I have defined my total and need to show the message, and after the user has writen down the total and clicks OK, the cell showing the total is deleted. how to make it work?

The syntax I am using is:

Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")"
MsgBox "Write Total Charges to Import: "
Range("B" & EndRow + 2).Select
Selection.Delete

The calculation to display is the result of (Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")") but when I add the formula next to "Write Total Changes to Import" does not work.

## Calculated Field In Pivot Table?

Jan 21, 2014

Can I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?

## Pivot Table Calculated Field

Mar 26, 2014

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.

## Pivot Table Calculated Field?

Jun 19, 2012

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?

## Sum Total Of Calculated Field In PivotTable

Apr 28, 2008

i have a database of monthly sales of a regional distributor and im using a pivot table to manipulate my data. i want to show only what account makes a positive sales for a specific product and i want to get the total number of accounts who have the positive sales. there are multiple entries in my database, the condition is if an account have positive sales of a specific product regardless on how many times the account bought the result should be 1 (meaning one buying account)

i've tried to use a calculated field in my pivot table, i get the correct result per account but when it comes to grand total its not getting the sum of the total number of accounts. here is the sample of what i need to get..

you can see that there are multiple entries but it shows only 1 per account when it has at least 1 positive sales of a specific product and 0 if it hasn't any sales/negative... and for the total it adds the number of accounts to get the total number of buying accounts..

## Pivot Table - Calculated Field / Item?

Mar 18, 2014

I have a data that consists of months, forecast and actual.

I've created a pivot table where; months is on the row, forecast and actual is on the values

I wanted to know the percentages between forecast and actual (forecast / actual) to see how it perform each month.

How do I go about inserting a pre-defined named called "percentages" on pivot table with calculated field or calculated item on pivot table itself?

## Pivot Table - Add Calculated Field Not Working

Apr 26, 2012

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.

## Removing Calculated Field From Pivot Table Using VBA

Feb 5, 2014

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"

[Code]....

## Retain Calculated Field Number Formatting

Aug 16, 2006

I use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.

This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.

I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:

=IF(ISERROR(Contribution/'Block Hours'), "0", (Contribution/'Block Hours'))

would be nice if the formula itself could include the number formating. I tried including something like , 0.00% at the end, but it does not work.

## Sumif Function In Pivot Calculated Field?

Mar 20, 2007

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.

## PivotTable Calculated Field With IF Function/Formula

Jun 23, 2008

I have a PivotTable which shows the number of packs produced in a month. I created a calculated field which shows the number of vehicles needed to move these packs. The number of packs per vehicle is dependent on two criteria, the Pack Description and the Store. My calculated field, Shunts, is described as:

=Packs / IF(Store ="Middlewich",IF('Pack desc' ="Tall",30,60),IF('Pack desc' ="Tall",26,52))

which represents the logic

Middlewich & Tall = Packs / 30
Middlewich & other = Packs / 60
Other & tall = Packs / 26
Other & other = Packs / 52

However, it doesn't work!

I know Calculated Fields can be difficult to work with, but this is crazy and I think I'm missing something obvious as I'm sure I've had IFs work like this before!
I've attached my example, also showing the values I'd expect to see.

## Create New Worksheet And Copy Data Based On Date Field?

Apr 8, 2012

copy the excel sheet data from one sheet to another sheet?

-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)

Sn Code Type next calib
5BPR CORR7-Apr-12
4BPR CORR7-Apr-12
73BPR CORR7-Apr-12
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12
17BRG CORR9-Apr-12
311DP CORR9-Apr-12
227DP CORR9-Apr-12
227DP CORI R9-Apr-12

Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.

Example: Today's Date is 8-Apr-12

So, I want to copy following record to new work sheet (when I click the button / run the macro).

Sn Code Type next calib
9BRG CORR8-Apr-12
10BRG CORR8-Apr-12
11BRG CORR8-Apr-12

## Excel 2007 :: Pivot Table Calculated Field

Mar 19, 2009

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)?

## Using Pivot Table Summary Fields In Calculated Field?

Jan 21, 2012

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.

## Pivot Table - Incorporate Calculated Field For Sub Group

Sep 6, 2012

How to incorporate a calculated field for a sub group.

Here's what the data in the table looks like.

Competitive

Managed

ID
A
B
C
A
B
C

[Code] .....

I am looking to get 3 calculated fields onto the Right hand side that would essentially be the following Formula
=IF(L4"",IFERROR(D4/H4,"n/a"),"")

Thus,
1st Column
=IF(Competitive(A)"",IFERROR(Competitive(A)/Managed(A),"n/a"),"")

2nd Column
=IF(Competitive(B)"",IFERROR(Competitive(B)/Managed(B),"n/a"),"")

3rd Column
=IF(Competitive(C)"",IFERROR(Competitive(C)/Managed(C),"n/a"),"")

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.

## Calculated Field Average Error In Pivot Table?

Dec 10, 2013

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.

## Calculated Field Deletion Affects All Pivot Tables?

Jul 2, 2014

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.

## Create Auto Populated List Field Based Off Multiple Fields

Apr 21, 2014

I have two or more fields of data, where parts of them are auto populated based on inputs from elsewhere. That part i have working fine, however the problem arises in taking that information and displaying it as a Bill of Materials list on another tab in my excel file. I want to take the information, and only display it in the BOM tab if that piece of information is being used (ie item count field not being 0). I know how to do that part in a rather simple format, however i'd prefer to make it so that when i have the list, and a certain item isn't used then its not included in the BOM, and an empty space is not left behind. I would prefer this to be all be done automatically by a formula if possible instead of just having to resort to sorting the table every time the tool is being used.

Is this even possible in excel, A quick example doc is attached, shows 2 example fields. i'd like to take the information from those 2 fields. and somehow create a single list, with a single heading along the top, with all row containing a 0 "Unit Count" to be omitted entirely from the list, and not have a blank space where the item should be. Keep in mind these fields may be greater than 2, and may not be position one after the other in a strait down line.

Example.xlsx

## 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.

So what I need is Total Sales/X

X = Cell Reference