Solving for the average values of cells using openpyxl

How can i obtained the average of the values from B2:B1500 as shown in the code below.

wb=xl.load_workbook("J:/Data/T0444CH1.xlsx")
    type(wb)
    ws = wb.active
    tuple(ws['B2':'B1500'])
    cells=[]
    for rowOfCellObjects in ws['B2':'B1500']:
        for cellObj in rowOfCellObjects:
            cells.append(cellObj)
    av = sum(cells)/len(cells)

This is the error i am getting

av = sum(cells)/len(cells)
TypeError: unsupported operand type(s) for +: 'int' and 'Cell'

Answer

In this case, you are adding the Cell Objects with an integer, so in this case, we need to get the cell’s value. So, one way to get the value is to extract it from the object itself with cellObj.value.

By modifying your code, we can do this, namely by appending the value instead of the cell object itself.

wb=xl.load_workbook("J:/Data/T0444CH1.xlsx")
type(wb)
ws = wb.active
tuple(ws['B2':'B1500'])
cells=[]
for rowOfCellObjects in ws['B2':'B1500']:
    for cellObj in rowOfCellObjects:
        cells.append(cellObj.value)
av = sum(cells)/len(cells)

Note: Again, I haven’t tested it.