SUBTOTAL and other aggregate functions not working correctly inside a dynamic array formula

Aspose.Cells Product Family
, ,
1

Dear Aspose,

I am having trouble with the .NET version of Aspose.Cells calculation engine when the SUBTOTAL function is part of a dynamic array formula. Please review the attached solution, where the result, fxRate1 and fxRate2 variables are all #DIV/0! errors instead of 10632.7, 1 and 4.7 respectively. Attached is the Excel file that the program is trying to read along with the code itself. We are currently using version 22.9.0, but I can also reproduce it with the latest version (23.1.1).

Please note that I have tried other functions as well, like the AVERAGEIF, but the result is similar (then the error is #!REF for some reason). Now I have got a workaround to implement a custom function using the AbstractCalculationEngine to calculate these averages, but I think this is something that should be fixed and I would like to remove the piece of code for the long term. Or do you have any suggestion how to make that working by changing the formula or use a different function instead?

I am looking forward to your response.

Best regards,
Marton

ConsoleApp1.zip (34.3 KB)

2

We reproduced the issue as you mentioned by using your template file. I found SUBTOTAL and other aggregate functions are not working correctly inside a dynamic array formula by Aspose.Cells formula calculation engine. This might be due to the “ANCHORARRAY” formula.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-52634

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

Hi @amjad.sahi,

I see that the ticket is resolved, but I believe that the following Excel and calculation code is related to the same issue. Is there a way that you check the attached Excel file with this code?

var workbook = new Workbook(".\\Calc_summary_sent.xlsx");
workbook.RefreshDynamicArrayFormulas(false);
workbook.CalculateFormula();
var value1 = workbook.Worksheets["Calculation"].Cells["E1"].Value;
var value2 = workbook.Worksheets["Calculation"].Cells["E2"].Value;

Console.WriteLine($"Value1: {value1}");
Console.WriteLine($"Value2: {value2}");

Rather than getting back 30 and 70, I get back 30 and null.

I’m looking forward to your answer.

Best regards,
Marton

Calc_summary_sent.zip (7.1 KB)

4

@varsanyi.marton,

Thanks for the new file.

We fixed your issue (which was logged as “CELLSNET-52634” previously) recently a few days ago. The fix will be included in our upcoming release (Aspose.Cells v23.3) that we plan to publish in the first half of March 2023. You will be notified when the next version is released.

Moreover, I did not evaluate your new test case (using your new file) with fixed version yet although I tested with v23.2 which reproduces your mentioned issue. But we will try to make sure your new test case should also pass by our upcoming release. Once we have new updates, we will let you know.

5

@varsanyi.marton,

Thanks for the new case. We have tested it with our new fix and can confirm the new fix works fine for it too.

6

@johnson.shi, @amjad.sahi

Wonderful, thank you!

7

@varsanyi.marton,
You are welcome.

8

The issues you have found earlier (filed as CELLSNET-52634) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

1 Like