Free Support Forum - aspose.com

ArrayForumula Incorrect Value

Aspose.Cells Product Family
#1

I'm evaluating Aspose.Cells for .NET. My problem has to do with an array function. The function is:

SetArrayFormula(string.Format("=SUMPRODUCT(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1}),IF(ISNUMBER(C{0}:C{1}),C{0}:C{1}))/SUM(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1}))", startRow, endRow), 1, 1)

When I open the Excel sheet there is a value in the cell, but it is incorrectly calculated. When I "edit" it and hit CTRL + SHIFT + ENTER myself, the value is then correct. I was using Aspose.Cells 4.5.0.0, but I came across 4.5.0.13 on the forums but the problem is still there.

I have attached a copy of my problem from my original test. The value at the bottom should equal -4.8%.

-Damien

#2

Hi Damien,

Thanks for providing us the template file and pointing out the formula.

Yes, we found the issue after an initial test, we will figure out the issue soon.

Thank you.

#3

Amjad,

Have you had any luck fixing this issue? I have a prototype of the the report that my client needs, but the Array Function issue is preventing me from sending it on to them and having them purchase the component for use in their application.

Thanks,
-Damien

#4

Hi Damien,

We are working on the issue, hopefully we will provide you the fix soon.

Thank you.

#5

Hi Damien,

Please try the fix in http://www.aspose.com/community/forums/thread/139016.aspx.

We have fixed this bug.

#6

I found a new problem. While the initial formula I posted now works, I am unable to expand this to include an ISERROR check

For example

SetArrayFormula(string.Format("=IF(ISERROR(SUMPRODUCT(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1}),IF(ISNUMBER(C{0}:C{1}),C{0}:C{1}))/SUM(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1}))), \"\", SUMPRODUCT(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1}),IF(ISNUMBER(C{0}:C{1}),C{0}:C{1}))/SUM(IF(ISNUMBER(C{0}:C{1}),D{0}:D{1})))", startRow, endRow), 1, 1);

This causes #VALUE, but when I hit CTRL + SHIFT + ENTER myself, the value is correct

I've tried other things like testing is the denominator of the division is 0 (e.g. IF (SUM(...) = 0) prior to calling the formula, this works in Excel, but not with Aspose. In this case I get an error message when opening the workbook and {#N/A} is shown in the field.

Is there another way for me to test this and prevent the DIV/0 errors that will work with Aspose.Cells?

Thanks,

-Damien

#7

Hi Damien,

We found the issue and will fix it soon.

Thank you.

#8

Hi Damien,

Please try the fix in http://www.aspose.com/community/forums/thread/139606.aspx

#9

Simon -

I tried the version in that post, but the problem still persists. I have attach a spreadsheet with the problem. The first group in the spreadsheet is a subset of what was generated with Aspose.Cells, and the second group is when I make it an array function myself in Excel.

Thanks,
-Damien

#10

Hi Damien,

Thanks for your feedback,

Yes, we found the issue and will provide you a fix soon. Actually we did provide you the wrong fix, so, sorry for that.

Thank you.

#11

Hi Damien,

Please try the attached version, we have fixed the formula issue.

Thank you.