Python | Arithmetic operations in excel file using openpyxl

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, arithmatic operations and plotting graphs. Let’s see how to perform different arithmatic operations using openpyxl.

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:
    average

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:
    quotient

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:
    modulus

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    Output:



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :

1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.