Help needed on using Formulas in parsing SUMPRODUCT formula

[markm812]

Hi, I was working on parsing the formular for SUMPRODUCT from Excel.

def formulas_weighted_sum(array1,array2):
    print(f'=SUMPRODUCT(B1:B{len(array1)},C1:C{len(array2)})')
    func = formulas.Parser().ast(f'=SUMPRODUCT(B1:B{len(array1)},C1:C{len(array2)})')[1].compile()
    print(func.inputs)
    return func(array1,array2)

I called the function with the following line:

formulas_weighted_sum([1,2,3],[1,2,3])

that gives the following:

=SUMPRODUCT(B1:B3,C1:C3)
OrderedDict([('B1:B3', <Ranges>(B1:B3)), ('C1:C3', <Ranges>(C1:C3))])
array([[#VALUE!]], dtype=object)

which is not 14, the expected answer.

May I ask for specification on how to give proper input for the formular?

[vinci1it2000]

I fixed. You can use my dev. I suggest the following implementation:

>>> import formulas
>>> weighted_sum = formulas.Parser().ast(f'=SUMPRODUCT(A,B)')[1].compile()
>>> weighted_sum([1,2,3],[1,2,3])
14.0 

As an alternative, you can provide np.arrays instead of lists.