What would be the mathematical equivalent of this excel formula? =PERCENTRANK()
I need to create a SIMS (School Information Management System) assessment sheet that does the same calculation as this Excel =PERCENTRANK() function.
Its full components in Excel are:
=PERCENTRANK(Array,X,[Significance])
An example Array could be
- 13,17,27,33,42,56,61,69,74,83,95,98
I am looking to find the percentage rank of each of the values in that array (x)
The significance does not matter as much, as 4-6 decimal places would be handy to sort values that are close to each other out in a larger array.
What would be the equation for this function? I've tried to look up this function but cannot find the math's behind it.
microsoft-excel worksheet-function
edited Dec 21 '18 at 8:39
PeterH
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
add a comment |
I need to create a SIMS (School Information Management System) assessment sheet that does the same calculation as this Excel =PERCENTRANK() function.
Its full components in Excel are:
=PERCENTRANK(Array,X,[Significance])
An example Array could be
- 13,17,27,33,42,56,61,69,74,83,95,98
I am looking to find the percentage rank of each of the values in that array (x)
The significance does not matter as much, as 4-6 decimal places would be handy to sort values that are close to each other out in a larger array.
What would be the equation for this function? I've tried to look up this function but cannot find the math's behind it.
microsoft-excel worksheet-function
edited Dec 21 '18 at 8:39
PeterH
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
add a comment |
I need to create a SIMS (School Information Management System) assessment sheet that does the same calculation as this Excel =PERCENTRANK() function.
Its full components in Excel are:
=PERCENTRANK(Array,X,[Significance])
An example Array could be
- 13,17,27,33,42,56,61,69,74,83,95,98
I am looking to find the percentage rank of each of the values in that array (x)
The significance does not matter as much, as 4-6 decimal places would be handy to sort values that are close to each other out in a larger array.
What would be the equation for this function? I've tried to look up this function but cannot find the math's behind it.
microsoft-excel worksheet-function
edited Dec 21 '18 at 8:39
PeterH
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
I need to create a SIMS (School Information Management System) assessment sheet that does the same calculation as this Excel =PERCENTRANK() function.
Its full components in Excel are:
=PERCENTRANK(Array,X,[Significance])
An example Array could be
- 13,17,27,33,42,56,61,69,74,83,95,98
I am looking to find the percentage rank of each of the values in that array (x)
The significance does not matter as much, as 4-6 decimal places would be handy to sort values that are close to each other out in a larger array.
What would be the equation for this function? I've tried to look up this function but cannot find the math's behind it.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Dec 21 '18 at 8:39
PeterH
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
edited Dec 21 '18 at 8:39
PeterH
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
edited Dec 21 '18 at 8:39
PeterH
3,49332347
edited Dec 21 '18 at 8:39
PeterH
3,49332347
edited Dec 21 '18 at 8:39
PeterH
3,49332347
3,49332347
asked Dec 21 '18 at 6:50
And1PAnd1P
62
asked Dec 21 '18 at 6:50
And1PAnd1P
62
asked Dec 21 '18 at 6:50
And1PAnd1P
62
62
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
As I understand.
If the Value is present in array, then:
Count1 = count of values in Array below than a Value
Count2 = count of values in Array above than a Value
PercentRank = Count1 / (Count1 + Count2)
If the Value is not present in array, then its PercentRank is interpolated by PercentRank values of its "neighbors" in array:
PercentRank1 = PercentRank of the minimal value Value1 in Array not less than a Value
PercentRank2 = PercentRank of the maximal value Value2 in Array not greater than a Value
PercentRank = (PercentRank1 * (Value1 - Value) + PercentRank2 * (Value - Value2)) / (Value1 - Value2)
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1386544%2fwhat-would-be-the-mathematical-equivalent-of-this-excel-formula-percentrank%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
As I understand.
If the Value is present in array, then:
Count1 = count of values in Array below than a Value
Count2 = count of values in Array above than a Value
PercentRank = Count1 / (Count1 + Count2)
If the Value is not present in array, then its PercentRank is interpolated by PercentRank values of its "neighbors" in array:
PercentRank1 = PercentRank of the minimal value Value1 in Array not less than a Value
PercentRank2 = PercentRank of the maximal value Value2 in Array not greater than a Value
PercentRank = (PercentRank1 * (Value1 - Value) + PercentRank2 * (Value - Value2)) / (Value1 - Value2)
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
add a comment |
As I understand.
If the Value is present in array, then:
Count1 = count of values in Array below than a Value
Count2 = count of values in Array above than a Value
PercentRank = Count1 / (Count1 + Count2)
If the Value is not present in array, then its PercentRank is interpolated by PercentRank values of its "neighbors" in array:
PercentRank1 = PercentRank of the minimal value Value1 in Array not less than a Value
PercentRank2 = PercentRank of the maximal value Value2 in Array not greater than a Value
PercentRank = (PercentRank1 * (Value1 - Value) + PercentRank2 * (Value - Value2)) / (Value1 - Value2)
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
add a comment |
As I understand.
If the Value is present in array, then:
Count1 = count of values in Array below than a Value
Count2 = count of values in Array above than a Value
PercentRank = Count1 / (Count1 + Count2)
If the Value is not present in array, then its PercentRank is interpolated by PercentRank values of its "neighbors" in array:
PercentRank1 = PercentRank of the minimal value Value1 in Array not less than a Value
PercentRank2 = PercentRank of the maximal value Value2 in Array not greater than a Value
PercentRank = (PercentRank1 * (Value1 - Value) + PercentRank2 * (Value - Value2)) / (Value1 - Value2)
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
As I understand.
If the Value is present in array, then:
Count1 = count of values in Array below than a Value
Count2 = count of values in Array above than a Value
PercentRank = Count1 / (Count1 + Count2)
If the Value is not present in array, then its PercentRank is interpolated by PercentRank values of its "neighbors" in array:
PercentRank1 = PercentRank of the minimal value Value1 in Array not less than a Value
PercentRank2 = PercentRank of the maximal value Value2 in Array not greater than a Value
PercentRank = (PercentRank1 * (Value1 - Value) + PercentRank2 * (Value - Value2)) / (Value1 - Value2)
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
answered Dec 21 '18 at 7:38
AkinaAkina
1,27828
1,27828
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1386544%2fwhat-would-be-the-mathematical-equivalent-of-this-excel-formula-percentrank%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
