Using nested formulas in xlsxwrtier

user_9887761 0
Viewed 73

i am trying to generate an excel sheet with python and xlsxwriter and i want to have the following formula in the cell:

=IFERROR(AVERAGE(A1:A10);"")

whereas my german excel would show this as

=WENNFEHLER(MITTELWERT(A1:A10);"")

but i dont really think the language aspect is making a difference.

It works nicely as long as i am doing:

worksheet.write_formula(l+4,1+k,"=AVERAGE(%s%s:%s%s)"%(chr(65+1+k),4,chr(65+1+k),l+4))

what does not work is using nested formulas. Excel always errors upon opening the sheet. I think this should be working:

worksheet.write_formula(l+4,1+k,"=IFERROR(AVERAGE(%s%s:%s%s);0)"%(chr(65+1+k),4,chr(65+1+k),l+4))

I believe i am making either a syntax mistake or hitting a limitation in xlsxwriter. Does somebody know how this works in xlsxwriter.

I was unable to find nested formulas in the docs or any examples of this elsewhere. Is this even possible?

Best,

Nosferuity

xlsxwriter excel python
1 Answers

The formula should work but you'll need to change from semi-colons to commas. See the Working with Formulas section of the XlsxWriter docs where this, and some other differences between Excel formulas are explained:

Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:

    worksheet.write_formula('A1', '=SUM(1, 2, 3)')   # OK
    worksheet.write_formula('A2', '=SUM(1; 2; 3)')   # Semi-colon. Error on load.

There is also a link in that doc to this handy Formula Translator.

Finally you may want to also have a look at the Cell Utility Functions that XlsxWriter provides and which might help you create the range within the formula.

user_102380
Related