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 an array formula. Writing the correct formula is easy, but when I ask excel to calculate - it shows "#NAME?", unless I manually enter each cell and press CTRL+SHIFT+ENTER.
However, prior to doing so, the formula shows the correct curly brackets so I am not certain what I am changing. Since we are talking about quite a few cells manually entering into each one of them is not feasible. Is there a way to make excel or Python execute the formula correctly in excel? I am open to using other libraries or writing a VB script if necessary.

Here is a minimum workable example of the work in python:

from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter
from random import random
from random import randrangewb = Workbook()
ws = wb.create_sheet()
ws.title = 'Data'#generate random data
for i in range(100):ws.cell(column=1, row=1+i, value=random()*100)ws.cell(column=2, row=1+i, value=randrange(1,5))#calculate standard deviations for specific integer values
for i in range(4):ws.cell(column=4+i, row=1, value='STDEV {0}'.format(i+1))ws.cell(column=4+i, row=2, value='=STDEV.P(IF(B1:B100={0},A1:A100," "))'.format(i+1))targetVal = '{0}2'.format(get_column_letter(4+i))ws.formula_attributes[targetVal] = {'t': 'array', 'ref': targetVal}#cleaning up
ws = wb.get_sheet_by_name("Sheet")
wb.remove_sheet(ws)wb.save("fTest.xlsx")

A quick explanation of the example: I generate 100 random numbers between 0 and 100 in column A and integer numbers between 1 and 4 (inclusive) in column B. Below the cell containing STDEV 1, I calculate the standard deviation of all values in column A that have a 1 in column B, and likewise for the other STDEV columns.
The generated excel document should have "#NAME?" in the STDEV.P cells if your excel is set to automatically calculate. If you enter into the formula and execute with CTRL+SHIFT+ENTER the formula executes correctly.

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 FORMULAEprint "STDEV.P" in FORMULAE

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