Dear Excel users
I have a list of test results, i want to set grades by percentile rankings. i worked out from my set of test results where the boundaries will be using the percentile.exc formula. Then i have to use a vlookup on this table against the test results to define the right grades for the test results
so a test result of 34 is in the 6th percentile so should be given a grade 3 , now i could do this manually, but more reliably by (vlookup, true) BUT I am sure it is possible to do this in one step, so that a formula is applied to the test result column that basically says, if the test result is in the lowest 5th percentile, give grade 2 , 6 to 27 percentile, give grade 3 ,, etc etc
Just one simple formula would be great as I have to do this 45 different sets of test data in one sitting
but I can't work out the formula,, any suggestions please? I've seen it is possible, but I should have written it down months ago
| percentile | marks for grade boundary | Grade |
| <6 | 0 | 2 |
| 6 | 34 | 3 |
| 28 | 50 | 4 |
| 50 | 64 | 5 |
| 73 | 72 | 6 |
| 95 | 92 | 7 |
I think you could use something like:
=LOOKUP(B2,PERCENTILE.INC($B$2:$B$10,{0,0.06,0.28,0.5,0.73,0.95}),{2,3,4,5,6,7})
where B2:B10 has your data set in.
thank you very much i will try that,
That works ,, brilliant, thanks so much.
You're welcome.
