The xlsx file i'm making needs to be able to calculate sums for it's generated tables, which are printed as cells due to limitations in table styling. This means I have two columns that i want to multiply across a row, and sum down a column, like so: intended table style
my issue is that as i try to make that table, the formula has '@' symbols added to the formula, like so:=SUM(@A2:A10*@B2:B10).
This doesn't occur if you just sum a single reigon (i.e. =SUM(A2:A10)) only when two ranges are getting multiplied. adding _xlfn. to the sum in the script has no impact. Here's the script used to make the excel file in the image:
# Sumfail example
import openpyxl
wb= openpyxl.Workbook()
for row in range(1,10):
wb.active[f'A{row}']=row
wb.active[f'B{row}']=row
wb.active['B10']='=SUM(A1:A9*B1:B9)'
wb.save('killMeNow.xlsx')
Does anyone know why this happens, and how to avoid it? I have a simple solution of find/replacing the @ symbol, but long term that isn't really sustainable.
Edit: found out why - it's treating it as an array formula, despite it having a single cell output. Probably because of that array1*array2 part. Still don't know how to solve this, though.
