What would be the mathematical/SIMS equivalent of this excel formula? =PERCENTRANK()

[And1P]

I need to create a Capita SIMS assessment manager sheet that does the same calculation as this Excel =PERCENTRANK() function.

Its full components in Excel are:

=PERCENTRANK(Array,X,[significance])

An example Array could be - 13,17,27,33,42,56,61,69,74,83,95,98

I am looking to find the percentage rank of each of the values in that array (x) - they are test scores.

The significance does not matter as much, as 4-6 decimal places would be handy to sort values that are close to each other out in a larger array.

What would be the equation for this function? I've tried to look up this function but cannot find the maths behind it or how to apply it within a SIMS assessment manager sheet.

 

Thank you in advance for your help!

[Marci]

Soneone's dropped an answer over on StackExchange for you (in case you don't have notifications turned on for over there) - https://superuser.com/questions/1386544/what-would-be-the-mathematical-equivalent-of-this-excel-formula-percentrank

[BrownShoes]

I think the closest you could get to that would be to use Position - Data Review using Formula - which is similar to Rank in Excel. Anything more complex is difficult in Assessment - and I can't immediately see a way you could use a series of If Then Else statements to achieve this either. If there is a mathematical equivalent equation, it wouldn't be usable in AM - afraid I don't understand the post in StackExchange referred to above.

[HughR]

I am looking to do something very similar. Currently I am using the 'POSITION' formula to calculate the rank of each pupil's test score compared to the rest of the cohort. It works fine: pupils with the same test score are given the same rank, and the next lowest score is assigned the next rank depending upon how many pupils achieved the same score i.e. 7 pupils score 39 at a rank position of 44, the next pupil with a slightly higher score of 40 will have a rank score 7 places lower i.e. rank 37.

 

My difficulty is that I'm trying to work out a dynamic quintile (increments of 20%) percentage score based on the test cohort (let's say 176 for the sake of argument, in which case the boundary for 20% would be 35.2 etc) rather than 100, so I need some way for SIMS to extract the 176 test cohort number to use in the quintile calculation. I can't see any way to automate this, so I'm having to work this out manually then set the quintile thresholds manually in the NESTED IF THEN ELSE formula. This is fine up to a point - till late test scores come in and change the ranking scores, hence the need for dynamic quintile calculations. I can't see any way of pulling out this cohort number in an assessment marksheet (though it will show it in the SUMMARY).

 

Does anyone know how to extract a representative Cohort number for use in formula calculations? The lowest rank (lowest test score) should reflect the cohort count.