I have a formula that if it is written in the Excel formula bar works fine but if I type the same formula in pandas, it shows this error:

Blockquote Removed Records: Formula from /xl/worksheets/sheet1.xml part

The formula is pretty large:

=INDEX(LINEST(FILTER(IF(ISNUMBER(E2:E1048576),E2:E1048576,""),IF(ISNUMBER(E2:E1048576),E2:E1048576,"")<>"",),FILTER(B2:B1048576,IF(ISNUMBER(E2:E1048576),E2:E1048576,"")<>"",),FALSE,TRUE),1,1)

This formula computes the slope of the scatter plot between two signals, but one of them (Column E) may contain #N/A values.

The formula works fine but I can't find the reason why it does not work when creating the .xlsx with pandas.

import pandas as pd

df = pd.DataFrame()
formula = '=INDEX(LINEST(FILTER(IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\"),IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\")<>\"\",),FILTER(B2:B1048576,IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\")<>\"\",),FALSE,TRUE),1,1)'
df[0,'formula'] = formula
df.to_excel('name.xlsx')

The code is not exactly like the one I posted (obviously, since it is useless), but the error pops nonetheless.

I would appreciate the help, maybe there is an error in the formula syntax? What I can't understand is that it works if I type the formula in Excel...

CodePudding user response：

It should work with a couple of provisos:

1. You will need a recent version of Excel since the formula contains a dynamic function (FILTER()).
2. You will need to use a recent version of XlsxWriter as the excel engine in order get support for dynamic functions.

Here is a small example based on yours:

import pandas as pd

formula = '=INDEX(LINEST(FILTER(IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\"),IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\")<>\"\",),FILTER(B2:B1048576,IF(ISNUMBER(E2:E1048576),E2:E1048576,\"\")<>\"\",),FALSE,TRUE),1,1)'

df = pd.DataFrame({'formula': [formula]})
writer = pd.ExcelWriter('formula.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

writer.save()

And here is the output:

As you can see the function works (although there is a calc error since there isn't any data for the function to operate on).

You could also add this function after you have added the dataframe data (i.e., you don't have to include the formula in the dataframe if you don't want to. See Working with Python Pandas and XlsxWriter for examples on how to do that.