0
269
VisualizaçõesExcel PERCENTRANK.INC in Javascript
Is there an exact way to clone the PERCENTRANK.INC function of Excel in Javascript ? I wrote my own and also tried to use the following libraries - the results don't always match.
https://github.com/formulajs/formulajs
https://github.com/malcolmvr/percentile-rank
FormulaJS comes close to Excel's output but it still has differences. In the table below the 'RAW Data' is the original array, the second column is values which I am trying to compare with the data in the first column using the PERCENTRANK.INC function.
Is there a way to get the exact results as in Excel for this function. I am also sharing my Node code to experiment with the function.
const formularjs = require('@formulajs/formulajs');
let array =[71,13,23,32,45,99,103,71,43,11,91,21,45,45,89,66,41,29,66,63];
let compare_array =[11,19,17,31,32,43,71,63,35,13,73,74,81,100,13,41,31,29,31,33];
compare_array.forEach(function(n){
let pr = formularjs.PERCENTRANKINC(array,n,3);
console.log(pr);
});
2 Respostas
Responde à pergunta0
I managed to find the discrepancy, using your code from
https://github.com/malcolmvr/percentile-rank/blob/master/lib/index.js
I rewrote it in VB, with a tweak to recreate the issue.
Sub Button1_Click()
For i = 1 To 20
Cells(i, 4) = prc(Cells(i, 2), True) / 19
Next i
For i = 1 To 20
If Cells(i, 4) = 0 Then Cells(i, 4) = ipp(Cells(i, 2))
Next i
End Sub
Function prc(v, j)
For i = 1 To 20
If v = Cells(i, 1) Then
prc = i - 1
If j Then i = 20 ' if j is true then return first value
End If
Next i
End Function
Function ipp(v)
For i = 1 To 19
If v > Cells(i, 1) And v < Cells(i + 1, 1) Then
x1 = Cells(i, 1)
x2 = Cells(i + 1, 1)
y1 = prc(x1, False) / 19
y2 = prc(x2, False) / 19
ipp = ((x2 - v) * y1 - (x1 - v) * y2) / (x2 - x1)
End If
Next i
End Function
Screenshot
The first column is the sorted raw data - your function doesn't work unless this is the case. The second is the test values, the third is Excel's PERCENTRANK.INC, and the fourth is my function (to 3dp, not 3sf).
The issue comes from data with multiple entries, for example 71 appears twice in the raw data.
When direct matches are being found, the first value found is returned. When the data is being interpolated, the last value is returned.
This is addressed in my code using boolean flag j to determine which value (first or last) is to be returned.
0
This answer is based on @JMP's answer above but a node/JS version.
The below is a rewritten version of 'percentile-rank' library.
const percentRank = (arr, value, bool) => {
arr.sort(function(a, b) {
return a - b;
});
let percValue;
for (let i = 0; i < arr.length; i++) {
if (arr[i] === value) {
percValue = i / (arr.length - 1);;
if (bool) return percValue;
}
}
return percValue;
// calculate value using linear interpolation
let x1, x2, y1, y2;
for (let i = 0; i < arr.length - 1; i++) {
if (arr[i] < value && value < arr[i + 1]) {
x1 = arr[i];
x2 = arr[i + 1];
y1 = percentRank(arr, x1, false);
y2 = percentRank(arr, x2, false);
return (((x2 - value) * y1 + (value - x1) * y2)) / (x2 - x1);
}
}
throw new Error('Out of bounds');
};
In the below code we call the newly written function.
let array =[71,13,23,32,45,99,103,71,43,11,91,21,45,45,89,66,41,29,66,63];
let compare_array =[11,19,17,31,32,43,71,63,35,13,73,74,81,100,13,41,31,29,31,33];
compare_array.forEach(function(n){
var array1 =[];
array.forEach(function(a){
if(!isNaN(a) ) array1.push(a);
})
let pr = percentRank(array1,n,true);
console.log(pr.toFixed(3));
});
The answers match what we get in the Excel Percentrank.inc