Pivot Table / Create Average Of Proportions (Custom Calculation)
Aug 2, 2013
I am trying to find expected proportion of code per country by looking at current values. I have a list of countries and associated classifications (0-5) with counts, similar to as follows:
Country
code
count
USA
1
65465
USA
2
54651
USA
3
65411
[code]...
I am interested in creating a pivot table with the average of each code as a proportion of each country. The final table would be expected proportion of codes. The pivot table for this set would look like this:
Row Labels
Average
1
5.4%
2
3.9%
3
4.7%
[code]...
Mean per code of the proportion of code per country
View 1 Replies
ADVERTISEMENT
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Jul 3, 2014
I have created a pivot table from raw data. The result is correct although i need to alter something.
The values that show (615 & 300) are in minutes. I need to divide this by 60 to get hours. The resulting chart will then show hours and not minutes. The main reason is that minutes will be into thousands very quickly and hours are more meaningful.
Is there a way to do this within the pivot table.
Attached link to file:
[URL]
View 2 Replies
View Related
Aug 19, 2014
I set up my pivot table but on the Value area I need to make a multiplication with the total values, not just sum it all up.
Normally in the Value area you can choose Sum, Count, Average, etc, but you can't multiply it with another cell. There is a "product" option but it multiply the total values together, it doesn't ask for another cell value.
PT-Value-Field-Settings.png [URL]....
View 2 Replies
View Related
Apr 25, 2014
Calculating usage...
Take the Current date and I need that inventory ---- find the date 7 days before this date ---- get the inventory for that date --- current inventory - previous inventory / 7 will give me the daily usage... the data will be in a pivot table so I need to make sure it doesnt change the usage when moving the table around...
View 1 Replies
View Related
May 3, 2014
Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like
ICV TRUCK
LCV BUS
LCV TRUCK
MCV BUS
MCV TRUCK
PICKUP &
SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of
MCV BUS
LCV BUS
MCV TRUCK
ICV TRUCK
LCV TRUCK
PICKUP
SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.
Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?
View 6 Replies
View Related
Feb 2, 2014
My boss asked me to format a pivot table like this with irregular ranges:
I've had a crack (as seen below and have gotten everything right except the strange group irregular ranges. I'm stumped! I'm not sure if pivot tables even have a function for irregular group ranges.
My raw data looks like here is its layout:
View 7 Replies
View Related
Jul 7, 2014
I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
View 1 Replies
View Related
Aug 8, 2012
I was wondering if it is possible to create a custom pivot table report filter? I would like to take an existing pivot table report filter and manually add values into it. I would like to do this because I have multiple pivot tables, some with the same values and some with different values and I have a VBA code from Contextures that applies a mass filter to all fields with the same name. So if i could manually add values into one report filter, I could filter from one location and have all my pivot tables update at the same time if they contain the value that i would like to filter by.
View 1 Replies
View Related
Apr 16, 2009
i have a database of the kind:
Model Version Color Store
1 a zz HH
2 b xx QQ
3 c yy OO
4 d ww PP
I need to obtain in the y axis the model, and in the x axis the store.
The data per each model and store should be: the version and color.
I'm using Pivot table to do that, but i'm only able to obtain the "count" (or sum or other formula) but I'm not able to obtain the content of the cell.
Example:
Store HH PP
Model
1 Version a d
Color zz ww
2 Version b c
Color xx yy
View 9 Replies
View Related
Nov 5, 2009
I have my data in a pivot and need to average some of the data and ignore duplicates, blanks and numbers... another complication is that the data I want to average is in two separate columns (first and last name). I also need the average to be subtotaled based on my pivot...
View 9 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
Apr 30, 2014
I often use the same file/pivot table for both month and weekly reporting. In my source data my field headers show 1,2,3,4..etc. This way I can use for month or weekly. But I often change the "Custom" name on the Field Value Settings to something more descriptive like "Jan" or "Week1"
The problem is that each time I update the the "Custom" name the Pivot Table auto refreshes and it takes forever to re-label all the columns.
Is there a way to suppress this refresh when updating the Custom Field Name?
Capture.JPG
View 2 Replies
View Related
Sep 17, 2012
Is there a way to average and counta the values in the row of a each category in pivot table?
Example
Pivot Table
Fruits
Grad Total
China
UK
[Code]....
Is this possible?
Fruits
Grad Total
China
UK
USA
UAE
[Code]....
View 1 Replies
View Related
Dec 20, 2007
With Pivot Tables, there is the ability to add Grand Totals to Rows or Columns, but I want to add Averages to the end of the row instead. Can this be done? I have tried Calculated Fields but can't get the right result. Auto Merged Post;Hi again,
After I posted this I found another similar post, where the answer was that the "Average" calculation has to be done outside the pivot table, ie. there seems to be no way the pivot will give averages for rows, only grand totals.
If this is the case then I will have to work around it.... I was hoping it could be done within the pivot because I have graphs linking to the pivot and they all go spak when I update the pivot with different data. The number of columns will change all the time, meaning the average will need to be reworked. Just trying to save time!
View 8 Replies
View Related
Apr 22, 2008
I am trying to get some averages using pivot tables. However, whenever I set up my pivot table and then go to field settings and pick average, all I get are # DIV/0 errors in all the boxes where there were numbers.
View 4 Replies
View Related
Apr 10, 2014
On a pivot table, I want the grand total to be the average. When I code it, the code changes all the values in the column to an average.
if a person's % for April is 95% & the sum of all the people in the table is 1924%. If I change the grand total to average, the person's april % changes to 19% (which is an average instead of a total.
[Code] .....
View 1 Replies
View Related
Sep 22, 2011
How can I get a pivot table to calculate the average of the monthly totals and not the average of all the raw data.
Below is a pivot table of the data on the left. When the pivot table averages the months it calculates the average of every single record, 48.83. How can I get it to average the monthly totals:average(apr,may,Jun)= 179.05
Date cost Sum of cost Years Date
4/1/2011 -75.00 2011
2011 Average Monthly Average
4/11/2011 -58.88
Apr May Jun
[Code] ........
View 1 Replies
View Related
Aug 2, 2006
I created two pivots:
1 provides sales data for companies in a time period,
2 provides market share data for companies in a time period.
Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields).
Attachment : calculate averages from pivot table.zip
View 6 Replies
View Related
May 1, 2014
I am working on a pivot table and am having issues with our subtotal: Sum / Averages.
The pivot is setup as so:
excel help.jpg
The problem is I need the average column to average the viewable area, and not from the data, so for example G28 should be 55 and not 6. Also, I need the Average of the fill rate to be a value.
View 2 Replies
View Related
Feb 11, 2013
I have a column in my pivot table with values that are formatted as percents. I am trying to make the grand total reflect the average of all values in the column, but it keeps showing a sum of all values.
Example: the values in the % column are 90%, 100%, and 110%. I want it to show 100% (the average), but it is showing 300% (the sum)
View 1 Replies
View Related
Nov 6, 2013
Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.
View 5 Replies
View Related
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.
View 2 Replies
View Related
Aug 2, 2006
I created two pivots: 1 provides sales data for companies in a time period, 2 provides market share data for companies in a time period. Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields). Check out my attachment too to symplify this question. How how i formulate this formula?
View 4 Replies
View Related
Jan 23, 2014
I have a problem when Im trying to calculate averages in my pivot table. I dont know how to handle my missing values. If I leave them blank I cant get the average at all because the values end up under the dates instead of in a column next to the dates where I want them to be. If I write 0 it uses it as a value, eg 2+2+0 and i get 1,33 instead of 2. Also the zero could be a value for some of the parameters, so it's no good.
I am new to pivot tables.
View 5 Replies
View Related
May 1, 2009
1) I want to create a pivot table in a new tab that has both the prefix “ENG” and the date of creation as the new name of the tab every time a new table is required, using the example headers highlighted in the attached file.
2) Then if possible format the table as in the example, i.e. bordered cells for all the data and the dates only aligned to the right. Example in tab “Eng 01 05 09”
No 1 is the most import question I would like help on. Formatting is not crucial. This is only to make the table easier on the “eye of the beholder”, I am writing this spreadsheet for people who have less knowledge than myself and would like to keep it as simple as possible, (run a macro and get the results).
View 11 Replies
View Related
Jan 26, 2012
I regularly build a pivot table using VBA. I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia. (In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)
I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....
I have attached a file showing how far I can get with vba and where i want to get.
I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....
grouping reqs.xls
View 2 Replies
View Related
Feb 24, 2010
I'm trying to create what is essentially a report card for our staff. I have all the data in a pivot table and then I am using 'GETPIVOTDATA's in the report template to extract the information, linking the sheets via a cell that contains the staff members name. Easy enough. However for reasons of efficiency and practicality I would like to be able to select the names from a drop down list on the sheet rather than having to type it in manually or maintaining a separate sheet for everyone. The operators will change from month to month and there are approximately 90 staff at any given point in time that will need to be reported on so it really needs to be sourced from the pivot data.
View 7 Replies
View Related
Jun 3, 2014
Can record the all those actions which we need to do on a regular basis. In my work i have to create the pivot table to a data which gets changed every day but the data sequence remains the same.
I have tried to record the macro for the pivot table but when i tried to run the macro again then the below error message comes.
"run time error 5" "Invalid procedure call or argument" when i click on debug then the below macro program is highlighted in yellow color.
View 1 Replies
View Related
Jun 17, 2014
For example...
I have data for monday.. Tueday ..Wednesday as individual excel
All the excel have same column names and dynamic values.
Is there a way i can create one pivot with different excel as source
View 1 Replies
View Related
