Count number of cells that are between certain percentages
I have attached a spreadsheet.
I need a formula to count the number of students from each school, in each grade, that are between 90-100%, 80-89%, 70-79%, and below 70%
So for example, there are 9 students in Grade 1 at the community school that are between 90-100% and 7 that are at 70-79%.
Please help, I have 25 schools and it's horrendous to count them.
Thank you so much
| Attachment | Size |
|---|---|
| November 2014 Student Attendance by Percent - help.xlsx | 29.92 KB |
In your example spreadsheet I
In your example spreadsheet I have added the below formula into column D and labled the column RESULT
=IF(C2="","",IF(C2>=90%,"A",IF(C2<70%,"D",IF(AND(C2>=80%,C2<=89%),"B","C"))))
for ease I have just used generic A,B,C & D to represent your different percentile groups. You can change these to anything you wish in the above.
Next all you need to do is insert a pivot table, put the School in the Filter section, Grade in the Row labels and RESULT in the Values (NB if it does not state count of RESULT, you need to left click on Result, select "value field settings" and choose count)
This should give you everything you need
Regards
Mark
YESSSS!!! Except I had to put
YESSSS!!! Except I had to put the school and grade in filters, results in the rows, and count of percent in values and it gave me the breakdown. Thank you sooooooo much, you made my life a million times easier :)
I need it broken down further
I know need it broken down even further and can't figure out how to do that:
95-100%
90-94%
80-89%
70-79%
60-69%
50-59%
Below 50%
Can you modify the formula for me please?
Hey Krystal. What are the
Hey Krystal.
What are the grades you're looking to assign to the new percentages?
Regards
Mark
Hey Mark, Each grade needs to
Hey Mark,
Each grade needs to be broken down by the new percentage categories. Thanks!! Krystal
Hey
Hey Mark,
=IF(C2="","",IF(C2>=90%,"A",IF(C2<70%,"D",IF(AND(C2>=80%,C2<=89%),"B","C"))))
I need this formula modified to count the number of students 95%-100%, 90-94%, 80-89%, 70-79%, 60-69%, 50-59%, and below 50%
Can you please help? I'm so confused yikes thanks!
I posted this in another one
I posted this in another one of your threads but I don't think you saw it:
100-95
=COUNTIFS(C:C,">=95%",C:C,"<=100%")
94-90
=COUNTIFS(C:C,">=90%",C:C,"<=94%")
89-80
=COUNTIFS(C:C,">=80%",C:C,"<=89%")
79-70
=COUNTIFS(C:C,">=70%",C:C,"<=79%")
69-60
=COUNTIFS(C:C,">=60%",C:C,"<=69%")
59-50
=COUNTIFS(C:C,">=50%",C:C,"<=59%")
Below 50
=COUNTIFS(C:C,">=0%",C:C,"<50%")