I want to use the Excel AVERAGE formula to get the average of columns in a file. I am doing this like so:
font = Font(name="Calibri", bold=True)
ws.cell(100, 1).value = "Mean" # row 100 is the average row
ws.cell(100, 1).font = font
col_range = [2, 3, 4] # the columns to average
for col_index in col_range:
cell = ws.cell(100, col_index)
* cell.value = f"=AVERAGE({cell.column_letter}2:{cell.column_letter}98)"
cell.font = font
wb.save(out_file)
However, when I open the Excel file, the average cells are empty:
As you can see, the cells in row 100 are empty apart from the "Mean" cell. There is no formula in the other cells whatsoever, but the markup of the cell did work (bold text).
When printing the formulas indicated with * in the above code, I see this:
=AVERAGE(B2:B98)
=AVERAGE(C2:C98)
=AVERAGE(D2:D98)
This is how I want it to be.
When I do something like cell.value = "test" instead of the formula, then this does work:
I thought this might be a language issue since my Excel is in Dutch, and AVERAGE=GEMIDDELDE in my language, but even if I use GEMIDDELDE, then this will still result in empty cells.
Any ideas on why this isn't working? I should probably mention that the Excel file I am doing this in is created right before with a script, but I do properly save it and then open it again to enter the formulas.
EDIT: I did just find this in the docs:
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")
NB you must use the English name for a function and function arguments must be separated by commas and not other punctuation such as semi-colons.
If I follow this step by step, then this works. However, this code uses Workbook(), but I use load_workbook(), and then it still doesn't work.
I tried using , instead of ;, but this also didn't work. I think it's weird that you are not supposed to use ;, because isn't the value of a cell just a string? I feel like openpyxl doesn't do anything to it, but yet you're not supposed to use ;.
I also downloaded the English version of Excel to rule out the language. I also tested using , and . as the decimal separators in the cells that have to be averaged, but none of this works either. I feel like this is more an openpyxl issue now; I'm using version 3.0.5. If you use Workbook(), formulas work, but load_workbook() just doesn't work. Any tips would still be appreciated though.
