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

by

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:

  • add worksheets
  • 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_book    = load_workbook(filename=file_name)
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

The available functions for Excel are too numerous to mention in this article. For a complete list, click here.

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

In this article, we learned how to:

  • 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:

  • Add and Style Heading
  • Add a Logo
  • Style Header Row
  • Style Borders
  • Change Cell Alignment
  • Change Column Width
  • Format Numbers