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:

  • add worksheets
  • rename worksheets
  • reorder worksheets
  • delete Worksheets

Remember to add the Required Starter Code (lines [1-4]) to the top of each code snippet. This snippet will allow the code in this article to run error-free.

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

Required starter code:

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