openpyxl Part 11 – Formulae

In the previous part of the openpyxl series we learned how to freeze rows and columns. Today we’ll see how to add formulae in a workbook. We’ll be still working on the wb2.xlsx workbook. Here’s the data our workbook contains:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474.00	40

As usual, let’s start by loading the workbook:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active

There are lots of formulae that you can use. Just import the FORMULAE frozenset set from openpyxl.utils module and check them out:

>>> from openpyxl.utils import FORMULAE
>>> sorted(FORMULAE)
['ABS', 'ACCRINT', 'ACCRINTM', 'ACOS', 'ACOSH', 'AMORDEGRC', 'AMORLINC', 'AND', 'AREAS', 'ASC', 'ASIN', 'ASINH', 'ATAN', 'ATAN2', 'ATANH', 'AVEDEV', 'AVERAGE', 'AVERAGEA', 'AVERAGEIF', 'AVERAGEIFS', 'BAHTTEXT', 'BESSELI', 'BESSELJ', 'BESSELK', 'BESSELY', 'BETADIST', 'BETAINV', 'BIN2DEC', 'BIN2HEX', 'BIN2OCT', 'BINOMDIST', 'CEILING', 'CELL', 'CHAR', 'CHIDIST', 'CHIINV', 'CHITEST', 'CHOOSE', 'CLEAN', 'CODE', 'COLUMN', 'COLUMNS', 'COMBIN', 'COMPLEX', 'CONCATENATE', 'CONFIDENCE', 'CONVERT', 'CORREL', 'COS', 'COSH', 'COUNT', 'COUNTA', 'COUNTBLANK', 'COUNTIF', 'COUNTIFS', 'COUPDAYBS', 'COUPDAYS', 'COUPDAYSNC', 'COUPNCD', 'COUPNUM', 'COUPPCD', 'COVAR', 'CRITBINOM', 'CUBEKPIMEMBER', 'CUBEMEMBER', 'CUBEMEMBERPROPERTY', 'CUBERANKEDMEMBER', 'CUBESET', 'CUBESETCOUNT', 'CUBEVALUE', 'CUMIPMT', 'CUMPRINC', 'DATE', 'DATEDIF', 'DATEVALUE', 'DAVERAGE', 'DAY', 'DAYS360', 'DB', 'DCOUNT', 'DCOUNTA', 'DDB', 'DEC2BIN', 'DEC2HEX', 'DEC2OCT', 'DEGREES', 'DELTA', 'DEVSQ', 'DGET', 'DISC', 'DMAX', 'DMIN', 'DOLLAR', 'DOLLARDE', 'DOLLARFR', 'DPRODUCT', 'DSTDEV', 'DSTDEVP', 'DSUM', 'DURATION', 'DVAR', 'DVARP', 'ECMA.CEILING', 'EDATE', 'EFFECT', 'EOMONTH', 'ERF', 'ERFC', 'ERROR.TYPE', 'EVEN', 'EXACT', 'EXP', 'EXPONDIST', 'FACT', 'FACTDOUBLE', 'FALSE', 'FDIST', 'FIND', 'FINDB', 'FINV', 'FISHER', 'FISHERINV', 'FIXED', 'FLOOR', 'FORECAST', 'FREQUENCY', 'FTEST', 'FV', 'FVSCHEDULE', 'GAMMADIST', 'GAMMAINV', 'GAMMALN', 'GCD', 'GEOMEAN', 'GESTEP', 'GETPIVOTDATA', 'GROWTH', 'HARMEAN', 'HEX2BIN', 'HEX2DEC', 'HEX2OCT', 'HLOOKUP', 'HOUR', 'HYPERLINK', 'HYPGEOMDIST', 'IF', 'IFERROR', 'IMABS', 'IMAGINARY', 'IMARGUMENT', 'IMCONJUGATE', 'IMCOS', 'IMDIV', 'IMEXP', 'IMLN', 'IMLOG10', 'IMLOG2', 'IMPOWER', 'IMPRODUCT', 'IMREAL', 'IMSIN', 'IMSQRT', 'IMSUB', 'IMSUM', 'INDEX', 'INDIRECT', 'INFO', 'INT', 'INTERCEPT', 'INTRATE', 'IPMT', 'IRR', 'ISBLANK', 'ISERR', 'ISERROR', 'ISEVEN', 'ISLOGICAL', 'ISNA', 'ISNONTEXT', 'ISNUMBER', 'ISO.CEILING', 'ISODD', 'ISPMT', 'ISREF', 'ISTEXT', 'JIS', 'KURT', 'LARGE', 'LCM', 'LEFT', 'LEFTB', 'LEN', 'LENB', 'LINEST', 'LN', 'LOG', 'LOG10', 'LOGEST', 'LOGINV', 'LOGNORMDIST', 'LOOKUP', 'LOWER', 'MATCH', 'MAX', 'MAXA', 'MDETERM', 'MDURATION', 'MEDIAN', 'MID', 'MIDB', 'MIN', 'MINA', 'MINUTE', 'MINVERSE', 'MIRR', 'MMULT', 'MOD', 'MODE', 'MONTH', 'MROUND', 'MULTINOMIAL', 'N', 'NA', 'NEGBINOMDIST', 'NETWORKDAYS', 'NETWORKDAYS.INTL', 'NOMINAL', 'NORMDIST', 'NORMINV', 'NORMSDIST', 'NORMSINV', 'NOT', 'NOW', 'NPER', 'NPV', 'OCT2BIN', 'OCT2DEC', 'OCT2HEX', 'ODD', 'ODDFPRICE', 'ODDFYIELD', 'ODDLPRICE', 'ODDLYIELD', 'OFFSET', 'OR', 'PEARSON', 'PERCENTILE', 'PERCENTRANK', 'PERMUT', 'PHONETIC', 'PI', 'PMT', 'POISSON', 'POWER', 'PPMT', 'PRICE', 'PRICEDISC', 'PRICEMAT', 'PROB', 'PRODUCT', 'PROPER', 'PV', 'QUARTILE', 'QUOTIENT', 'RADIANS', 'RAND', 'RANDBETWEEN', 'RANK', 'RATE', 'RECEIVED', 'REPLACE', 'REPLACEB', 'REPT', 'RIGHT', 'RIGHTB', 'ROMAN', 'ROUND', 'ROUNDDOWN', 'ROUNDUP', 'ROW', 'ROWS', 'RSQ', 'RTD', 'SEARCH', 'SEARCHB', 'SECOND', 'SERIESSUM', 'SIGN', 'SIN', 'SINH', 'SKEW', 'SLN', 'SLOPE', 'SMALL', 'SQRT', 'SQRTPI', 'STANDARDIZE', 'STDEV STDEVA', 'STDEVP', 'STDEVPA STEYX', 'SUBSTITUTE', 'SUBTOTAL', 'SUM', 'SUMIF', 'SUMIFS', 'SUMPRODUCT', 'SUMSQ', 'SUMX2MY2', 'SUMX2PY2', 'SUMXMY2', 'SYD', 'T', 'TAN', 'TANH', 'TBILLEQ', 'TBILLPRICE', 'TBILLYIELD', 'TDIST', 'TEXT', 'TIME', 'TIMEVALUE', 'TINV', 'TODAY', 'TRANSPOSE', 'TREND', 'TRIM', 'TRIMMEAN', 'TRUE ADDRESS', 'TRUNC', 'TTEST', 'TYPE', 'UPPER', 'VALUE', 'VAR', 'VARA', 'VARP', 'VARPA', 'VDB', 'VLOOKUP', 'WEEKDAY', 'WEEKNUM', 'WEIBULL', 'WORKDAY ', 'WORKDAY.INTL', 'XIRR', 'XNPV', 'YEAR', 'YEARFRAC', 'YIELD', 'YIELDDISC', 'YIELDMAT', 'ZTEST']
>>>

As you can see, they are way too many to cover even a substantial part of them here. But let’s at least have a look at some examples.

First let’s add some text so that we know what the numbers mean. In column A, starting from row 21, let’s add the following strings:

>>> sheet['a21'] = 'SUM'
>>> sheet['a22'] = 'MIN'
>>> sheet['a23'] = 'MAX'
>>> sheet['a24'] = 'AVERAGE'
>>> sheet['a25'] = 'BIG COMPANIES'

Now we’ll use the formulae in the number_of_employees column, so culumn E. We want to know the following:

– how many employees there are in all the companies altogether:

>>> sheet['e21'] = '=SUM(e2:e11)'

– what’s the minimum number of employees:

>>> sheet['e22'] = '=MIN(e2:e11)'

– what’s the maximum number of employees:

>>> sheet['e23'] = '=MAX(e2:e11)'

– what’s the average number of employees:

>>> sheet['e24'] = '=AVERAGE(e2:e11)'

– how many big companies there are, so companies with more than 40 workers:

>>> sheet['e25'] = '=COUNTIF(e2:e11, ">40")'

Now let’s save the workbook:

>>> workbook.save(filename="wb2.xlsx")

And let’s open it. You should see all the formulae having taken effect:

If you click on a cell with a formula (A), you will see the formula that you used (B):

In the next part of the openpyxl series we’ll see how to add styles.