2

I'm trying to use a formula in Excel using Python and the OpenPyXL library. The aim is to output the following into an excel cell.

=SMALL(IF(G4:BI4>BJ4,G4:BI4),1)

However, when I output the above in excel, the end result has an '@' symbol.

=SMALL(IF(@G4:BI4>BJ4,G4:BI4),1)

The code I'm using is as follows:

col_num += 1
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = f"=SMALL(IF({var_first}:{var_last}>{lowest_offer_cell},{var_first}:{var_last}),1)"

How do I get rid of the '@' symbol and get the correct output?

Frankline
  • 40,277
  • 8
  • 44
  • 75
  • 1
    Does this work for you? https://stackoverflow.com/questions/57298554/how-to-insert-array-formula-in-an-excel-sheet-with-openpyxl/57441549#57441549 – Redox Aug 14 '22 at 15:37
  • The `@` symbol is something Excel uses for certain kinds of formulae. You'll need to check the Excel documentation and possibly reverse engineer the code if this isn't what you want. – Charlie Clark Aug 14 '22 at 17:25
  • @Redox: Yes, that link helps. – Frankline Aug 15 '22 at 09:02
  • Does this answer your question? [How to insert array formula in an Excel sheet with openpyxl?](https://stackoverflow.com/questions/57298554/how-to-insert-array-formula-in-an-excel-sheet-with-openpyxl) – BigBen Jul 26 '23 at 15:17

1 Answers1

2

Answering my own question based on suggestions from this answer.

I added one line to my code.

col_num += 1
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = f"=SMALL(IF({var_first}:{var_last}>{lowest_offer_cell},{var_first}:{var_last}),1)"
worksheet.formula_attributes[cell.coordinate] = {'t': 'array', 'ref': f"{cell.coordinate}:{cell.coordinate}"}

I set the worksheet.formula_attributes to an array formula, then set the ref to the cell's coordinate. Now when I generate the Excel file, the '@' symbol is not included.

Frankline
  • 40,277
  • 8
  • 44
  • 75