Cell is not in Solution

[jonathanhoss]

First, thank you for the great library!

I am working with a large Excel file that I need to automate. Since I didn’t create the Excel file myself, I’m unsure what specific functions or features are used inside it.

The Excel loads successfully without any errors. However, when I call the calculate method, the cell I need (E40) is missing from the solution.

Interestingly:

1. I can access and retrieve values for other calculated cells from the same sheet without any issue.
2. When I use .finish(circular=True), the cell E40 becomes available in the solution but its value is 0. Inside Excel, the cell evaluates correctly even with iterative calculations disabled.

Here’s a simplified version of the code I’m using:

import formulas  
formulas.__version__
# >>> '1.2.8'

x1_model = formulas.ExcelModel().loads("SAMPLE.xlsx").finish()  
sol = x1_model.calculate()  

# Trying to access the specific cell  
sol["'[SAMPLE.xlsx]SHEET'!E40"]
# >>> KeyError: "'[SAMPLE.xlsx]SHEET'!E40"


# Also tried this:
sol = x1_model.calculate(
    outputs=[
        "'[Ecxel_Jana.xlsx]SHEET'!E40"
    ]
)

Do you have any suggestions on how to debug this? I would try to get some kind of information. Cell XYZ cannot be evaluated.

Can I check if cell E40 is dependent on other cells or unsupported functions?

Unfortunately, I cannot share the Excel file as it contains sensitive data.

[vinci1it2000]

Thank you for the detailed description and for using the library!

The issue you’re encountering with cell E40 is likely because it is part of a circular reference loop. When you call .finish(circular=True), the library explicitly enables handling circular references, which is why the cell becomes available in the solution. However, the value 0 being returned is not actually 0 in the Excel sense—it is a placeholder behavior due to how Excel internally treats circular references.

Formulas replicates Excel’s behavior exactly: Excel will display 0 for a circular reference cell, but it’s not an actual 0. You can verify this by seeing the type/class of the returned value that should be a sh.Token.

To confirm this, I would need to review the Excel file. If you can share the file (with sensitive data redacted), I can provide an exact solution or workaround. Alternatively, you can try checking dependencies for E40 to identify any potential circular references.

Let me know how you’d like to proceed!