ABS($G$3:$G$9999) does not work

Aspose.Cells Product Family
1

My company has purchased Aspose.Cells for Java for use in a application we are developing. Our customer’s excel sheets use ABS on a range on cells - at that point the Aspose engine bails out with a FormulaCalcException.

(The exact formula used is structured like =SUMPRODUCT(($page.$D$3:$page.$D$9999<>“xxx”)(ABS($page.$E$3:$page.$E$9999)<$E$1)($page.$F$3:$page.$F$9999))
, but the same error can be reproduced with
"{=ABS($D$18:$D$20)}"
)


best regards,
Christian Wendt

2

Hi,

Thanks for your info. It's caused by function sumproduct. But it's not the easy feature. It will take us 1 week to do it.

3

Thanks for your fast reply,


I believe the main error is not caused by sumproduct but by ABS itself:

In a testcase i deleted all formulas out of the excel sheet and inserted a single {=ABS(G8:G10)} into one cell defined as ‘matrix output’ - i got a FormulaCalcException, too.


Best regards,
Christian Wendt

4

Hi,

Ok! we will figure it out soon.

Thank you.

5

Hi,

Please try this fix.

If the error still occours, please post your template file.

6

Thanks for you quick fix. Our first problem-testcase : the ABS on a range seems to be solved by it.

Sadly, the main sheet still doesn’t calculate. We tried to narrow the problem down and came up with a very weird testcase. We narrowed the error down from a bunch of complex formulas in several worksheets to a single cell with a more simple formula that still exhibits the same error.

We did try to do the same in a new worksheet - the exact same formula does not throw an exception.

It doesn’t return the correct result, either - instead #VALUE is reported.

Excel and Openoffice both show the correct result in the cell.

Attached is a zip with both excel sheets and our simple test.java

Best regards,
Christian Wendt

7

Hi Christian,

Thanks for the template file with code,

Yes, We can reproduce the issue. We will fix it soon.

Thank you.

8

Hi Chirstian,

Please try this attached fix.

Many functions and operaters are changed if they are the child params of SumProduct. We could not fix all bugs of SumProduct. So could you list your usage of sumproduct function? We will support them first.

9

It fixes our minimal testcase, thanks.

but in our main application we now get this exception:
com.aspose.cells.FormulaCalcException: [[D cannot be cast to com.aspose.cells.bK
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.cL.e(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.P.d(Unknown Source)
at com.aspose.cells.P.a(Unknown Source)
at com.aspose.cells.M.b(Unknown Source)
at com.aspose.cells.M.a(Unknown Source)
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.cL.g(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.cL.b(Unknown Source)
at com.aspose.cells.M.b(Unknown Source)
at com.aspose.cells.M.a(Unknown Source)
at com.aspose.cells.M.b(Unknown Source)
at com.aspose.cells.M.a(Unknown Source)
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.cL.a(Unknown Source)
at com.aspose.cells.cL.g(Unknown Source)
at com.aspose.cells.Cell.a(Unknown Source)
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.Worksheet.a(Unknown Source)
at com.aspose.cells.Worksheets.calculateFormula(Unknown Source)
at com.aspose.cells.Worksheet.calculateFormula(Unknown Source)

10

I Attached a excel sheet (suitable for my initial testcase) with a broken down defective formula - it doesn’t make sense at all cause it references empty cells, but it shouldn’t throw that exception.

Thanks for your effort.

11

Hi,

Please try this fix.

12

Thank you very much. The provided fix calculates the entire workbook without any exception. We’re still looking into the details whether it is calculating correct, too - but everything looks fine so far.

Thanks for the quick responses.


Best regards,
Christian Wendt