Array formula using 'RIGHT()' returning error

Aspose.Cells Product Family
1

I have a simple excel sheet containing an array formula that uses the RIGHT() method in Excel (see attached). The referenced cells contain numbers 10 through 15 while the array formula is {=SUM(IF(RIGHT($A$2:$A$7,2)=“15”,$A$2:$A$7,FALSE))}. This correctly returns 15 in Excel.


However if I open the the workbook using Aspose and calculateFormula, the value is returned as an error “#VALUE!”.

The code I’m running is as simple as:

workbook = new Workbook(filepath);
workbook.calculateFormula();

Can Aspose not handle array formula?

Thanks.


2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We were able to replicate this issue with the latest version with the following code. We have logged this issue in our database. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40523.

Java


String filePath=“F:\Shak-Data-RW\Downloads\arrayRight.xls”;


Workbook workbook = new Workbook(filePath);

workbook.calculateFormula();


Worksheet worksheet = workbook.getWorksheets().get(0);

Cell b9 = worksheet.getCells().get(“B9”);


System.out.println(b9.getStringValue());


workbook.save(filePath + “.out.xls”);

Output:
#VALUE!
3

Thanks for the update.


This brings up a more general issue. How can I ensure that a sheet is being calculated correctly and without errors?

I have been using a method along the lines of:

List erroredCellLocations = new ArrayList<>();


FindOptions findOptions = new FindOptions();
findOptions.setLookInType(LookInType.VALUES);

Cell foundCell;
Cell prevCell = null;
int cnt = worksheetCells.getCount();

for (int i = 0; i < cnt; i++) {
foundCell = worksheetCells.find("#VALUE!", prevCell, findOptions);
if (foundCell != null) {
erroredCellLocations.add(foundCell.getName());
prevCell = foundCell;
}
}
return erroredCellLocations;

Is there a more efficient way to check if a worksheet / workbook contains errors?

Thanks.
4

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Your approach is right. Finding the string using Find API is the efficient way of checking the workbook if it contains #VALUE!.

The other approach is to loop through all the cells and check their values, which is not efficient way.

5

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We have fixed the issue of calculating formula.

Please download and try this fix: Aspose.Cells for Java v7.4.3.5 and let us know your feedback.

6

The issues you have found earlier (filed as CELLSJAVA-40523) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.