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

[varsanyi.marton]

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)

[Amjad_Sahi]

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.