Determining percentage of students between certain grades

Question

I have an Excel spreadsheet with the following data:

#-----------------------------------------------------------------------------------------------------------------------------------#
#    Student    #   KS2 Grade   #  Target   #   Expected 1  #   Expected 2  #   Expected 3  #   FSM Status  #   Gifted & Talented   #
#-----------------------------------------------------------------------------------------------------------------------------------#
#     User 1    #       4       #     6     #        7      #       5       #       6       #       Y       #           N           #
#     User 2    #       3       #     5     #        5      #       4       #       4       #       N       #           N           #
#     User 3    #       5       #     6     #        6      #       6       #       7       #       N       #           N           #
#     User 4    #       4       #     6     #        5      #       6       #       6       #       N       #           Y           #
#     User 5    #       5       #     7     #        7      #       6       #       7       #       N       #           N           #
#     User 6    #       3       #     4     #        4      #       4       #       4       #       N       #           N           #
#     User 7    #       3       #     4     #        5      #       3       #       4       #       Y       #           Y           #
#-----------------------------------------------------------------------------------------------------------------------------------#

What I'd like to do is determine the percentage of students with certain levels, i.e. a range of levels.

For instance, in the data above, I'd like to determine the % of all students that have a Target level of 5 - 7.

I'd then like to also expand the formula to specify % of Gifted & Talented students with a Target level of 5 - 7.

Is this possible in Excel? If so, where do I start?

---

UPDATE: I've attempted my own formula.

I've created three formulas in separate cells:

=COUNT('Student Data'!AB2:AB19) <-- count the number of cells containing values.

=SUMPRODUCT(('Student Data'!AB2:AB19 >= 6)*('Student Data'!AB2:AB19<=7)) <-- work out how many of the values in that data range are between levels 6 and 7.

=SUM(E25/D25) <-- work out the percentage of students from SUMPRODUCT formula.

This appears to work, however... I'm not sure how to amend these formulas for, for example, Gifted & Talented students.

I'd then like to also expand the formula to specify % of Gifted & Talented students with a Target level of 5 - 7.

Answer 1

I would take a look here.

Just apply PercentRank, and it should get you what you want.

Update: A more full answer is that you need to use PercentRank to find out what what the percent is at 5, and what percent are at 7. Then, depending on what you mean by 5-7 (inclusive both assume from here on, and also inclusive on the percent) you can do the following:

=PERCENTRANK.INC(A1:A7,7) Gets percentile of 7 scores.
=PERCENTRANK.INC(A1:A7,5-1) Gets the percentile of 4 or lower scores (this assumes that there is nothing between 4 and 5).

Subtract the first from the second.

To make it work for the gifted and talented, you really should just create a separate list with just those students (pivot table or similar if you want to get fancy, or just sort what you have now and be careful with the ranges).

Answer 2

Yes. it is possible in excel. and although i can give an answer i would recommend you to figure it out yourself for sake of learning....

Hint: put the upper and lower values(5 & 7) in 2 cells and construct a formula based on that... Also read this page: http://office.microsoft.com/en-us/excel-help/count-numbers-greater-than-or-less-than-a-number-HP003056117.aspx

its quite simple..

if you still cant figure it out, i'll write the formula for you.