Trying to do the following:
In column G add the following formula:
"=ACOS(COS(RADIANS(90-D2)) *COS(RADIANS(90-D3)) +SIN(RADIANS(90-D2)) *SIN(RADIANS(90-D3)) *COS(RADIANS(E2-E3))) *6371"
Repeat this formula in column G up to the last row with data in columns D and E. I want the cells in the formula to update as formula goes down the rows (i.e. D2 becomes D3...)
Tried the following without success. This will copy the formula in all the rows for column G with data in columns D and E but I am missing the part to update the D and E cells in the formula for each row. My script is incomplete and don't know how to fix it.
for row_num in range(3, max_row+1):
sheet['G{}'.format(row_num)] = '=ACOS(COS(RADIANS(90-D2)) *COS(RADIANS(90-D3)) +SIN(RADIANS(90-D2)) *SIN(RADIANS(90-D3)) *COS(RADIANS(E2-E3))) *6371'.format(row_num)```
I am new with Python but I just figured it out. The following works.
for row_num in range(3, max_row+1): sheet['G{}'.format(row_num)] = '=ACOS(COS(RADIANS(90-D{})) *COS(RADIANS(90-D{})) +SIN(RADIANS(90-D{})) *SIN(RADIANS(90-D{})) *COS(RADIANS(E{}-E{}))) *6371'.format(row_num-1, row_num, row_num-1, row_num, row_num-1, row_num)