Excel PERCENTRANK.INC in Javascript

Question

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.

https://support.microsoft.com/en-us/office/percentrank-inc-function-149592c9-00c0-49ba-86c1-c1f45b80463a

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); 
 
});

user_4361999 · Answer

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.

user_609235 · Answer

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

Excel PERCENTRANK.INC in Javascript

2 Answers