Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.