We are getting a couple of errors due to the LET function after calling wb.CalculateFormula(ignoreError: true). We are using version 25.9 of Aspose.Cells
When using an xlsb workbook format with a LET function and calling wb.CalculateFormula(ignoreError: true); we get #NAME errors.
When using a xlsx workbook with a nested LET function where the calculations in one let function depend on a table that also uses let functions, we get #NUM errors.
For our working case we need to be able to use nested let functions within a xlsb workbook. Test LET Function WBs.zip (1.3 MB)
I have attached test workbooks for both issues. In both situations we are returning the table in the sheet ‘Let Function’ . For the nested let function the addional tables are in the ‘Tables’ tab. We have set wb.RefreshDynamicArrayFormulas(true); then called wb.CalculateFormula(ignoreError: true); and then values = wb.Worksheet.Cells.CreateRange(a,b,c,d).Value to get the table
It appears you are experiencing issues with the LET function in your Excel workbooks while using Aspose.Cells version 25.9.
For the XLSB workbook format, encountering a #NAME error with the LET function may suggest that the function is not recognized or supported in that context. Verify that the workbook is correctly formatted and that the LET function is being utilized appropriately. Additionally, check for any updates or patches for Aspose.Cells that may address this issue.
In the case of the XLSX workbook with nested LET functions resulting in #NUM errors, this could be attributed to circular references or incorrect dependencies among the LET functions and the tables. Carefully review the logic in your nested LET functions to ensure they are referencing the necessary data correctly.
To troubleshoot these issues, consider the following steps:
Confirm that you are using the latest version of Aspose.Cells, as updates may include fixes for known issues.
Examine the formulas in your workbooks for any potential errors or unsupported features.
If the problem persists, consider reaching out to the Aspose support forum with specific details about your formulas and the structure of your workbooks.
For additional assistance, consult the official documentation regarding supported functions and error handling in Aspose.Cells.
I am able to reproduce the issue as you mentioned by using the following sample code with your template XLSB file. I found when calculating LET function, it results in “#NAME?” error. I am using Aspose.Cells for .NET 25.10.
Workbook workbook = new Workbook("e:\\test2\\Test Let Function Binary Minimal.xlsb");
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx");
We require thorough evaluation of the issue. 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-59184
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.
I tested your scenario/case with Aspose.Cells for .NET v25.10 (please try it) using the following sample code with your template XLSX file. It works fine and the formulas are calculated properly.
Workbook workbook = new Workbook("e:\\test2\\Test Nested Let Function Std WB.xlsx");
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx");
Please note, with the release of the new version, check Release notes, there are some important changes in the behavior of calculating formulas:
Changes behavior of Workbook.CalculateFormula(…) methods for refreshing dynamic array formulas Dynamic array formulas are becoming more and more popular in more and more users’ cases. Old versions of Aspose.Cells required users to call Workbook.RefreshDynamicArrayFormulas(…) explicitly before calling Workbook.CalculateFormula() if there are dynamic array formulas that need to be refreshed. Starting from 25.10, we include this step into Workbook.CalculateFormula(…) automatically. So users do not need to explicitly call Workbook.RefreshDynamicArrayFormulas(…) anymore before calculating formulas for the workbook.
This is to inform you that your issue (Ticket ID: “CELLSNET-59184”) has been resolved. The fix will be included in the upcoming release (Aspose.Cells v25.11) that we plan to release in the first half of November 2025. You will be notified when the next version is released.
Hi, we have now tested in v25.10 and we still get the #NUM error. I have attached an updated workbook that has column totals and in our test we just return the value from I3 on the sheet ‘Let Function’. We’re not doing the workbook.Save we are using the cell range to return the value from I3. The other total values from row 3 give correct results.
From our investigations is appears that the Rand() function used in the nested LET funtion is causing the issue. Test Let Function Combined Std WB_v2.zip (2.6 MB)
@tombzenith
Thank you for the xlsx template file. We have reproduced the issue of “#NUM!” error for some LET functions. We also 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-59201
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.
This is to inform you that your issue (Ticket ID: “CELLSNET-59201”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.11) that we plan to release in the first half of November 2025. You will be notified when the next version is released.
The issues you have found earlier (filed as CELLSNET-59184,CELLSNET-59201) have been fixed in this update. This message was posted using Bugs notification tool by leoluo
I have updated to v25.11 of Aspose.Cells and I’m getting an unusual exception " Exception of type ‘\u000f\u001b\u0015’ was thrown " This is when I’m copying the formula in a table and then re-caclulating the workbook. Can you provide some guidance on what may cause this exception so that I can try to isolate the issue in a test workbook.
It is hard for us to know which kind of formula or data may cause such kind of issue. Some most possible situations may be: circular references, table formula referencing to other formula recursively with deep stack, or you are using custom calculation engine of your own with some abnormal logic…
We think you may save the workbook to a local file just before calling Workbook.CalculateFormula(), if calculating the workbook loaded from the saved file can reproduce the issue, please send the file to us so we can trace and figure the issue out.
I have tested the LET function again in v25.11 of Aspose.Cells and it has reolved the issues for a simple LET function but I’m still getting #NAME errors for a more complex nested function. In the attached I am loading the spreadsheet, calculating and then returning the table tblFloorVols (Tab Increases, range A15:J2415). The LET function in the table references other tables in the tabs CalcSensitivities and Tables. The tab SimpleLet shows that the simple let function is working without errors. Complex Nested LET Function_v2.zip (1.8 MB)
I am able to reproduce the issue as you mentioned by using your template Excel (XLSB) file. I found when calculating LET involving within complex nested functions results in “#NAME?” error. I can notice the issue with the table tblFloorVols (Tab Increases, range A15:J2415) as you described.
Workbook workbook = new Workbook("e:\\test2\\Complex Nested LET Function_v2.xlsb");
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.xlsx");
We require thorough evaluation of the issue. 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-59439
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.