Excel Statistics 19: Grouping Categories w Formula & Pivot Table

Hey gang~
I have gone to the edge and back and surprised that I have not found the answer (which concerns me that it may not be possible to do). I am working with some baseball stats and trying to find the players average based on how many years they have played. I have compiled the complete list of MLB players since 2008, and some finished their careers in 2008 some began their careers in 2009 and then you the ones that have played the past three seasons, so obviously there is not a concrete number of years to divide by for the totals. I am just learning the pivot table universe and looks like a phenominal tool. Just seems that I have a speed bump here to get what I need. Am I missing something BEFORE I create the pivot, like count how many years or something. I hope this is not too vague, feel free to shoot me any questions that need to be answered. And I thank you in advance for your help!
Happy New Year
UGALY

AVG Hitters A B C D E F G H I J K 1 Values 2 Row Labels Sum of AB Sum of H Sum of 1B Sum of 2B Sum of 3B Sum of HR Sum of R Sum of RBI Sum of BB Sum of K 3 Abreu, Bobby 1745 491 319 109 8 55 284 281 254 354 4 2008 609 180 117 39 4 20 100 100 73 109 5 2009 563 165 118 29 3 15 96 103 94 113 6 2010 573 146 84 41 1 20 88 78 87 132 7 Abreu, Tony 193 45 32 11 1 1 16 13 4 47 8 2010 193 45 32 11 1 1 16 13 4 47 9 Alonso, Yonder 29 6 4 2 0 0 2 3 0 10 10 2010 29 6 4 2 0 0 2 3 0 10 11 Alvarez, Pedro 347 89 51 21 1 16 42 64 37 119 12 2010 347 89 51 21 1 16 42 64 37 119 13 Anderson, Garret 557 163 118 27 3 15 66 84 29 77 14 2008 557 163 118 27 3 15 66 84 29 77 15 Andrus, Elvis 1068 284 235 32 11 6 160 75 104 173 16 2009 480 128 97 17 8 6 72 40 40 77 17 2010 588 156 138 15 3 0 88 35 64 96 18 Ankiel, Rick 996 244 142 55 5 42 146 133 94 270 19 2008 413 109 61 21 2 25 65 71 42 100 20 2009 372 86 52 21 2 11 50 38 26 99 21 2010 211 49 29 13 1 6 31 24 26 71 22 Antonelli, Matt 57 11 8 2 0 1 6 3 5 11 23 2008 57 11 8 2 0 1 6 3 5 11 24 Atkins, Garrett 965 255 177 44 4 30 123 147 81 158 25 2008 611 175 119 32 3 21 86 99 40 100 26 2009 354 80 58 12 1 9 37 48 41 58 Excel 2007

What's the best way to have group 1 sum divided by total of stat code 6100? I've tried =getpivotdata, but hasn't seemed to be fruitful. 6100 will always be a stand alone figure, while group 1 may be stand alone or a group of stat codes 9000 and 9010. Thanks to any and all wisdom!


******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B6 =
A B C D E 2 Sum of Adjusted Value         3 DEPT GL Dept Desc Stat Code2 Stat Code Total 4 600 NURSING AD                     6100   2099 5     Group1 9000 177 6     Group1 Sum   177 7 600 Total       2276 8 601 NURSING SU                     6100   2099 9     Group1 9000 958 10     Group1 Sum   958 11 601 Total       3057 12 603 NURSING ORIENTATION            6100   2099 13     Group1 9000 521 14     Group1 Sum   521 15 603 Total       2620 16 622 MED SURG                       6100   1029 17     Group1 9000 6138 18       9010 314 19     Group1 Sum   6452 20 622 Total       7481 21 633 TELEMETRY                      6100   1024 22     Group1 9000 7889 23       9010 1120 24     Group1 Sum   9009 25 633 Total       10033 26           Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

please zoom in while looking at the picture.
The first spreadsheet shows departments and their spending
The second spreadsheet calculates total spend by departments over 100k and some percentages.
I need to be able to count how many transactions there are per department over 100k. Is there a formula and/or a way to do this with a pivot table and a formula? I need it to look like the format in the second spreadsheet. My current formulas are as follows

Column B =SUM(G2:G164) in a hidden column to calculate the number of contracts over 100k

Column C =B2/$G$912 to calculate % of total

Column D running total for cummulative percentages (formula)

Column I =SUM(H3:H66) in a hidden column to calculate the total spent per department

Column J =I2/$I$914 to calculate % of total
Column B =SUM(G2:G164) to calculate the number of contracts over 100k

Column C =B2/$G$912 to calculate % of total

Column D running total for cummulative percentages (formula)

Column I =SUM(H3:H66) to calculate the total spent per department

Column J =I2/$I$914 to calculate % of total

Column K running total for cummulative percentages (formula)

Is there an easier way to do this with formulas and or pivot tables?

Any help is appreciated. I am sort of new to pivot tables.

I want to count the number of events that occur in 10 minute increments over
a period of days. I can easily count count the number of events, but the
default is for each minute and I don't see any way to change it to 10 minute
increments? Is this possible? Any help is appreciated.

I have a workbook with one sheet dedicated to several copies of pivot table data, and I am trying Group one of the tables by Month, but when I do all the other tables which have Date in the row label also automatically switch to the same grouping. How do I group only one pivot table by Month and leave the others set to daily? Thanks for any ideas.

I need to group a column field (which contains numeric values ranging anywhere from 0-infinity) so that it will display a count of each value for values 1-60 & then a final column which will count everything that is

Hello

I have a Pivot Table that is constructed from a macro which sometimes works and sometimes doesn't. The reason being is I have three seperate groups in the Pivot Table and the third group changes in size each month. In my macro for the third group I currently have;

Range("M12:M38").Select
Selection.Group

So I had 27 different types in this group when the macro was created but I want to change the code in the macro so it searches for the last cell before the Grand Total in the Pivot Table and will then group from M12 (always constant) to that last Active Cell and label it as International. This is an extract of what my Pivot Table currently looks like and in it you will see the International Group only has four diferent types this time;


Sum of Duration

Type Prefix Total

Fixed 02 493.27
03 105.55
07 320.13
08 198.84 1117.79 (subtotal of fixed, Cell outside of Pivot Table)
Mobile 04 797.27 797.27 (subtotal of mobile, Cell outside of Pivot Table)
International 32 487.48
44 27.56
62 112.25
66 140.26 767.55 (subtotal of International, Cell outside of Pivot Table)
Grand Total 2682.61

I will also need the subtotal of the third group to be variable as well. These subtotals are currently outside the PivotTable but can be moved inside the Pivot Table if it makes it easier code wise inside the Macro.

Any help would be greatly appreciated.

Hi there,

Assume I have a data set with a column holding 3 categories (eg apple, orange, pineapple)....and a pivot table from this data set filtered for only "apple." Let's say I add some new lines of data with a new category, "banana"...if I then refresh my apple pivot table, banana will automatically be included. How can I prevent this?

Thanks!

I'm going nuts with one of my first attempts at pivot table analyses - I hope somebody can help

I want to see income by product and product category, like this:
Sales by Product

* B C D E F G H I 18 Contribution of each Product - by (Month) * * * * * * * 19 Sum of Income Year * * * * * * 20 Product Categories 2004 2005 2006 2007 2008 2009 Grand Total 21 Single sheet marketing 244,788 215,601 311,547 417,984 534,472 354,512 2,078,904 22 Brochures & Catalogues 248,823 321,185 275,085 545,247 292,151 110,339 1,792,830 23 Pre-press 158,289 191,739 142,416 195,973 140,146 48,771 877,334 24 Greetings Cards & Invitations 71,539 106,745 142,101 212,221 202,975 133,912 869,493 25 Reports / Booklets / Newsletters 104,826 79,471 119,623 240,686 179,817 92,846 817,269 26 Stationery 86,188 94,765 130,172 138,515 137,122 59,428 646,189 27 Magazine 144,150 77,845 53,581 103,684 84,642 40,276 504,177 28 Point of Sale Material 5,285 9,225 26,558 60,606 93,227 54,517 249,417 29 Stickers / Labels 15,784 20,784 38,729 29,323 32,913 26,914 164,446 30 Vouchers / tickets 18,541 13,961 9,112 11,207 7,599 102,601 163,022 31 Menus 28,613 39,161 18,227 13,681 13,545 18,020 131,246 32 Content services 12,700 13,925 12,925 15,275 14,075 1,150 70,050 33 Custom / large format 1,500 2,061 3,130 3,052 1,394 738 11,875 34 Tear Sheet * * * * 3,874 5,534 9,408 35 Grand Total 1,141,024 1,186,466 1,283,208 1,987,453 1,737,952 1,049,558 8,385,661



But
I have invoice data that I have tried to categorize into products and product groups, based on keywords in the invoice description. Example below:

* A B C D E F 1 Invoice Date Year Month Income Products Product Categories 2 26 Jul 05 2005 7 200 Menus,Flyers / Leaflets Menus,Single sheet marketing 3 26 Aug 04 2004 8 917 Letterheads,Compliment Slips Stationery 4 05 Jan 05 2005 1 668 Letterheads,Compliment Slips Stationery 5 15 Jan 07 2007 1 329 Invitations,Posters Greetings Cards & Invitations,Single sheet marketing 6 28 Oct 08 2008 10 408.9 Letterheads,Compliment slips Stationery 7 14 Nov 08 2008 11 417.13 Stickers,Signs Stickers / Labels,Custom / large format 8 15 Apr 04 2004 4 360 Letterheads,Business Cards Stationery 9 29 Apr 04 2004 4 295 Letterheads,Business Cards Stationery 10 13 May 04 2004 5 125 Reports,Invitations Reports / Booklets / Newsletters,Greetings Cards & Invitations 11 28 Sep 04 2004 9 204 Letterheads,Compliment Slips Stationery 12 28 Aug 07 2007 8 370.5 Business Cards,Brochure Stationery,Brochures & Catalogues 13 26 Oct 07 2007 10 192.25 Business Cards,Flyers / Leaflets Stationery,Single sheet marketing

So the problem is, I have multiple products per invoice, and also multiple (compound) product categories, like this:




So I thought it might be better to organize the product types as columns, like this:

* A B C D E G H I J K L M N 1 Invoice Date Year Month Income Products Business Cards Christmas Cards Compliment slips Invitations Letterheads Magazine Mailing Menus 2 20/07/2005 2005 7 358.38 Letterheads,Compliment slips * * Compliment slips * Letterheads * * * 3 16/11/2005 2005 11 458.26 Letterheads,Business Cards Business Cards * * * Letterheads * * * 4 07/01/2008 2008 1 1540.13 Tickets,Menus * * * * * * * Menus 5 22/07/2009 2009 7 465.75 Postcards,Invitations * * * Invitations * * * * 6 05/03/2004 2004 3 502.25 Business Cards,Mailing Business Cards * * * * * Mailing * 7 18/11/2005 2005 11 260.25 Brochure,Mailing * * * * * * Mailing * 8 09/03/2006 2006 3 568.7 Letterheads,Compliment Slips * * Compliment slips * Letterheads * * *


But using those as column headings I can't seem to get summaries - only nested figures.

What is the best way of organising my data so that I can get a table that looks like the top one? (I realise that there will be some double counting, but that is OK for now).

In case it makes a difference, I have 36k records, 50 product types and 16 product categories. Each product type only belongs to one category.

Any help is greatly appreciated as I've been at this for two days, reading as much as I can on the web, but as you can see I've not got very far!

Hi there,

I have a data set with a column holding 3 categories (eg apple, orange, pineapple)....and a pivot table from this data set filtered for only "apple." Let's say I add some new lines of data with a new category, "banana"...if I then refresh my apple pivot table, banana will automatically be included. How can I prevent this?

Thanks!