Pivot Table - count if within range - label values, or...?

[kcmuppet]

Hi

I'm struggling to create a pivot table that can reproduce the layout in the attached picture.

I've got 6 types of test scores that I want to analyse with lots of permuations, like gender, test type, first language of the pupil, etc, so I want to be able to use slicers to make it easier to view subsets of results.

I've been trying to use label filters in a pivot table to create % of pupils making each grade-band. I can see that it can produce columns of each score greater than or less than a value, but I can't work out how to get it to count within a range or sum the columns greater than or less than. I could create calculated columns in my data table, but there would be 180 of them given the number of tests, scores and re-tests that there are in the actual data, so I'd rather have the calculations done in the analysis table.

Would greatly appreciate some expert input. Thanks

 

[DILIPandey]

Hi Kcmuppet,

There is no picture in your post... instead of picture, you can type the data in your a Excel sheet as it is supposed to appear in Pivot table and copy paste here.

Also, paste a sample data as well.


Regards,
DILIPandey

 

[kcmuppet]

There is no picture in your post...

...instead of picture, you can type the data in your a Excel sheet as it is supposed to appear in Pivot table and copy paste here. Also, paste a sample data as well.

Hi, I think you need to switch on visible images in your settings to be able to view pictures:

Settings (top right)>General Settings. The tick "Show Images (including attached images and images in code)" under "Thread Display Options - Visible Post Elements:"



Some experts over at Chandoo's forums helped me solve it using a table for buckets and setting up data relationships. The problem and solution including sample spreadsheets are [U][URL="http://[QUOTE="DILIPandey, post: 4710563, member: 252009"]There is no picture in your post...[/QUOTE] [QUOTE="DILIPandey, post: 4710563, member: 252009"]...instead of picture, you can type the data in your a Excel sheet as it is supposed to appear in Pivot table and copy paste here. Also, paste a sample data as well.[/QUOTE] Hi, I think you need to switch on visible images in your settings to be able to view pictures: Settings (top right)>General Settings. The tick "Show Images (including attached images and images in [IMG] code)" under "Thread Display Options - Visible Post Elements:" Some experts over at Chandoo's forums helped me solve it using a table for buckets and setting up data relationships. The problem and solution including sample spreadsheets are here, in case it helps anyone: http://forum.chandoo.org/threads/pivot-table-count-if-within-range-label-values-or.32492/"]here[/URL][/U], in case it helps anyone:
[URL="http://forum.chandoo.org/threads/pivot-table-count-if-within-range-label-values-or.32492/"]Pivot Table - count if within range - label values, or...? | Chandoo.org Excel Forums - Become Awesome in Excel[/URL]

 

[DILIPandey]

Hi Kcmuppet,

Signatures, Avatars, Images.. all options are checked there


Regards,
DILIPandey