XIIR Function Give Numeric Error If any NULL Value Exist in the Range

Aspose.Cells Product Family
1

Hi,

XIIR Function not working when its range contains empty row(s).

Let say an excel only contain the formula “=XIRR(A1:A4;B1:B4)” at C1

and we fill the ranges with the following code;

  Workbook workbook = new Workbook("C:/test1.xlsx");
  WorksheetCollection worksheets = workbook.getWorksheets();
  Worksheet ioWorksheet = worksheets.get(0);
  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
  ioWorksheet.getCells().get("a1").putValue(-1000);
  ioWorksheet.getCells().get("b1").putValue(dateFormat.parse("20190101"));
  ioWorksheet.getCells().get("a2").putValue(-1000);
  ioWorksheet.getCells().get("b2").putValue(dateFormat.parse("20190201"));
  ioWorksheet.getCells().get("a3").putValue(-1500);
  ioWorksheet.getCells().get("b3").putValue(dateFormat.parse("20190301"));
  ioWorksheet.getCells().get("a4").putValue(5000);
  ioWorksheet.getCells().get("b4").putValue(dateFormat.parse("20190401"));

  CalculationOptions options = new CalculationOptions();
  options.setPrecisionStrategy(CalculationPrecisionStrategy.NONE);
  workbook.calculateFormula(options);

  String cellValue = ioWorksheet.getCells().get("c1").getStringValueWithoutFormat();

And the result is SUCCESSFULL

But if the formula is “=XIRR(A1:A5;B1:B5)” at C1

the ranges increased 1 row and it is left empty;

The result become #NUM!

On the other hand, excel work fine in both cases.

We are under “Aspose.Total for Java” license until 29.12.2019
We tried this issue on versions, 18.10 and 19.4.
The behavior was exactly the same.

Please help,

Gökçe

2

@gkucukeren,

Thanks for the sample code and details.

After an initial test, I am able to observe the issue as you mentioned by using your sample code with a template file. I found XIRR function gives numeric error if any null value exists in the range:
I have a template file having formula “=XIRR” in C1 cell in the first worksheet. I used the following sample code using the template file (attached):
e.g
Sample code:

 Workbook workbook = new Workbook("e:\\test2\\test1_ne.xlsx");
         WorksheetCollection worksheets = workbook.getWorksheets();
         Worksheet ioWorksheet = worksheets.get(0);
         SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
         ioWorksheet.getCells().get("a1").putValue(-1000);
         ioWorksheet.getCells().get("b1").putValue(dateFormat.parse("20190101"));
         ioWorksheet.getCells().get("a2").putValue(-1000);
         ioWorksheet.getCells().get("b2").putValue(dateFormat.parse("20190201"));
         ioWorksheet.getCells().get("a3").putValue(-1500);
         ioWorksheet.getCells().get("b3").putValue(dateFormat.parse("20190301"));
         ioWorksheet.getCells().get("a4").putValue(5000);

ioWorksheet.getCells().get("b4").putValue(dateFormat.parse("20190401"));

CalculationOptions options = new CalculationOptions();
options.setPrecisionStrategy(CalculationPrecisionStrategy.NONE);
workbook.calculateFormula(options);

String cellValue = ioWorksheet.getCells().get("c1").getStringValueWithoutFormat();

System.out.println(cellValue);

Output:

#NUM!

I have logged a ticket with an id “CELLSJAVA-42891” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.
files1.zip (5.6 KB)

3

Hi @Amjad_Sahi,

is there any update or expected fix date about this issue.
According your answer we look for a workaround ?

Looking forward good news

4

@gkucukeren,

We are working over your issue. We will provide you more details or latest updates on it within a couple of days or so.

5

@gkucukeren,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42891”) now. We will provide you the fixed version by next week after performing QA and incorporating other enhancements and fixes.

6

The issues you have found earlier (filed as CELLSJAVA-42891) have been fixed in Aspose.Cells for Java 19.5. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi