openpyxl and stdev.p name error

Question

I have a script to format a bunch of data and then push it into excel, where I can easily scrub the broken data, and do a bit more analysis.

As part of this I'm pushing quite a lot of data to excel, and want excel to do some of the legwork, so I'm putting a certain number of formulae into the sheet.

Most of these ("=AVERAGE(...)" "=A1+3" etc) work absolutely fine, but when I add the standard deviation ("=STDEV.P(...)" I get a name error when I open in excel 2013.

If I click in the cell within excel and hit (i.e. don't change anything within the cell), the cell re-calculates without the name error, so I'm a bit confused.

Is there anything extra that needs to be done to get this to work?

Has anyone else had any experience of this?

Thanks,

Will --

Answer 1

I've investigated further and this is the issue:

When saving the formula "STDEV.P" openpyxl saves it as:

"=_xludf.STDEV.P(...)"

which is correct for many formula, but not this one. The result should be:

"=_xlfn.STDEV.P(...)"

When I explicitly change the function to the latter, it works as expected. I'll file a bug report, so hopefully this is done automatically in the future.