Bug 52818 - Implementation of the RANK function
Summary: Implementation of the RANK function
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-dev
Hardware: PC Windows Vista
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-03-04 09:41 UTC by rubin wang
Modified: 2012-03-05 12:14 UTC (History)
0 users

implemenation of RANK (2.89 KB, text/x-java)
2012-03-04 09:41 UTC, rubin wang
FormulaEvalTestData.xls (tests for RANK added) (163.50 KB, application/vnd.ms-excel)
2012-03-05 08:26 UTC, rubin wang
Rank.java (4.56 KB, text/x-java)
2012-03-05 08:37 UTC, rubin wang
FunctionEval.java (8.14 KB, text/plain)
2012-03-05 08:45 UTC, rubin wang

Note You need to log in before you can comment on or make changes to this bug.
Description rubin wang 2012-03-04 09:41:36 UTC
Created attachment 28414 [details]
implemenation of RANK

I could not find the implementation of RANK in POI3.8, so I proceed to create one. See attachment. 

I also "add retval[216] = new Rank();" to private static Function[] produceFunctions().

Comment 1 Yegor Kozlov 2012-03-05 07:14:17 UTC
Any chance you could upload a unit test for your implementation?

How to test:

 - open ./test-data/spreadsheet/FormulaEvalTestData.xls and fill the placeholder for RANK. Just follow the pattern that other functions use. The formula testing framework will find and run this test automatically.
- It is a good idea to test against the example from MS Office online help: http://office.microsoft.com/en-us/excel-help/rank-function-HP010062535.aspx?CTT=5&origin=HA010277524.
Also check that your implementation follows the spec and correctly handles ordered/unordered lists, duplicates, not numbers, etc.

Comment 2 rubin wang 2012-03-05 08:26:59 UTC
Created attachment 28417 [details]
FormulaEvalTestData.xls (tests for RANK added)

Test case
Comment 3 rubin wang 2012-03-05 08:37:34 UTC
Created attachment 28418 [details]

Implementation of RANK(ARG1, ARG2, ARG3). Based on the description in http://office.microsoft.com/en-us/excel-help/rank-function-HP010062535.aspx?CTT=5&origin=HA010277524.

The algorithm is as follows:

1) throw EvaluationException(ErrorEval.NUM_ERROR) if either ARG0 does not contain a number or ARG3 does not contain 0 or 1.

2) set rank=0, search each cell in the range specified by ARG2, if the cell contains a number greater than ARG0 and ARG3 is 0, or lower than ARG0 and ARG3 is 1, then rank++;  If the cell does not contain a number, then ignore this cell.

3) return the value of rank
Comment 4 rubin wang 2012-03-05 08:45:08 UTC
Created attachment 28419 [details]

Add one line "retval[216] = new Rank();" to the function "private static Function[] produceFunctions()" This is the only change.
Comment 5 Yegor Kozlov 2012-03-05 12:14:05 UTC
Patch applied in r1297021