How Can I Get Python Generated Excel Document To Correctly Calculate Array Formulas

I am generating some excel files with python using python 3.6 and openpyxl. At one point I have to calculate standard deviations of a subsection of data. In excel this is done with

Solution 1:

open-pyxl has a bug with the STDEV.P function. add the full name as such:

ws.cell(column=4+i, row=2, value='=_xlfn.STDEV.P(IF(B1:B100={},A1:A100,0))'.format(i+1))

And it will be working correctly.

You can also see it Here.

You can see in the documentation that not all formulae were included:

If you’re trying to use a formula that isn’t known this could be because you’re using a formula that was not included in the initial specification. Such formulae must be prefixed with xlfn. to work.

To check if a formula is included you can use:

from openpyxl.utils import FORMULAE

print"STDEV.P"in FORMULAE

if false, _xlfn. should be put before the formula name for it to work.