Hi everyone,
I'm stuck with a problem that I think might be very easy for people who know how - I'd be really grateful for some help.
I have a national dataset (they're KS2 education results in the UK for anyone who's curious).
In column J I have the score, and in column K, the number of students nationally who achieved that score.
I need to calculate where the grade boundaries fall to allocate a "band" to each incoming student. We'll have six bands.
If every score (all 646,171 of them) were written out in a column, I know how to use percentile.inc to get the boundaries.
What I can't work out is how to create an "array" where every number in column J "appears" the number of times specified in column K.
All ideas would be very gratefully received!
Thank you for reading!
Hello,
A simple method for one such array is
=IF(SEQUENCE(K8),J8)
The problem is how to combine it with several rows. I would like to try using Power Query, if you are able to upload a sample file it would be great.
Br,
Anders
Maybe I overlooked it but hopefully the SEQUENCE() function works with the OP's Excel version ???
I believe I found the data set you referred to on a UK Government site and extracted a bit of data that seems to relate to the 646,171 scores you mentioned.
The attached file contains that data extract and a Power Query solution that lists all 647K scores. Though, the latter is not loaded back to Excel as the file would become quite large. But you can load it yourself and apply the PERCENTILE.INC function to the 'expanded' scaled_scores column. However, I wonder why you can't use the data table as it is. Column L (pt_cumulative) contains the rounded percentile boundaries for each of the scores in column J. I just added some extra columns to calculate the exact percentages (columns M:N).
Hopefully, you will find this useful.
