Using Excel PERCENTILE function in Apache POI

Question

I am using Apache POI 3.9 and I need to use the Excel formula PERCENTILE and evaluate the formula from within Java.

The problem is that PERCENTILE is not supported.

I get a org.apache.poi.ss.formula.eval.NotImplementedException: error

I have 3 possible solutions to my problem.

Write my own percentile function in java and contribute it to the Apache POI library (See http://poi.apache.org/spreadsheet/eval-devguide.html)
Take the relevant cells from the Excel sheet into java and calculate them using a function such as the one in Percentile calculation
Convert the PERCENTILE into a SMALL function such as instead of =PERCENTILE(A1:A10,95%) then =(SMALL(A1:A10,9)+SMALL(A1:A10,10))/2

At this point in time I need a speedy solution. For me, the third would be the best, but it doesn't give exactly the same results.

Does anyone have any ideas before I fallback on option 2 which is a bit messy?

Why are you ruling out writing the missing function and contributing it back? That would fix your case, and fix it for everyone else too! — Gagravarr, Oct 23 '13 at 16:21
That was why I wrote above "at this point in time I need a speedy solution..." I did spend a couple of hours looking into writing the missing function, but realized I didn't have the resources to do it properly. — gordon613, Oct 24 '13 at 10:11
If you understand the maths/stats behind the function, it really doesn't take long to write it. [This talk from 2010](http://people.apache.org/~yegor/apachecon_us2010/Evaluation_Of_Excel_Formulas_In_POI.pptx) covers how to do so — Gagravarr, Oct 24 '13 at 10:20

score 1 · Accepted Answer · edited May 23 '17 at 12:10

Thank you Gagravarr for your encouragement and also for your post Apache POI - How to register a function which was extremely helpful

I implemented the PERCENTILE function in the library in the following way:

I downloaded the source code for Apache POI 3.9
I checked for the ID number of the PERCENTILE function in src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt (or online) and it was 328
I thus added retval[328] = AggregateFunction.PERCENTILE; to the file org.apache.poi.ss.formula.eval.FunctionEval.java
I added public static final Function PERCENTILE = new Percentile(); to the file org.apache.poi.ss.formula.functions.AggregateFunction.java in the appropriate place.

I added to the same file the following code (which I based on the LargeSmall function; I had looked for the existing code/type of function which was most similar to what I wanted to do)

private static final class Percentile extends Fixed2ArgFunction {

protected Percentile() {
}

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0,
        ValueEval arg1) {
    double dn;
    try {
        ValueEval ve1 = OperandResolver.getSingleValue(arg1, srcRowIndex, srcColumnIndex);
        dn = OperandResolver.coerceValueToDouble(ve1);
    } catch (EvaluationException e1) {
        // all errors in the second arg translate to #VALUE!
        return ErrorEval.VALUE_INVALID;
    }
    if (dn < 0 || dn > 1) { // has to be percentage
        return ErrorEval.NUM_ERROR;
    }

    double result;
    try {
        double[] ds = ValueCollector.collectValues(arg0);
        int N = ds.length;
        double n = (N - 1) * dn + 1;
        if (n == 1d) {
            result = StatsLib.kthSmallest(ds, 1);
        } else if (n == N) {
            result = StatsLib.kthLargest(ds, 1);
        } else {
            int k = (int) n;
            double d = n - k;
            result = StatsLib.kthSmallest(ds, k) + d
                    * (StatsLib.kthSmallest(ds, k + 1) - StatsLib.kthSmallest(ds, k));
        }

        NumericFunction.checkValue(result);
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }

    return new NumberEval(result);
}

}

I then uploaded these two files individually and could then use the PERCENTILE function normally.

If you could, please attach this new code to the [Apache POI Bugzilla Bug Tracker](http://issues.apache.org/bugzilla/buglist.cgi?product=POI) as an enhancement with patch, so it can get included in the next release! — Gagravarr, Oct 30 '13 at 01:17

Using Excel PERCENTILE function in Apache POI

1 Answers1