Python “Formula” Package: How do I parse Excel formula with a range of cells?

[JaneTan]

Hi,

I have read the documentation of the "Formula" package at https://formulas.readthedocs.io/en/stabl...ng-formula but it doesn't explain how to input the values for an Excel function that uses a range of cells.

Hope someone can help with the below queries. Thank you!

1) For Excel function with a range of values separated by :, how do I input into the parsed function?

EG

val='=SUM(A1:B3)'
func = formulas.Parser().ast(val)[1].compile()

print (func.inputs)
output:
OrderedDict([('A1:B3', <Ranges>(A1:B3))])

print (func(25,4))
output:
raise TypeError('too many positional arguments') from None

TypeError: too many positional arguments

2) Excel Function SUMPRODUCT doesn't seem to be supported, I get [[#VALUE!]] error

EG

val='=SUMPRODUCT(A1,B3)'
func = formulas.Parser().ast(val)[1].compile()

print (func.inputs)
output:
OrderedDict([('A1', <Ranges>(A1)), ('B3', <Ranges>(B3))])

print (func(25,4))
output:
[[#VALUE!]]

[jefsummers]

1. I would use Pandas. Check the documentation for the read_excel function HERE. You can specify the file, sheet, and columns to be imported.

2. Once in Python, do not expect to use Excel functions. Different program. You will likely find equivalents. Again, check out Pandas.