Reducing 4 functions to 1

G’day Excel Forum

I’ve been playing around with some simple statistics for part of my postgrad studies (both honours and pilot study for masters). For one project I need to determine how well a new calculated value fits primary training data as the new calculated value will be given a score based on this. For the other I need to score spatial data values with a ranking based on where in the normal distribution it fits (in the attached example from 1 to 5 (quintiles) however in other cases it may be deciles (1 to 10) or percentiles (1 to 100)).

The best solution I have been able to find so far is to generate a ‘Boundary Array’, using excels built in percentile function, containing the values that correspond to various percentiles of the data and then classifying the data by which boundary range it fits into best (see attached spreadsheet).
Using a conditional max function to lookup the class required in a separate table entitled the boundary array

I was wondering if anyone out there can see a way of achieving this exact same result without the need for the boundary array table but rather a single function that classifies the data into a given number of classes based on the distribution of the data.

If anyone has any thoughts let us know

Regards
Matt