SQRT does not support single-cell range

Aspose.Cells Product Family
1

When evaluating the following formula, Aspose.Cells for Java cannot calculate the value: (A1 contains a number, e.g. 100)

=SQRT(A1:A1)

Excel allows a single-cell range as an argument to SQRT. Currently, Aspose.Cells returns #VALUE after a workbook.calculateFormula();

I have not tried other functions which expect a numeric argument whether it is allowed to supply a single-cell range as a parameter.

With kind regards,

Barry

2

Hi,

Well, yes, allowing single cell range for SQRT formula is not supported. I have logged your feature request to our tracking system with an id: CELLSJAVA-20060. We will support it soon.

Thank you.

3

Hi,

We have fixed your issue for calculating formulas, please try the attached version.

Thank you.

4

It still doesn’t work when used in combination with ArrayFormula and CustomFunction:

Workbook workbook = new Workbook();
Worksheet sheet1 = workbook.getWorksheets().getSheet(0);
sheet1.getCells().getCell(“A1”).setValue(2);
sheet1.getCells().getCell(“A2”).setArrayFormula("=SQRT(DUMMY(A1:A1))",1,1);
sheet1.getCells().getCell(“A3”).setArrayFormula("=SQRT(A1:A1)",1,1);
workbook.calculateFormula(false,new ICustomFunctionEx() {
public Object getSettings(Object o) {
return null;
}

public Object calculateCustomFunction(String name, String[] paramLiterals, Object[] params, Object[] contexts) {
Cell cell = (Cell)contexts[0];
Worksheet worksheet = cell.getWorkSheet();
Workbook workbook = worksheet.getWorkbook();
if (“DUMMY”.equals(name)) {
return params[0]; // dummy
}
else {
return null;
}
}

});
System.out.println("Value: "+sheet1.getCells().getCell(“A2”).getValue());
System.out.println("Value: "+sheet1.getCells().getCell(“A3”).getValue());

When a CustomFunction is called (in this case DUMMY, which simply returns the first parameter), then the calculation-engine cannot perform the SQRT on a single cell:

Value: #VALUE!
Value: 1.4142135623730951

Unfortunately, the Excel sheet that we are trying to calculate uses a lot of these formulas (e.g. {=SQRT(MMULT(A1:A2,TRANSPOSE(B1:B2)))} … )

With kind regards,

Barry

5

Hi,

Thanks for the sample code.

I can find the issue as you have mentioned using your code. I have reopened the issue and we will figure it out soon.

Thank you.

6

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.