Open In App

Python | Arithmetic operations in excel file using openpyxl

Improve
Improve
Like Article
Like
Save
Share
Report

Prerequisite: Reading & Writing to excel sheet using openpyxl
Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Let’s see how to perform different arithmetic operations using openpyxl. 
 

  • =SUM(cell1:cell2) : Adds all the numbers in a range of cells. 
     

Python3




# import openpyxl module
import openpyxl
 
# Call a Workbook() function of openpyxl
# to create a new blank Workbook object
wb = openpyxl.Workbook()
 
# Get workbook active sheet 
# from the active attribute.
sheet = wb.active
 
# writing to the cell of an excel sheet
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = 400
sheet['A4'] = 500
sheet['A5'] = 600
 
# The value in cell A7 is set to a formula
# that sums the values in A1, A2, A3, A4, A5 .
sheet['A7'] = '= SUM(A1:A5)'
 
# save the file
wb.save("sum.xlsx")


  • Output: 
     

  • =PRODUCT(cell1:cell2) : Multiplies all the numbers in the range of cells.
     

Python3




import openpyxl
 
wb = openpyxl.Workbook()
sheet = wb.active
 
sheet['A1'] = 2
sheet['A2'] = 3
sheet['A3'] = 4
sheet['A4'] = 5
sheet['A5'] = 6
 
# The value in cell A7 is set to a formula
# that multiplies the values in A1, A2, A3, A4, A5 .
sheet['A7'] = '= PRODUCT(A1:A5)'
 
wb.save("product.xlsx")


  • Output: 
     

  • =AVERAGE(cell1:cell2) : It gives the average (arithmetical mean) of all the numbers which is present in the given cell range.
     

Python3




import openpyxl
 
wb = openpyxl.Workbook()
sheet = wb.active
 
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = 400
sheet['A4'] = 500
sheet['A5'] = 600
 
# The value in cell A7 is set to a formula
# that return average of the values in A1, A2, A3, A4, A5 .
sheet['A7'] = '= AVERAGE(A1:A5)'
 
wb.save("average.xlsx")


  • Output: 
     

average

  • =QUOTIENT(num1, num2) : It returns the integer portion of a division.
     

Python3




import openpyxl
 
wb = openpyxl.Workbook()
sheet = wb.active
 
# The value in cell is set to a formula
# that gives quotient value .
sheet['A1'] = '= QUOTIENT(64, 8)'
sheet['A2'] = '= QUOTIENT(25, 4)'
 
wb.save("quotient.xlsx")


  • Output: 
     

quotient

  • =MOD(num1, num2) : Returns the remainder after a number is divided by the divisor.
     

Python3




import openpyxl
 
wb = openpyxl.Workbook()
sheet = wb.active
 
# The value in cell is set to a formula
# that gives remainder or modulus value.
sheet['A1'] = '= MOD(64, 8)'
sheet['A2'] = '= MOD(25, 4)'
 
wb.save("modulus.xlsx")


  • Output: 
     

modulus

  • =COUNT(cell1:cell2) : It counts the number of cells in a range that contain the number.
     

Python3




import openpyxl
 
wb = openpyxl.Workbook()
sheet = wb.active
 
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = 400
sheet['A4'] = 500
sheet['A5'] = 600
 
# The value in cell A7 is set to a formula
# that gives counting of number present in the cells.
sheet['A7'] = '= COUNT(A1:A6)'
 
wb.save("count.xlsx")


  • Output: 
     

 



Last Updated : 19 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads