Problem with Array Formula

Aspose.Cells Product Family
1

There is an inconsistency in the calculation of an array formula in Aspose 8.3.1.0 compared to Excel and Aspose 6.

The issue is with an array formula of the form:

{=SUM(IF(NOT(ISERROR(C1:C14)),C1:C14))}

In Aspose.Cells 6 the result matches Excel.

In 8.3.1.0 it results in #VALUE.

The problem appears to be in the NOT portion in that if I remove the NOT and switch the result to the else portion of the statement as follows:

{=SUM(IF(ISERROR((C1:C14)),C1:C14))}

We are not wild about converting all of our hundreds of formulas to the because it would be pretty error prone and could easily miss something. Also, it would be an easy mistake to make as the workbook is updated.

Any suggestions?

2

Hi,


Thanks for the template file and details.

After an initial test, I observed the issue as you mentioned by using the following
sample code with your template file you attached. I found Aspose.Cells does not
calculate the array formula fine, it gives “#VALUE!” error instead of
“3351712203” value.

e.g


Sample code:


var work = new Workbook("e:\\test2\\ASPOSE+Tester.xls");
System.Console.WriteLine(work.Worksheets["Test"].Cells["A2"].Value); //3351712203
work.CalculateFormula();
System.Console.WriteLine(work.Worksheets["Test"].Cells["A2"].Value);//#VALUE!

I also tested the issue with some older version e.g v7.0.1 and it gives me expected value. I have logged a ticket with an id "CELLSNET-43466" for your issue. We will look into it to figure it out soon.

Thank you.
3

Hi Amjad,


Thanks for confirming and the issue. Will I be updated when there is a resolution?
4

Hi,

Thanks for your posting and using Aspose.Cells.

Once, the fix will be available for you, we will post a reply in this thread to notify you. Also, since your thread is attached with the logged issue, once this issue is fixed and major official release will be published, you will get automatic notification of the fix.

5

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.3.2.7 and let us know your feedback.

6

Thanks for the quick response. We will test it out.

7

We have tested this out and there was a definite improvement, however it has some problems when one of the cells has an error.

Please find attached the small workbook. We have replicated the same in A24 and A26 cells in “Test” worksheet.

8

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe problems with cells A24 and A26. We have tested this issue with the following sample code and attached the output pdf for your reference. Could you please name all the cells which have the problems so that it will be helpful for the product team to sort out and fix these issues? Thanks for your cooperation.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\ASPOSE+Tester+with+error+values+in+array+formula.xls”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();


workbook.Save(“output.pdf”);

9

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

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

Attached is a spreadsheet with some test cases on the Summary tab. There are two cases where the results are different from Excel and also different from Aspose. These rows are highlighted in red.


There is one also where the precision looks to be deviate quite a bit from Excel.

All values and formulas are in the Summary tab.
11

Hi,

Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue after executing the following sample using the latest version: Aspose.Cells for .NET 8.4.0. We found the Calculate Formula method’s results are difference from Excel.

We have logged this issue in our database for investigation. We will look into it and fix 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

  • CELLSNET-43492 - Workbook.CalculateFormula gives different results from Excel

I have attached the output PDF generated with the following code and screenshot highlighting this issue for a reference.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\ASPOSE+Tester+03132015.xls”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(“output.pdf”, opts);

12

Thanks Shakeel.


Is there an estimate for when a patch would be available? We would like to upgrade to the newest version but are stuck on 6.x until this is fixed.
13

Hi,


We have fixed your issue logged as “CELLSNET-43492”. We will provide the fixed version (e.g Aspose.Cells for .NET v8.4.0.1 - the download link would be shared) before the end of this week hopefully after doing the QA and incorporating other enhancements and fixes.

Thank you.
14

Hi All,


Is there an update on this ticket?
15

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.4.0.1

We have fixed your issue “CELLSNET-43492” now.

Let us know your feedback.

Thank you

16

This example in the spreadsheet is now working. However, there seems to be a specific case where #REF! in the formula is still causing a discrepancy. We are trying to isolate it but it is in a very complex workbook.

We are facing the issue in “FI5” cell of “Validation Asset Details” sheet. While opening it in Excel, it shows the value .05. But while trying to read it through Aspose 8x version, it gives the #Ref! error.

17

Hi,


Thanks for providing us template file and details.

After an initial test, I observed the issue as you mentioned by using the following sample code with your new template file. I found Aspose.Cells does not read the value from the cell (FI5) or calculated value against the array formula fine, it gives “#REF!” error.
e.g
Sample code:

string filePath = @“e:\test2\ASPOSE+Validation+Workbook+2013-03-26.xls”;


var work = new Workbook(filePath);
System.Console.WriteLine(work.Worksheets[“Validation Asset Details”].Cells[“FI5”].Value); //#REF!
work.CalculateFormula();
System.Console.WriteLine(work.Worksheets[“Validation Asset Details”].Cells[“FI5”].Value);//#REF

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

Once we have any update on it, we will let you know here.

Thank you.
18

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.4.0.3 and let us know your feedback.

We have fixed your mentioned issue. But we find MS Excel returns the strange result of FG110:FG120 in the worksheet “Validation Liability Details”
.
The value of FG100 is 9/17/2014 in MS Excel and it’s 6/18/2015 in Aspose.Cells
.
We could not know it’s limitation or a bug of MS Excel that if day parameter (in the function DATE(year,month,day)) > Short.MaxValue , it will be processed as Short.MaxValue.

We think Aspose.Cells work as expected. If you still want to get the same result as MS Excel, please contact to us.

19

We seem to be getting closer. Here is another example that appears to not be working.


{=SUM(IF(NOT(ISERROR(I24:I26)),IF($K24:$K26=“Key Interest Rate Duration, [a / b x d]”,I24:I26)))}

With this data in I24:I26

0.2
0.1
#REF!

and this data in $K24:$K26

Key Interest Rate Duration, [a / b x d]
Key Rate Durations
#REF!

Please see attachment.

20

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested this issue with the following sample code and I did not find any different results than Excel. Both Excel and Aspose.Cells calculate the values in a same way.

I have attached the output pdf which was generated after workbook.CalculateFormula(). I have also attached the screenshot highlighting the obtained values.

Please provide us a screenshot highlighting your issues so that we could look into it more precisely and closely.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\ASPOSE+Tester+04092015.xls”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();


//Hide all the sheets except Tester

foreach (Worksheet sheet in workbook.Worksheets)

{

if (sheet.Name != “Tester”)

{

sheet.IsVisible = false;

}

}


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(“output.pdf”, opts);