'Add values spreadsheet iterating over cells and values to append

I would like to add the following formulas to a spreadsheet

   sheet["D6"] = "=AVERAGE(D3:D5)"
   sheet["E6"] = "=AVERAGE(E3:E5)"
   sheet["F6"] = "=AVERAGE(F3:F5)"
   sheet["G6"] = "=AVERAGE(G3:G5)"
   sheet["H6"] = "=AVERAGE(H3:H5)"
   sheet["I6"] = "=AVERAGE(I3:I5)"
   sheet["J6"] = "=AVERAGE(J3:J5)"
   sheet["K6"] = "=AVERAGE(K3:K5)"
   sheet["L6"] = "=AVERAGE(L3:L5)"
   sheet["M6"] = "=AVERAGE(M3:M5)"
   sheet["N6"] = "=AVERAGE(N3:N5)"
   sheet["O6"] = "=AVERAGE(O3:O5)"
   sheet["P6"] = "=AVERAGE(P3:P5)"

This is so repeated and I am found that I can also use a loop like this

>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...    for cell in row:
...        print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>

to achieve what I need

python openpyxl

Solution 1:^[1]

...

avg_row_start = 3  # row start of Averaging
avg_row_end = 5    # row end of Averaging
avg_totals_row = 6 # row where the Averaging formulas reside
start_col = 4      # col of first formula 'D' = 4
end_col = 16       # col of last formula 'P' = 16
for row in ws.iter_rows(min_row=avg_totals_row, max_row=avg_totals_row, min_col=start_col, max_col=end_col):
    for cell in row:
        avg_start = cell.column_letter + str(avg_row_start) # Cell coord for start avg range
        avg_end = cell.column_letter + str(avg_row_end)     # Cell coord for end avg range 
        cell.value = '=AVERAGE({0}:{1})'.format(avg_start, avg_end)  #  wite avg formula

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	moken

'Add values spreadsheet iterating over cells and values to append

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]