I'm using the xlsxwriter module from python to write some simulation results into an excel file. Writing the results seems to be working perfectly. But i also need to calculate the "average of the simulation", which can be done by =AVERAGE(CELL:CELL).
Unfortunately this does not seem to work, although the formula is actually in the sheet as "=AVERAGE(A2:A10001)", but the value is just set to 0. If I now copy and paste this cell, containing the formula, to an other cell, it gets executed.
Does anyone know how xlsxwriter can execute this automatically instead of just writing the formula into the cell?
for x in range(0, len(exploit_names)):
worksheet.write(amount_of_runs+1, x , '=AVERAGE(' + chr(65 + x) + '2:' + chr(65 + x)+str(amount_of_runs + 1) + ')')
worksheet.write_formula(amount_of_runs + 1, x,
'=AVERAGE(' + chr(65 + x) + '2:' + chr(65 + x) + str(amount_of_runs + 1) + ')',
cell_format1, "#DIV/0!")
This executed the formula instantly and gave me the correct value for the cells. More workarounds can be found on this Issue Page which @jmcnamara posted in his comment.
Does anyone know how xlsxwriter can execute this automatically instead of just writing the formula into the cell?
From the XlsxWriter FAQ/Known Issues:
Formula results displaying as zero in non-Excel applications
Due to wide range of possible formulas and interdependencies between them XlsxWriter doesn't, and realistically cannot, calculate the result of a formula when it is written to an XLSX file. Instead, it stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.
This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don't have a facility to calculate formulas, such as Excel Viewer, or several mobile applications, will only display the 0 results.
See Formula Results for more details and a workaround.