Add support for Excel array formulae
Array formulae are displayed in Excel wrapped in braces:
{=MAX(C2:C6B2:B6)}
Within worksheet.xml they appear as:
<c r="E1"> <f ref="E1" t="array">MAX(C2:C6B2:B6)</f> <v> </v> </c>
There is currently no way to add an array formula to a cell. The request is to provide a mechanism to do so.
Thanks for all your efforts.
Comments (9)


Thanks very much for the example. The formula is stored as
IFERROR(INDEX(POTS_TARGETS_TG, SMALL(IF(ISBLANK(POTS_TARGETS_TG), "", ROW(POTS_TARGETS_TG)MIN(ROW(POTS_TARGETS_TG))+1), ROW(A1))), IFERROR(INDEX(POTS_TARGETS_VP, SMALL(IF(ISBLANK(POTS_TARGETS_VP), "", ROW(POTS_TARGETS_VP)MIN(ROW(POTS_TARGETS_VP))+1), ROW(A1)SUMPRODUCT(NOT((ISBLANK(POTS_TARGETS_TG)))))), ""))
How does this compare with formula as entered? (I can only see it in German which makes the comparison pretty tricky).

I've been looking at the way this is done in Excel and I don't think that using special syntax is the way to do this. If you need to use an array formula this can be done already using the
formula_attributes
dictionary.ws.formula_attributes['B4'] = {'t': 'array', 'ref': 'B4:B8'}
The formula is assigned to the cell in the normal way. We might be able to clean up the interface here to support other formula and maybe make it a cell method.

reporter This works perfectly for my needs.
Thank you very much!

Great, I'll close the ticket then. The semantics might change in the future to be more explicit: something like
ws.array_formula.add('B4', 'B4:B8')

 changed status to resolved
Turns out this is already possible with ws.formula_attributes.

 removed version
Removing version: NEXT (automated comment)

I am having trouble getting array formulas to work.
Say I have:
ws.formula_attributes['Q5'] = {'t': 'array', 'ref': 'Q5'}
I have tried various arguments for the 'ref' value under formula_attributes, but when I try to open the workbook it is corrupted and has to be repaired, and then the formula is not there.

@_clearwater questions should be asked on the mailing list.
 Log in to comment
Can you add a file with a couple of examples? On the mailing list John mentioned returned the possibility of multiple return parameters. Are they included in the example? If so we might manage a quick hack with the curly brackets placed around the formula after the
=
sows['E1'] = "={MAX(C2:C6B2:B6)}
but any more than that is going to require some thought.