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

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
435
Office Version
  1. 365
  2. 2010
Hi

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

Clipboard01.jpg


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
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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]
 
Last edited:
Upvote 0
Hi Kcmuppet,

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


Regards,
DILIPandey
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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