Mixing
Rescalling a range of numbers depending on the interval they belong to
I have the following conversion table:
and I need to convert the values of the second column to the values in the first column, as continuous variables.
Currently, I am trying to write a function that accepts a value from the second column, check its validity and then uses the following conversion formula for the data transformation:
The code currently looks like this:
Public Function ReScale (input As Double)
'Max and Min values of the old scale.
Dim MinOld As Double: MinOld = 2
Dim MaxOld As Double: MaxOld = 6
'Test input value validity.
If input > MaxOld Or input < MinOld Then
ReScale = CVErr(xlErrNA)
End
Dim MinNew As Double
Dim MaxNew As Double
'Find in which interval the input value belongs.
If input >= 5.5 Then
MinOld = 5.5
MaxOld = 6.0
MinNew = 85
MaxNew = 100
ElseIf input >= 4.50 And input <= 5.49 Then
MinOld = 4.50
MaxOld = 5.49
MinNew = 65
MaxNew = 84
...` <--------------------- Question?
'Transform the old data to the new scale.
ReScale = ( (MaxNew - MinNew) / (MaxOld - MinOld) ) * (input - MaxOld) + MaxNew
End Function
My question is:
Should I search for MaxNew, MinNew, MaxOld and MinOld depending on the variable value or should I use the absolute minimum and maximum?
Is there an easier way of doing this?
excel vba
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
|
show 2 more comments
I have the following conversion table:
and I need to convert the values of the second column to the values in the first column, as continuous variables.
Currently, I am trying to write a function that accepts a value from the second column, check its validity and then uses the following conversion formula for the data transformation:
The code currently looks like this:
Public Function ReScale (input As Double)
'Max and Min values of the old scale.
Dim MinOld As Double: MinOld = 2
Dim MaxOld As Double: MaxOld = 6
'Test input value validity.
If input > MaxOld Or input < MinOld Then
ReScale = CVErr(xlErrNA)
End
Dim MinNew As Double
Dim MaxNew As Double
'Find in which interval the input value belongs.
If input >= 5.5 Then
MinOld = 5.5
MaxOld = 6.0
MinNew = 85
MaxNew = 100
ElseIf input >= 4.50 And input <= 5.49 Then
MinOld = 4.50
MaxOld = 5.49
MinNew = 65
MaxNew = 84
...` <--------------------- Question?
'Transform the old data to the new scale.
ReScale = ( (MaxNew - MinNew) / (MaxOld - MinOld) ) * (input - MaxOld) + MaxNew
End Function
My question is:
Should I search for MaxNew, MinNew, MaxOld and MinOld depending on the variable value or should I use the absolute minimum and maximum?
Is there an easier way of doing this?
excel vba
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
1
Why do you need the<=check in thoseElseIfstatement? (Unless you want to handle 5.495 as a special case?)
– Chronocidal
Nov 21 '18 at 16:16
1
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
@Chronocidal oh, yes! You are right, it should just be< 5.50, instead of<=5.49, right?
– Ziezi
Nov 21 '18 at 16:38
@AhmedAU Exactly, the last interval's length is1. Thanks!
– Ziezi
Nov 21 '18 at 16:39
2
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such asy = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)
– Chronocidal
Nov 21 '18 at 16:46
|
show 2 more comments
I have the following conversion table:
and I need to convert the values of the second column to the values in the first column, as continuous variables.
Currently, I am trying to write a function that accepts a value from the second column, check its validity and then uses the following conversion formula for the data transformation:
The code currently looks like this:
Public Function ReScale (input As Double)
'Max and Min values of the old scale.
Dim MinOld As Double: MinOld = 2
Dim MaxOld As Double: MaxOld = 6
'Test input value validity.
If input > MaxOld Or input < MinOld Then
ReScale = CVErr(xlErrNA)
End
Dim MinNew As Double
Dim MaxNew As Double
'Find in which interval the input value belongs.
If input >= 5.5 Then
MinOld = 5.5
MaxOld = 6.0
MinNew = 85
MaxNew = 100
ElseIf input >= 4.50 And input <= 5.49 Then
MinOld = 4.50
MaxOld = 5.49
MinNew = 65
MaxNew = 84
...` <--------------------- Question?
'Transform the old data to the new scale.
ReScale = ( (MaxNew - MinNew) / (MaxOld - MinOld) ) * (input - MaxOld) + MaxNew
End Function
My question is:
Should I search for MaxNew, MinNew, MaxOld and MinOld depending on the variable value or should I use the absolute minimum and maximum?
Is there an easier way of doing this?
excel vba
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
I have the following conversion table:
and I need to convert the values of the second column to the values in the first column, as continuous variables.
Currently, I am trying to write a function that accepts a value from the second column, check its validity and then uses the following conversion formula for the data transformation:
The code currently looks like this:
Public Function ReScale (input As Double)
'Max and Min values of the old scale.
Dim MinOld As Double: MinOld = 2
Dim MaxOld As Double: MaxOld = 6
'Test input value validity.
If input > MaxOld Or input < MinOld Then
ReScale = CVErr(xlErrNA)
End
Dim MinNew As Double
Dim MaxNew As Double
'Find in which interval the input value belongs.
If input >= 5.5 Then
MinOld = 5.5
MaxOld = 6.0
MinNew = 85
MaxNew = 100
ElseIf input >= 4.50 And input <= 5.49 Then
MinOld = 4.50
MaxOld = 5.49
MinNew = 65
MaxNew = 84
...` <--------------------- Question?
'Transform the old data to the new scale.
ReScale = ( (MaxNew - MinNew) / (MaxOld - MinOld) ) * (input - MaxOld) + MaxNew
End Function
My question is:
Should I search for MaxNew, MinNew, MaxOld and MinOld depending on the variable value or should I use the absolute minimum and maximum?
Is there an easier way of doing this?
excel vba
excel vba
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
edited Nov 21 '18 at 16:09
Ziezi
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
asked Nov 21 '18 at 15:59
ZieziZiezi
4,87832638
4,87832638
1
Why do you need the<=check in thoseElseIfstatement? (Unless you want to handle 5.495 as a special case?)
– Chronocidal
Nov 21 '18 at 16:16
1
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
@Chronocidal oh, yes! You are right, it should just be< 5.50, instead of<=5.49, right?
– Ziezi
Nov 21 '18 at 16:38
@AhmedAU Exactly, the last interval's length is1. Thanks!
– Ziezi
Nov 21 '18 at 16:39
2
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such asy = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)
– Chronocidal
Nov 21 '18 at 16:46
|
show 2 more comments
1
Why do you need the<=check in thoseElseIfstatement? (Unless you want to handle 5.495 as a special case?)
– Chronocidal
Nov 21 '18 at 16:16
1
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
@Chronocidal oh, yes! You are right, it should just be< 5.50, instead of<=5.49, right?
– Ziezi
Nov 21 '18 at 16:38
@AhmedAU Exactly, the last interval's length is1. Thanks!
– Ziezi
Nov 21 '18 at 16:39
2
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such asy = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)
– Chronocidal
Nov 21 '18 at 16:46
1
1
Why do you need the
<= check in those ElseIf statement? (Unless you want to handle 5.495 as a special case?)– Chronocidal
Nov 21 '18 at 16:16
Why do you need the
<= check in those ElseIf statement? (Unless you want to handle 5.495 as a special case?)– Chronocidal
Nov 21 '18 at 16:16
1
1
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
@Chronocidal oh, yes! You are right, it should just be
< 5.50, instead of <=5.49, right?– Ziezi
Nov 21 '18 at 16:38
@Chronocidal oh, yes! You are right, it should just be
< 5.50, instead of <=5.49, right?– Ziezi
Nov 21 '18 at 16:38
@AhmedAU Exactly, the last interval's length is
1. Thanks!– Ziezi
Nov 21 '18 at 16:39
@AhmedAU Exactly, the last interval's length is
1. Thanks!– Ziezi
Nov 21 '18 at 16:39
2
2
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such as
y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)– Chronocidal
Nov 21 '18 at 16:46
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such as
y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)– Chronocidal
Nov 21 '18 at 16:46
|
show 2 more comments
1 Answer
1
active
oldest
votes
This is a bit of a Frame Challenge:
At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
},
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%2fstackoverflow.com%2fquestions%2f53415950%2frescalling-a-range-of-numbers-depending-on-the-interval-they-belong-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is a bit of a Frame Challenge:
At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
add a comment |
This is a bit of a Frame Challenge:
At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
add a comment |
This is a bit of a Frame Challenge:
At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
This is a bit of a Frame Challenge:
At the moment, this grading system gives a really sharp change in the grading structure at the boundaries. For example, take a look at the following conversions:
A Grading Curve is generally called that for a reason - to give a smooth distribution and transition. If you were to drop a cubic trendline on your graph, you can almost (but not quite) get there with y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277:
(I have circled the sharp transitions, and the fact that the equation given currently goes negative at x=2)
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
answered Nov 22 '18 at 11:06
ChronocidalChronocidal
2,9031316
2,9031316
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
add a comment |
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
That is an interesting observation - the largest bump is at the fail/pass point, 3.00, i.e. the pass threshold of the scale in the second column is 3.00. Having that in mind, should the scale Curve be smooth or should it reflect the characteristics of the grading system?
– Ziezi
Nov 23 '18 at 5:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
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%2fstackoverflow.com%2fquestions%2f53415950%2frescalling-a-range-of-numbers-depending-on-the-interval-they-belong-to%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
1
Why do you need the
<=check in thoseElseIfstatement? (Unless you want to handle 5.495 as a special case?)– Chronocidal
Nov 21 '18 at 16:16
1
since the conversion table shows there is no liner relation, my opinion is to proceed with current approach only for accurate conversion.
– Ahmed AU
Nov 21 '18 at 16:30
@Chronocidal oh, yes! You are right, it should just be
< 5.50, instead of<=5.49, right?– Ziezi
Nov 21 '18 at 16:38
@AhmedAU Exactly, the last interval's length is
1. Thanks!– Ziezi
Nov 21 '18 at 16:39
2
(Also - this gives really sharp grading changes at the boundaries - from 3.2 input to 3.3 input is an output change of 1.84, from 3.3 input to 3.4 input is an output change of 1.84, from 3.4 input to 3.5 input is an output change of 2.65, from 3.5 input to 3.6 input is an output change of 1.92, from 3.6 input to 3.7 input is an output change of 1.92... You would get a smoother approximation from a cubic equation such as
y = 0.0015x^3 - 0.1115x^2 + 4.545x - 5.5277)– Chronocidal
Nov 21 '18 at 16:46