libxlsxwriter

In general a formula in Excel can be used directly in the worksheet_write_formula() function:
However, there are a few potential issues and differences that the user should be aware of. These are explained in the following sections.
Excel stores formulas in the format of the US English version, regardless of the language or locale of the enduser's version of Excel. Therefore all formula function names written using libxlsxwriter must be in English:
Also, formulas must be written with the US style separator/range operator which is a comma (not semicolon). Therefore a formula with multiple values should be written as follows:
If you have a nonEnglish version of Excel you can use the following multilingual Formula Translator to help you convert the formula. It can also replace semicolons with commas.
Excel 2010 and later versions added functions which weren't defined in the original file specification. These functions are referred to by Microsoft as "Future Functions". Examples of these functions are ACOT
, CHISQ.DIST.RT
, CONFIDENCE.NORM
, STDEV.P
, STDEV.S
and WORKDAY.INTL
.
When written using worksheet_write_formula()
these functions need to be fully qualified with a _xlfn.
(or other) prefix as they are shown the list below. For example:
They will appear without the prefix in Excel:
The following list is taken from MS XLSX extensions documentation on future functions.
Future Functions 

_xlfn.ACOT 
_xlfn.ACOTH 
_xlfn.AGGREGATE 
_xlfn.ARABIC 
_xlfn.BASE 
_xlfn.BETA.DIST 
_xlfn.BETA.INV 
_xlfn.BINOM.DIST 
_xlfn.BINOM.DIST.RANGE 
_xlfn.BINOM.INV 
_xlfn.BITAND 
_xlfn.BITLSHIFT 
_xlfn.BITOR 
_xlfn.BITRSHIFT 
_xlfn.BITXOR 
_xlfn.CEILING.MATH 
_xlfn.CEILING.PRECISE 
_xlfn.CHISQ.DIST 
_xlfn.CHISQ.DIST.RT 
_xlfn.CHISQ.INV 
_xlfn.CHISQ.INV.RT 
_xlfn.CHISQ.TEST 
_xlfn.COMBINA 
_xlfn.CONCAT 
_xlfn.CONFIDENCE.NORM 
_xlfn.CONFIDENCE.T 
_xlfn.COT 
_xlfn.COTH 
_xlfn.COVARIANCE.P 
_xlfn.COVARIANCE.S 
_xlfn.CSC 
_xlfn.CSCH 
_xlfn.DAYS 
_xlfn.DECIMAL 
ECMA.CEILING 
_xlfn.ERF.PRECISE 
_xlfn.ERFC.PRECISE 
_xlfn.EXPON.DIST 
_xlfn.F.DIST 
_xlfn.F.DIST.RT 
_xlfn.F.INV 
_xlfn.F.INV.RT 
_xlfn.F.TEST 
_xlfn.FILTERXML 
_xlfn.FLOOR.MATH 
_xlfn.FLOOR.PRECISE 
_xlfn.FORECAST.ETS 
_xlfn.FORECAST.ETS.CONFINT 
_xlfn.FORECAST.ETS.SEASONALITY 
_xlfn.FORECAST.ETS.STAT 
_xlfn.FORECAST.LINEAR 
_xlfn.FORMULATEXT 
_xlfn.GAMMA 
_xlfn.GAMMA.DIST 
_xlfn.GAMMA.INV 
_xlfn.GAMMALN.PRECISE 
_xlfn.GAUSS 
_xlfn.HYPGEOM.DIST 
_xlfn.IFNA 
_xlfn.IFS 
_xlfn.IMCOSH 
_xlfn.IMCOT 
_xlfn.IMCSC 
_xlfn.IMCSCH 
_xlfn.IMSEC 
_xlfn.IMSECH 
_xlfn.IMSINH 
_xlfn.IMTAN 
_xlfn.ISFORMULA 
ISO.CEILING 
_xlfn.ISOWEEKNUM 
_xlfn.LOGNORM.DIST 
_xlfn.LOGNORM.INV 
_xlfn.MAXIFS 
_xlfn.MINIFS 
_xlfn.MODE.MULT 
_xlfn.MODE.SNGL 
_xlfn.MUNIT 
_xlfn.NEGBINOM.DIST 
NETWORKDAYS.INTL 
_xlfn.NORM.DIST 
_xlfn.NORM.INV 
_xlfn.NORM.S.DIST 
_xlfn.NORM.S.INV 
_xlfn.NUMBERVALUE 
_xlfn.PDURATION 
_xlfn.PERCENTILE.EXC 
_xlfn.PERCENTILE.INC 
_xlfn.PERCENTRANK.EXC 
_xlfn.PERCENTRANK.INC 
_xlfn.PERMUTATIONA 
_xlfn.PHI 
_xlfn.POISSON.DIST 
_xlfn.QUARTILE.EXC 
_xlfn.QUARTILE.INC 
_xlfn.QUERYSTRING 
_xlfn.RANK.AVG 
_xlfn.RANK.EQ 
_xlfn.RRI 
_xlfn.SEC 
_xlfn.SECH 
_xlfn.SHEET 
_xlfn.SHEETS 
_xlfn.SKEW.P 
_xlfn.STDEV.P 
_xlfn.STDEV.S 
_xlfn.SWITCH 
_xlfn.T.DIST 
_xlfn.T.DIST.2T 
_xlfn.T.DIST.RT 
_xlfn.T.INV 
_xlfn.T.INV.2T 
_xlfn.T.TEST 
_xlfn.TEXTJOIN 
_xlfn.UNICHAR 
_xlfn.UNICODE 
_xlfn.VAR.P 
_xlfn.VAR.S 
_xlfn.WEBSERVICE 
_xlfn.WEIBULL.DIST 
WORKDAY.INTL 
_xlfn.XOR 
_xlfn.Z.TEST 
If there is an error in the syntax of a formula it is usually displayed in Excel as #NAME
?. Alternatively you may get a warning from Excel when the file is loaded. If you encounter an error like this you can debug it as follows:
Finally if you have completed all the previous steps and still get a #NAME
? error you can examine a valid Excel file to see what the correct syntax should be. To do this you should create a valid formula in Excel and save the file. You can then examine the XML in the unzipped file.
The following shows how to do that using Linux unzip
and `libxml's xmllint to format the XML for clarity:
$ unzip myfile.xlsx d myfile $ xmllint format myfile/xl/worksheets/sheet1.xml  grep '<f>' <f>SUM(1, 2, 3)</f>
Libxlsxwriter doesn't calculate the result of a formula and instead 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 will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.
If required, it is also possible to specify the calculated result of the formula using the result
parameter for :func:worksheet_write_formula_num()
: