Python Forum
Python “Formula” Package: How do I parse Excel formula with a range of cells?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python “Formula” Package: How do I parse Excel formula with a range of cells?
#1
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!]]
Reply
#2
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  {SOLVED]Help getting formula value in Excel to a csv Pedroski55 1 90 Sep-20-2021, 12:19 AM
Last Post: Pedroski55
  How do I read in a Formula in Excel and convert it to do the computation in Python? JaneTan 2 288 Jul-07-2021, 02:06 PM
Last Post: Marbelous
  Coding a formula citizen 2 378 Jun-16-2021, 05:22 PM
Last Post: citizen
  Question about formula implementation in general format Alienspecimen 0 451 Mar-01-2021, 08:39 PM
Last Post: Alienspecimen
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 2,089 Feb-11-2021, 09:31 PM
Last Post: nilamo
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 594 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Reading from Excel: value not formula! faryad13 0 826 Oct-28-2020, 09:52 PM
Last Post: faryad13
  I try to make Heron formula program abcd 7 858 Oct-22-2020, 12:48 AM
Last Post: abcd
  Python IDE doesn't see opencv-python package on my Jetson Nano sadhaonnisa 1 895 Oct-11-2020, 01:04 AM
Last Post: Larz60+
  Applying Moving Averages formula to a number lynnette1983 1 715 Sep-29-2020, 10:21 AM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020