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?
<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
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
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>
