Add support for Excel array formulae

Issue #454 resolved
Kris Seraphine
created an issue

Array formulae are displayed in Excel wrapped in braces:

{=MAX(C2:C6-B2:B6)}

Within worksheet.xml they appear as:

<c r="E1">
<f ref="E1" t="array">MAX(C2:C6-B2: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)

  1. CharlieC

    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 = so ws['E1'] = "={MAX(C2:C6-B2:B6)} but any more than that is going to require some thought.

  2. CharlieC

    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).

  3. CharlieC

    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.

  4. CharlieC

    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')

  5. clear water

    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.

  6. Log in to comment