I’m trying to replace cells in my Excel worksheet that contains hyphen “-“ with the average value between the above lying cell and the below lying cell. I’ll been trying to do this by looping through each row in column 3
from openpyxl import load_workbook
wb = load_workbook(d_filename)
for i in range(7,31):
if i =="-":
sheet_ranges.cell(row = i, column = 3).value = mean(i-1,i+1)
sheet_ranges.cell(row = i, column = 3).value
wb.save(filename = d_filename)
The reason values are not replaced is that you use
i to check if its equal to
-. i is an index, not the value of a cell. Also to calculate the mean, you are using indices, not the values of top and below cells.
So you could solve this in following way:
def interpolatrion_of_empty_cell(): for i in range(7,31): cell_value = sheet_ranges.cell(row=i, column=3).value if cell_value == "-": top_value = sheet_ranges.cell(row=i+1, column=3).value bottom_value = sheet_ranges.cell(row=i - 1, column=3).value sheet_ranges.cell(row=i, column=3).value = (float(top_value) + float(bottom_value))/2
Not that this may require tweaking, as it does not accout for cases where tob and bottom rows are
-, not numbers, or just empty cells.