INDEX(FILTER(...);1) working incorrectly inside LAMBDA

Aspose.Cells Product Family
1

Dear Aspose,

I am using 24.4.0 version of Aspose.Cells for .NET, and we are implementing a rather complex calculation using the engine. We are using the INDEX function within a LAMBDA, where we want to get the first item based on some criteria, so we are using the FILTER function as the first parameter of INDEX, and a constant value of 1 as then second parameter. However, we get incorrect result, which is not an error. See the attached sample application.

example.zip (124.7 KB)

When I get the result from the calculation engine, the cell contains 0, but the expected value is 80, which is correctly visible after opening the saved Excel file.

Note that all input ranges of the J2 cell of the ForeignCalculationSubtotals sheet is calculated properly, so the issue must be with the formula of it.

Please note that in this particular case, we can use the INDEX(..., MATCH()) formula combination as a workaround, but it still would be good to fix this issue.

I am looking forward to your response.

Best regards,
Marton

2

@marton.varsanyi
By using sample file and code for testing on the latest version v24.4, we are able to reproduce the issue. It was found that the mixed calculation of INDEX, FILTER, and LAMBDA got incorrect values.

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-55493

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

@marton.varsanyi,

We are pleased to inform you that your issue (logged earlier as “CELLSNET-55493”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v24.5) that we plan to release in the first half of May 2024. You will be notified when the next version is released.

4

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