Solved

Counting number of instances of different scores in a filtered list

Posted on 2014-11-23
8
115 Views
Last Modified: 2014-11-23
I need to produce some graphs showing the distribution of scores for different groups.  
There are about 2000 people.  Each person is scored on a scale 1 to 6.  There are about 10 different groups.  
I want to produce a graph for each group showing the distribution of scores in percentages.

If I use the COUNTIF function it counts all the items, whether filtered or not, so this doesn't work.  At the moment the only workaround I can think of is to sort the list by group then copy the data for each group to a different worksheet.  

Any suggestions how I can do this more elegantly please?
I have Excel 2010
0
Comment
Question by:colin_thames
8 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40460914
Can you attach a workbook please?
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 150 total points
ID: 40460939
Hello,

data analysis like this can typically be done with pivot tables and charts. The data needs to be in a contiguous table. For your example you would need a column for Group, Person and Score.

Pivot tables and charts can be built with just a few clicks and filtered with slicers or drop-downs in the chart or pivot table. In many scenarios no formulas are required.

See attached for an example.

cheers, teylyn
PivotExample.xlsx
0
 

Author Comment

by:colin_thames
ID: 40460960
Thanks Guys
Teylyn, I wondered if someone would suggest pivot tables but it's new to me and I have to get a solution tonight so not much time to learn.  I've removed names and changed class names but this is the structure.  I want to create two graphs.
Martin, I attach an example.
I'm aiming to create two graphs, one for Effort and the other for Knowledge, that will update each time I filter by another class.  Is that possible?
Example.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40460974
I suggest two things:

1. You shouldn't "filter" unless you have to. You are right, COUNTIF doesn't use filters, and I don't think that SUBTOTAL which does is what you want. Therefore, I would suggest having the class number that you want to focus on in a cell on it's own, and therefore the formulas (and the graph titles) can use that cell.

2. Instead of using COUNTIF, use COUNTIFS. Almost exactly the same, but you can filter by multiple criteria, e.g.

COUNTIFS(B:B, "A", A:A, "Orange")
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40460977
And you can still set up a dropdown list for the class selection cell, so you don't have to type it in and run the risk of misspelling,
0
 

Author Comment

by:colin_thames
ID: 40460989
Really sorry, Phillip, I'm not sure if I understand what you mean by "having the class number that you want to focus on in a cell on it's own"

Could you show what you mean on the example sheet?
0
 
LVL 81

Accepted Solution

by:
byundt earned 350 total points
ID: 40461022
I urge you to follow teylyn's suggestion of using a PivotTable & PivotChart. Doing so really is quite simple, and you'll be kicking yourself for avoiding it once you learn how.

1. Remove AutoFilter by clicking Data...Filter menu item
2. Create a Table by selecting cell A1 and then using Insert...Table menu item. Click "OK" to close the "Create Table" dialog.
3. Select cell A1 and then Create a PivotTable using the Insert...PivotTable menu item
4. In the first step of the PivotTable wizard, choose to put the PivotTable on the same worksheet, say in cell G1. Click "OK" to close the dialog.
5. You will now be presented with a PivotTable Fields dialog. Drag Class into the Filters box, Knowledge into the Columns box, and Knowledge into the Values box.
6. In cell H1 to the right of the Class label in the PivotTable, choose one of the class colors then click "OK". Note how your counts change.
7. Select any cell in the PivotTable, then go to the Analyze menu that now appears and choose PivotChart. A PivotChart will now appear on the worksheet when you click "OK".

You can filter the data either with the PivotTable or the PivotChart and the histogram will automatically update.
ExampleQ28567992.xlsx
0
 

Author Closing Comment

by:colin_thames
ID: 40461121
Many thanks guys, especially byundt for the worked example of the pivot table.  I baulked at trying it initially but when I eventually plucked up the courage it worked pretty much as you said.  There wasn't any sign of a Wizard but the rest of the instructions were excellent.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now