# My first pivot table

#### petereddy

##### New Member
Hi,

I've never created a pivot table before, so I'm not sure whether this is the route to go. Please let me know if I'm barking up the wrong tree.

Suppose I have 149 members in a marble club. You're given the details of each member at the bottom (it's long, so I figured I'd put it at the bottom).

How would you propose building the following distribution chart?

 Males Females Age Number Average number of marbles Number Average number of marbles Under 10 5 527 6 605 10-14 7 413 5 319 15-19 4 677 6 470 20-24 9 647 10 422 25-29 5 446 4 412 30-34 7 535 6 352 35-39 8 493 6 429 40-44 6 317 10 419 45-49 6 649 5 705 50-54 6 693 3 400 55-59 5 520 2 439 60 and over 9 609 9 697 Total 77 6526 72 5669

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

I think the distribution table is pretty straight forward, but, to be perfectly clear, the averages are for the specific sex and specific age range. Is this a pivot table task? Or does this task just require brute force and perhaps some clever Excel formulas? To create the distribution above, I used the countifs and sumifs functions, which sped it up, but I imagine pivot tables should be able to do this.

Hopefully this is obvious, but I have no interest in marbles. I need to do a distribution of salaries, ages and group divisions for a client report, but I figured the above example is sufficiently analogous for me adapt it to the table I'm putting in the report.

Anyway, the data set's below. Have at it.

Thanks for considering this.

Peter

 Member ID Age Sex Marbles AA 61 M 824 AB 41 F 239 AC 63 M 76 AD 21 M 831 AE 34 F 213 AF 47 F 837 AG 21 F 3 AH 31 F 151 AI 35 F 170 AJ 36 F 332 AK 19 M 825 AL 26 F 76 AM 44 F 789 AN 6 M 274 AO 33 M 859 AP 40 F 455 AQ 49 F 588 AR 29 M 921 AS 20 M 474 AT 16 M 329 AU 37 M 204 AV 55 M 285 AW 36 M 852 AX 17 F 871 AY 30 F 1 AZ 55 M 394 BA 6 M 636 BB 11 F 766 BC 29 M 177 BD 15 M 693 BE 13 M 373 BF 17 F 366 BG 5 M 613 BH 60 M 848 BI 17 F 375 BJ 46 M 917 BK 55 F 866 BL 12 M 849 BM 36 M 191 BN 14 M 948 BO 26 F 707 BP 47 F 282 BQ 20 F 204 BR 54 M 754 BS 9 F 939 BT 20 M 576 BU 62 F 301 BV 36 M 977 BW 36 M 323 BX 36 F 744 BY 37 F 340 BZ 37 F 196 CA 34 F 786 CB 55 M 837 CC 53 F 138 CD 24 F 408 CE 44 M 161 CF 48 F 833 CG 43 F 637 CH 62 F 859 CI 25 F 1 CJ 63 F 949 CK 46 M 876 CL 23 M 477 CM 19 F 770 CN 63 M 513 CO 30 M 268 CP 7 F 613 CQ 17 M 859 CR 65 M 909 CS 19 F 171 CT 52 F 606 CU 42 M 87 CV 55 M 140 CW 21 F 97 CX 60 M 371 CY 64 F 911 CZ 40 M 228 DA 13 F 229 DB 31 M 14 DC 44 F 564 DD 22 M 444 DE 21 M 661 DF 51 M 804 DG 26 M 422 DH 49 M 880 DI 38 M 340 DJ 22 M 853 DK 50 M 514 DL 62 F 588 DM 14 F 293 DN 34 M 127 DO 20 F 364 DP 44 M 217 DQ 40 F 133 DR 23 F 720 DS 13 M 104 DT 30 F 709 DU 36 M 977 DV 43 F 972 DW 15 F 265 DX 53 M 909 DY 52 F 457 DZ 44 M 273 EA 8 F 160 EB 12 M 18 EC 47 F 986 ED 33 M 918 EE 30 F 251 EF 55 M 944 EG 61 M 272 EH 48 M 778 EI 6 M 458 EJ 10 M 511 EK 38 M 77 EL 6 M 655 EM 65 F 795 EN 32 M 604 EO 22 F 260 EP 29 M 146 EQ 12 F 5 ER 40 F 219 ES 55 F 12 ET 24 M 688 EU 10 F 302 EV 64 F 857 EW 44 F 51 EX 49 M 54 EY 24 F 989 EZ 64 M 994 FA 5 F 741 FB 5 F 220 FC 7 F 954 FD 54 M 520 FE 62 F 104 FF 24 M 818 FG 60 F 908 FH 23 F 658 FI 21 F 519 FJ 46 M 391 FK 29 M 565 FL 64 M 671 FM 52 M 656 FN 41 M 937 FO 41 F 127 FP 26 F 864 FQ 10 M 88 FR 30 M 958 FS 38 F 789

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

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### petereddy

##### New Member
Thanks, Andrew. Just what I needed.

Peter

Replies
3
Views
108
Replies
5
Views
109
Replies
4
Views
275
Replies
4
Views
127
Replies
2
Views
87

1,190,946
Messages
5,983,803
Members
439,860
Latest member
AlunM

### 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.

### Which adblocker are you using?

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

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