Producing a range of 0-100 based on multiple variables with different units (percent, numerical)
$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
statistics data-analysis descriptive-statistics data-structure
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
$endgroup$
|
show 1 more comment
$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
statistics data-analysis descriptive-statistics data-structure
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
$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
|
show 1 more comment
$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
statistics data-analysis descriptive-statistics data-structure
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
$endgroup$
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
statistics data-analysis descriptive-statistics data-structure
statistics data-analysis descriptive-statistics data-structure
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
asked Dec 12 '18 at 23:55
NotEuclidNotEuclid
11
11
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
|
show 1 more comment
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
2
2
$begingroup$
This would probably be better on cross validated.se
$endgroup$
– Ross Millikan
Dec 13 '18 at 0:03
$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$
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$
@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$
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
$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
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "69"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmath.stackexchange.com%2fquestions%2f3037414%2fproducing-a-range-of-0-100-based-on-multiple-variables-with-different-units-per%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Mathematics Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmath.stackexchange.com%2fquestions%2f3037414%2fproducing-a-range-of-0-100-based-on-multiple-variables-with-different-units-per%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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