Calculating Average Market Share From 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).

Attachment : calculate averages from pivot table.zip

View 6 Replies


ADVERTISEMENT

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 View Related

Have To Show Market Growth And Share

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

Calculating Average Using Vlookup Table

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

Calculating Overtime In Pivot Table

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

Calculating Result From Pivot Table?

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

Pivot Table - Calculating Percentage Of Two Columns?

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

One Of The Values On Pivot Table Not Calculating Properly?

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

Calculating Overtime In Pivot Table For Multiple Weeks

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

Formula For Calculating A Ratio Using The Data In The Pivot Table.

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

Average IF In Pivot Table

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

How To Count And Average Pivot Table

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

Calculate Average In Pivot Table

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

Pivot Table Average Error

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

Pivot Table Grand Total Average

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

Average Of Monthly Totals In Pivot Table

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

Pivot Table Subtotal - Average Of The Fill Rate To Be A Value?

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

Display Grand Total In Pivot Table As Average Rather Than Sum?

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

Capture Average From Grand Total In Pivot Table

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

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.

View 2 Replies View Related

How To Calculate Average Without Counting Missing Values In Pivot Table

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

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 View Related

Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?

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

Create Pivot Table: Cannot Open Pivot Table Source File

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

VBA - Adjust Pivot Table Included Fields To Match Another Pivot Table

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

Pivot Table Query: Make A Pivot Table To Summarise The Data

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

Change The Date On One Of The Pivot Table And Pivot Table Match

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

Calculating A Rolling Average

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

Calculating Average Of A Range

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

Calculating Average Of 2 Averages

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







Copyrights 2005-15 www.BigResource.com, All rights reserved