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.
