Calculating Average Market Share From A Pivot Table
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
ADVERTISEMENT
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
Jul 13, 2006
I have access to a huge database in excel that involves sales data. The database is catagorized like:
Column A = Country
Column B = Product
Column C = Company
Column D = (Product) category
Column E = Sales
Column F = Period
Column G = Sales of particular company
Column H = Market category
Further details: see attached file!
I am looking for way to express developments ( absolute and relative) in market growth and market share over time.
I have 3 questions:
How can i show the growth of the total market sales over time (period 1 to 4)?
How can i show the growth of the Int comapny sales over time (period 1 to 4)?
How can i show the market share of the Int company over time (period 1 to 4)?
Market growth can be calculated by comparing the sales amounts of the total market (or the Int company) to previous periods. Market share can be calculated by expressing the sales figures of the Int company in percentage of the total sales in a partical period (i=1,2,3,4).
View 6 Replies
View Related
Dec 18, 2008
i have data on excel which has the following fields:
Invoice No,Inv.Date,Month,Customer,Part Number,Description,Qty.I want my final pivot table to have the following results for every part no. and description
Total monthly sales
total year sales
and for each model sale calculated as sale quantity / week
I have tried the following way but it did not work:
just next to the pivot table under the heading"AVG" i used the following formula to get the sale/week on cell o6=grand total sale /(applicable no. of weeks).and when i am copying the formula to the cells lying below the formula is being applied as that of the first cell so getting the same result for all the rows.is there a way to calculate sale/ wk within the same table
View 9 Replies
View Related
Nov 30, 2010
How to report Overtime in a pivot table. Apparently this is more difficult than it seems. Please take a look at the cross posting at [URL]....
I have a sample file there
View 7 Replies
View Related
Jan 3, 2014
I have a very large pivot table. 1 output is the sum of seconds it takes to complete a task. I would like to convert this field from seconds to minutes. the data is all in seconds, so i need to divid the sum by 3600. is there a way to do this calculation inside the pivot table?
View 1 Replies
View Related
May 14, 2014
why it works (but it obviously does). I crabbed off the sheet, modified it with my data and the percentages calculate reliably.
What I can't figure out is that in the Pct calculation in the table is the formula:
=GETPIVOTDATA("S_FCR",PT_1,"Center",[Center],"Date",MONTH([Date]))/GETPIVOTDATA("C_ID",PT_1,"Center",[Center],"Date",MONTH([Date]))^2
The formula itself makes perfect sense in terms of the numbers and filtering involved....but why does it actually work and what does the "^2" do?
View 2 Replies
View Related
Feb 3, 2014
This pivot table is used to track referrals for an employment service, and needs to show monthly totals of referrals, enrolments, cancellations and placements.
I have the referral date as one of the row lables, but it seems that each of the others now only reflectes back to that date rather than the date entered in that value
EG:
5 clients referred in September, One was placed in October, 2 in December and 1 in Jan but the totals for placed are all showing in September rather than the month the clinet was placed.
View 4 Replies
View Related
Feb 5, 2014
I have employees hours worked entered in multiple rows. Columns are Date, Name, Hours worked; then a column that calculates the week number.
The data is entered each day so the Names are not in order and I'd rather not have to resort by name each week.
I'm collecting that data along with several other fields in a pivot table, then I have a summary sheet that uses GETPIVOTDATA formulas to compile a ton of statistics.
I want to be able to filter the pivot table on any given number of weeks. I need to be able to calculate overtime (greater than 40 hours in a week) for each name over the filtered weeks. So I could want to see OT for weeks 4, 5, 7 combined.
View 1 Replies
View Related
Jan 17, 2008
I can’t seem to work out the formula for calculating a ratio using the data in the pivot table.
I’ve added a column next to the pivot table to work out the ratio between to columns.
=SUM(B11/C11)
But what if there is no data in cell (B11), I want to return a “ “ (blank space)… but it returns a #DIV/0!
View 14 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
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
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
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
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
View Related
Mar 19, 2013
On a worksheet, I created:
- 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.
View 3 Replies
View Related
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
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
Jan 22, 2007
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies
View Related
Apr 29, 2006
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
View 2 Replies
View Related
Oct 8, 2009
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly
View 10 Replies
View Related
Oct 9, 2009
What could be the syntax (in a code) for averaging a range in col C. the range values are given in E3 and E5. (E3=508 & E5=1200) These values changes each time I open the workbook.
View 2 Replies
View Related
Jun 7, 2013
I have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.
I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:
=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)
+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)
But if I use the same for the average scores is works fine until a person covers two duties:
=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)
+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)
This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?
i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.
View 1 Replies
View Related
