Calculated ranges to be used inside another function

[mageshkv]

Suppose I calculate

A1 = B1 + 1 A2 = B2 + 1 ....

A1000 = B1000+ 1

C4=SUM(A1:A1000)

1. How will formula parser handle this. Should I pass it as list of values or get them into Dataframe. Since A1 to A1000 are calculated in the sheet ?.

2. Is there any other easy way in Formulas to handle this situation

Regards Magesh

[vinci1it2000]

1. You should pass a dictionary with all values from column B.
2. You can import the excel and overwrite some values

I'm attaching an example of usage for the problem you mentioned:

import formulas
import pandas as pd
import schedula as sh

n = 10000  # Problem size.

# Model definition.
model = formulas.ExcelModel().from_dict(sh.combine_dicts(
    {f'A{i}': f'= B{i} + 1' for i in range(1, n + 1)},
    {'C4': f'=SUM(A1:A{n})'}
))

# Raw dataset (Pandas excel like).
xl_df = pd.DataFrame(
    data=list(range(1, n + 1)), index=list(range(1, n + 1)), columns=['B']
)

# Prepare inputs.
inputs = {
    '%s%d' % k: v
    for k, v in sh.stack_nested_keys(xl_df.to_dict('dict'), depth=2)
}
# Run model.
sol = model.calculate(inputs)  

# Validate results.
assert sol['C4'].value[0][0] == (sum(list(range(1, n + 1))) + n)