Hai Experts,

   Is there any release with this function fix "RANDBETWEEN()".

Thanks
Jaibabu.J

I guess RANDBETWEEN is not a very popular function.  If you are able to write an implementation yourself and submit a patch, it is likely to get added more quickly.

Similar functions already exist in NumericFunction.java:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/functions/NumericFunction.java?annotate=893403


Contributions are always welcome.

Created attachment 25876 [details]
Adds support for RANDBETWEEN() function. The zip file contains a svn diff and a tar file which contains an Excel spreadsheet for testing

RANDBETWEEN is one of the Analysis Toolpak formula and I have implemented it as such rather than an entry in NumericFunctions

One strange behavior I noticed while formulating Testcases for the function was that it can't be assigned into a workbook created directly in POI. The same is true for all the currently implemented ATP functions ISEVEN(), ISODD() and YEARFRAC()

If you run the following

Workbook wb = new HSSFWorkbook();	
Sheet sheet = wb.createSheet("ATPSheet");
Row row = sheet.createRow(0);
Cell formulaCell = row.createCell(0, HSSFCell.CELL_TYPE_FORMULA);
formulaCell.setCellFormula("ISEVEN()");

You will get the following exception:

Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Name 'ISEVEN' is completely unknown in the current workbook
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:914)
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:552)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:423)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1113)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1073)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1060)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1420)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1520)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1504)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1461)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1441)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1562)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
	at org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:72)
	at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:598)

If you open a Workbook that has the ATP formula already present they you are able to assign the formula to new cells without issue.

Is this expected behavior or is there a defect with the ATP functions? Or do you need to alter the workbook after creation to support the ATP functions?

Thanks for the patch, applied in r984796

> If you open a Workbook that has the ATP formula already present they you are
> able to assign the formula to new cells without issue.
> 
> Is this expected behavior or is there a defect with the ATP functions? Or do
> you need to alter the workbook after creation to support the ATP functions?

This is currently a limitation of POI. We can evaluate UDFs from the Analysis Toolpack but we cannot assign formulas containing these functions. It may seem an easy task, but unfortunately it is not. 

Firstly, FormulaParser needs additional information to process a user defined formula token. This additional information should describe the number of arguments and their type, type of the returned value, etc. For built-in functions this is described by org.apache.poi.hssf.record.formula.functionFunctionMetadata and functionMetadata.txt, I guess we will need something similar for the Analysis Toolpack.  

Secondly, each UDF used in a workbook must have a corresponding EXTERNALNAME record, this is how Excel resolves user-defined functions. Suppose you open a Workbook that has formulas with ATP functions. Each used ATP has a corresponding EXTERNALNAME record and is registered in the workbook-global LinkTable:

ISEVEN      --> EXTERNALNAME 
RANDBETWEEN --> EXTERNALNAME 

where all EXTERNALNAME records are aggregated in a LinkTable.

When you assign more formulas with ISEVEN and RANDBETWEEN, the FormulaParser resolves their names via the list of EXTERNALNAME and the assignment is successful. When you assign formulas with ISEVEN and RANDBETWEEN to a new workbook then the LinkTable is empty and you get a "completely unknown" exception.

Yegor