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:

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

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

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

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'

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

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")

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.