LET function #NAME error

Aspose.Cells Product Family
1

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

  1. When using an xlsb workbook format with a LET function and calling wb.CalculateFormula(ignoreError: true); we get #NAME errors.
  2. 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

Many thanks in advance for any assistance

2

@tombzenith

It appears you are experiencing issues with the LET function in your Excel workbooks while using Aspose.Cells version 25.9.

  1. 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.

  2. 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.

3

@tombzenith,

Thanks for the XLSB file and details.

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.

1 Like
4

@tombzenith,

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.

5

@tombzenith,

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.

6

Many thanks for your help, I’ll await the updated release to test the functionality. Thanks again

7

@tombzenith,

You are welcome. We will ensure to keep you informed with updates regarding the new version as soon as it becomes available.

8

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)

9

@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.

1 Like