Pivot table question

P

petereddy

New Member
Joined
Feb 6, 2014
Messages
43
Hi,

I have a dataset that I made up (analogous to some confidential client data I actually have), and I need to make a table to put in a report. The dataset is at the bottom of the post, if it'd be helpful to replicate my results.

I am able to get all the calculations I want, but it'd be nice if it came out the way my company's formatting standards require.

Here's what I'm getting:

Column Labels
0-45-910-1415-1920-2425-2930-35Total Count of AgeTotal Average of Marbles
Row LabelsCount of AgeAverage of MarblesCount of AgeAverage of MarblesCount of AgeAverage of MarblesCount of AgeAverage of MarblesCount of AgeAverage of MarblesCount of AgeAverage of MarblesCount of AgeAverage of Marbles
15-191744217442
20-2456010.456010.4
25-2938230.66666738230.666667
30-3489382.87589382.875
35-39211997.5111424311806.33333
40-44212345.5212434.5412390
45-49113732212717214198513512.4
50-54114593215527116433415520
55-59317306.33333317715.66667217931.5817616.125
60-65119632418681.75218521219004.5918823.33333
Grand Total208748.8512309.8513571.6617100.83333818038.375418226.25219004.55013244.18

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

And here's more what I'm looking for:

Years in club
Age0-45-910-1415-1920-2425-2930-35
15-191
7442
20-245
6010.4
25-293
8230.666667
30-348
9382.875
35-3921
11997.511424
40-4422
12345.512434.5
45-49122
137321271714198
50-54121
145931552716433
55-59332
17306.3333317715.6666717931.5
60-651422
1963218681.751852119004.5
Grand Total20556842

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>

The main difference is that the head count and average number of marbles per club member in the respective age and years of member bands are in the same column, instead of two different columns.

I can write a macro that can change the formatting, but I'm wondering whether there's a way to adapt the pivot table properties to save me that effort.

If this isn't clear, please let me know and I'll list more details.

Thanks, in advance, for any help.

Peter

Here's the dataset
IDAgeYears in clubMarbles
AA2507859
AB32010170
AC592918885
AD40811629
AE592417333
AF501114593
AG2237786
AH612217552
AI451414375
AJ3108292
AK33311611
AL1907442
AM2125600
AN562816978
AO2106077
AP44613062
AQ45512446
AR632318973
AS2949176
AT471314021
AU49313732
AV581717052
AW602018996
AX2235928
AY45712988
AZ571917381
BA411012461
BB33010554
BC431112408
BD611919632
BE37711424
BF653018238
BG3128257
BH3209320
BI612119206
BJ532416433
BK653319771
BL652719381
BM592218513
BN511915936
BO622717661
BP581917486
BQ501615118
BR2707657
BS37211050
BT39012945
BU592017301
BV2004661
BW3007503
BX3409356



<colgroup><col style="width: 48pt;" span="4" width="64">
<tbody>


</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi Peter,

I can get the pivot table to this point, but I'm struggling to create a calculated field based on the count of ID ranges

Sheet5

*ABCDEFGHIJ
2**********
3**Years in club*******
4AgeData0-45-910-1415-1920-2425-2930-34Grand Total
515-19Count of ID1******1
6*Sum of Marbles7442******7442
720-24Count of ID5******5
8*Sum of Marbles30052******30052
925-29Count of ID3******3
10*Sum of Marbles24692******24692
1130-34Count of ID8******8
12*Sum of Marbles75063******75063
1335-39Count of ID21*****3
14*Sum of Marbles2399511424*****35419
1540-44Count of ID*22****4
16*Sum of Marbles*2469124869****49560
1745-49Count of ID122****5
18*Sum of Marbles137322543428396****67562
1950-54Count of ID**121**4
20*Sum of Marbles**145933105416433**62080
2155-59Count of ID***332*8
22*Sum of Marbles***519195314735863*140929
2360-65Count of ID***14229
24*Sum of Marbles***19632747273704238009169410
25Total Count of ID*2055684250
26Total Sum of Marbles*17497661549678581026051443077290538009662209

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:99px;"><col style="width:74px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

It might be the case you could add another column to your dataset which counts the numbers between the age ranges to create your averages.

14 - 19 = 1

20 - 24 = 5

etc.


AP
 
Last edited:
Upvote 0
...as a temporary solution you could possibly add a helper column at the and of the table

Excel Workbook
ABCDEFGHIJK
2
3Years in club
4AgeData0-45-910-1415-1920-2425-2930-34Grand TotalAverage
515-19Count of Years in club11
6Sum of Marbles744274427442
720-24Count of Years in club55
8Sum of Marbles30052300526010.4
925-29Count of Years in club33
10Sum of Marbles24692246928230.67
1130-34Count of Years in club88
12Sum of Marbles75063750639382.88
1335-39Count of Years in club213
14Sum of Marbles23995114243541911806.3
1540-44Count of Years in club224
16Sum of Marbles24691248694956012390
1745-49Count of Years in club1225
18Sum of Marbles1373225434283966756213512.4
1950-54Count of Years in club1214
20Sum of Marbles1459331054164336208015520
2155-59Count of Years in club3328
22Sum of Marbles51919531473586314092917616.1
2360-65Count of Years in club14229
24Sum of Marbles1963274727370423800916941018823.3
25Total Count of Years in club2055684250
26Total Sum of Marbles17497661549678581026051443077290538009662209
Sheet5




AP
 
Upvote 0
I don't mind doing a small calculation, I just don't want to create new rows, move values, and delete unused columns. This was helpful. Thanks, both.

Peter
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Why won't this work
Replies
4
Views
338
GWteB
GWteB
A
  • Question
please make this code more efficient i am new
2
Replies
15
Views
685
johnnyL
J
M
  • Question
VBA code to add date/time and username if any cell in same row is changed for rows 2 down.
Replies
3
Views
2K
JW00
J
M
  • Question
Average value ignoring zero and space in calculated field pivot table
Replies
0
Views
461
mart_mrexcel
M
J
  • Solved
Working in first 5 rows then doesn't complete the Last column loop
Replies
8
Views
369
johnmpc
J

Forum statistics

Threads
1,203,663
Messages
6,056,620
Members
444,878
Latest member
SoupLaura

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top