Good morning/afternoon everyone,

first of all thanks to all of you for the valuable insights provided. I will be oulining here my current challenge, trying to provide as much detail as possible.


I have a dataset with scores associated to a list of clients of my company. These scores have been obtained through a survey designed to assess the riskiness of doing buisness with such clients, whereby every answer has a score associated with it. The higher the sum of the scores obtained by each client in each question, the higher the risk profile. Such scores range from 0 (the minimum) to several thousand.

Data quality:

The data I have is not normally distributed and does not fit a normal distribution even if standardized using Zscore and the NORM.DIST function in Excel. Smarter people than me have found distributions that could fit the data (ie: Gamma, Student T, etc.) but these distributions do not transfer over to the dataset of a different time period. That is, the distribution of the data changes every year a survey is taken and I would like to avoid trying to figure out what distribution fits the data everytime a new survey is done.

What I would like to do:

I am interested in creating a way to compare the risk scores associated with each client to come up with a way of deciding what qualified to be high risk and what is low risk. Ideally, I would love to have 5 categorizations: Very High, High, Medium, Low, Very Low risk. I need to devise a way to say that a certain score is high as opposed to low or medium.

What I ruled out:

I ruled out using the arythmectic mean and standard deviation. The reason is that the majority of clients has relatively low scores (0-400) and few have scores in the thousands. I fear that the mean would understate/overstate the mean risk score, being heavily affected by outliers. I need a more robust measure, so I was thinking about using the median instead.

My question:

How do you suggest I go about defining the buckets that I can associate with each of my five desired risk categories (Very High, High, Medium, Low, Very Low risk)? I know I could do it with percentiles relatively simply but that solution is not that appealing to me: it would make somewhat arbitrary to say, for instance, that the top 20 percentiles are very high risk, because it would most likely have a huge gap in between the min and max values of such bucket, whereas this would not be the case with the bottom 20 percentile, since lower score values are much more common.

A note:

I would really like to avoid any solutions that involve attributing a distribution to the data, since that will change and aside from the Gaussian one, most have hard to define parameters, at least in the absense of a statistical software more evolved than Excel.

Thank you all very much. Bernardo

  • $\begingroup$ the data is univariate. When you plot it, does the histogram looks unimodal? $\endgroup$ – user603 Jan 28 '14 at 15:10
  • 1
    $\begingroup$ I'm not positive I understand what you mean. The thing is that the data will vary massively year on year and survey to survey because. The distributions are absolutely random, they really are. But if you are asking if there is a single mode, in most cases there is. It's just that the value is very different survey to surbey, year on year. $\endgroup$ – Bernardo Jan 28 '14 at 15:15
  • $\begingroup$ I've got histograms, I'd be happy to share them but the website does not allow me to upload pics... $\endgroup$ – Bernardo Jan 28 '14 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.