Python Excel – Sum, Average, Max, and Date Formulas

Part 5 in the Working with Excel series focuses on formulas.

Background

After completing Part 4 of this series, you should be comfortable using Python and openpyxl to:

• rename worksheets
• reorder worksheets
• delete Worksheets

The j-greats.xlsx file should exist on your system.  If you do not have this particular file, click here for instructions.

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

```from openpyxl import load_workbook

file_name = 'j-greats.xlsx'
w_sheet   = w_book.active```

Calculate using Excel Sum

This example uses Excel’s SUM function to calculate the total of the Est. Worth column (range H2:H12).

```w_sheet['A14'] = 'TOTAL WORTH'
w_sheet['H14'] = '= SUM(H2:H12)'
w_book.save(file_name)
w_book.close()```
• Line [1] creates a label for this row and places this text in cell A14.
• Line [2] adds the result of the SUM function in cell H14. H14.
• Line [3] saves the changes.
• Line [4] closes the open Workbook.

Output

Calculate using Excel Average

This example uses Excel’s AVERAGE function to calculate the average of the Est. Worth column (range H2:H12).

```w_sheet['A15'] = 'AVG. WORTH'
w_sheet['H15'] = '= AVERAGE(H2:H12)'
w_book.save(file_name)
w_book.close()```
• Line [1] creates a label for this row and places this text in cell A15.
• Line [2] adds the result of the AVERAGE function in cell H15.
• Line [3] saves the changes.
• Line [4] closes the open Workbook.

Output

Calculate using Excel Max

This example uses Excel’s MAX function to determine the musician who has the highest Est. Worth column (range H2:H12).

```w_sheet['A16'] = 'MAX EARNER'
w_sheet['H16'] = '= MAX(H2:H12)'
w_book.save(file_name)
w_book.close()```
• Line [1] creates a label for this row and places this text in cell A16.
• Line [2] adds the result of the MAX function in cell H16. H15.
• Line [3] saves the changes.
• Line [4] closes the open Workbook.

Output

Configure Current Date

This example retrieves and formats the current date.

```from datetime import datetime
the_date = datetime.today()
new_date = datetime.strftime(the_date, '%m/%d/%Y')
w_sheet['A18'] = new_date
w_book.save(file_name)
w_book.close()```
• Line [1] imports the `datetime` library. This library is required to retrieve a date.
• Line [2] retrieves the current date (today function) and assigns this to the `the_date` variable.
• Line [3] formats the date (mm/dd/yyyy) and saves this to the `new_date` variable.
• Line [4] places the value of `new_date` in cell A18.
• Line [5], as always, saves the changes.
• Line [6] closes the Workbook.

Output

Summary

• Calculate using Excel Sum
• Calculate using Excel Average
• Calculate using Excel Max
• Configure Current Date

Next Up

Part 6 in Working with Excel will focus on how to style the Worksheet as follows: