0
$\begingroup$

I'm absolutely no statistics wizard and it is my first post here, so I hope you bear with me.

I'm working in Excel on a project where I need to convert data from a number of variables into a "one figure" scoring system that gives a range of e.g. 0-10 or 0-100. The variables contain values in both percent and "normal numerical" value.

Some background: the aim of what I'm doing is to arrive at how a number of municipalities perform on a number of facets (one example is "Affordability" which is structured as a product of some underlying categories) in a scoring system of 0-10 (or preferably 0-100) across a number of different performance categories (price pr. m^2; Rental costs pr. m^2; Housing burden (N.B. Picture of the variables and data below contains the variable avg. income after tax, which is only used for calculating housing burden).

Additionally, the method I'd use needs to be applicable for only one municipality (that is, not just for comparing data points in one category across municipalities).

I looked at doing min-max normalization as well as mean normalization. The issue I'm getting at then is that you would then convert the value of one data point in a category (x) into the new range (x'). But this doesn't help me because this would convert one data point for one municipality in one category (f.x. Municipality 1s data point in the category Price pr. M^2) or one arbitrarily picked data point in one municipality across categories(F.x. I convert price pr. m^2 in Municipality 1 according to municipality 1s scores across categories).

As I'm sure it shows, I have very little (to no) idea of what I'm doing here.

The most understandable way I can explain it is to say that I need to be able to take Municipality X's scores and convert them into one score between 0-100 (or 0-10). I hope you can at least guesstimate what I really need your help for by looking at the data I've posted in the link below

Any help is greatly appreciated and I'd be happy to provide examples of other facets (could be Comfort, Housing type diversity etc.) or elaborate.

Picture with categories, municipalities and data points

$\endgroup$
  • 2
    $\begingroup$ This would probably be better on cross validated.se $\endgroup$ – Ross Millikan Dec 13 '18 at 0:03
  • $\begingroup$ I'm not positive what you're asking, but it seems like you should just be able to divide by the max then multiply by 100 (or 10). $\endgroup$ – T. Fo Dec 13 '18 at 0:41
  • $\begingroup$ @RossMillikan Thanks for your comment. I'm not sure how I'd move the thread - or do I need to repost? $\endgroup$ – NotEuclid Dec 13 '18 at 15:42
  • $\begingroup$ The simplest is to delete this and post it on cross validated. If there is not a delete button at the bottom of the question for you, flag it for moderator attention $\endgroup$ – Ross Millikan Dec 13 '18 at 15:45
  • $\begingroup$ @T.Ford Thanks for the suggestion. Doing what you suggested, gives me scores of +10 or +100 for all the municipalities. When multiplying by 100, I get: Municipality 1: 104.2536498 | Municipality 2: 101.8748233 | Municipality 3: 102.4579349 | Municipality 4: 108.3361187 | Municipality 5: 104.9041733. In principle, I could subtract 100 and then get a score from 0-10, but I would have no idea of how to justify doing so. To try and put it simply, what I'm trying to do is to arrive at a score (in this case of how a municipality scores in affordability) by looking at a number of indicators. $\endgroup$ – NotEuclid Dec 13 '18 at 16:30

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.