# Determining percentage of students between certain grades

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.

-
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... – tumchaaditya Oct 19 '12 at 11:18

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).

-
adding a hint: you'll need 2 PercentRank functions.. – tumchaaditya Oct 19 '12 at 11:23
Thanks for that. Could you please check out my updated question? I think I've figured out what you're suggesting but I'm having problems with the second part of my question. – dunc Oct 19 '12 at 12:12

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.

-
Thanks for that. Could you please check out my updated question? I think I've figured out what you're suggesting but I'm having problems with the second part of my question. – dunc Oct 19 '12 at 12:11