CODEWITHSUNDEEP
python-3.xexcelopenpyxl
Frogknee
Frogknee

Reputation: 1

Openpyxl skipping iteration of for loop

I am using openpyxl on python and I want to sum the rows until they reach a certain number. After reaching that certain number, it would paste the sum on the cell next to it. Then, the loop would skip all the rows that have been added and go on to the cell and do the sum.

To give an idea of what I'm trying to do:excel picture

from openpyxl import load_workbook
import statistics

wb=load_workbook('Algorand.xlsx')
sheet=wb.active
testlist = []

for row in list(sheet.columns)[0]:
testlist= testlist + [row.value]

print(testlist)
avg=statistics.mean(testlist)
std=statistics.stdev(testlist)
target=avg+2*std
length=len(testlist)

for i in range(0,length-2):
   if testlist[i]<target:
   sum= testlist[i]+testlist[i+1]
   print(f"firstsum:{sum}")
   sheet.cell(row=i + 1, column=3, value=sum)

     if sum<target:
        next=i+2
        sum=sum+testlist[next]
        print(f"secsum:{sum}")
        sheet.cell(row=i + 1, column=3, value=sum)

        if sum<target:
           next = next + 1
           sum = sum + testlist[next]
           #next=i
           print(f"thirdsum:{sum}")
           sheet.cell(row=i + 1, column=3, value=sum)

However, I am getting this: code results not desired

How would I go about fixing this

Upvotes: 0

Views: 336

Answers (1)

Mike67
Mike67

Reputation: 11342

Your code seems more complicated than it needs to be to get the result you describe. Just loop through the cells in the first column and add to a total. If the total reaches the limit, write the total then reset it to zero.

Try this code:

from openpyxl import load_workbook
summax = 12  # write sum if reached this total
wb=load_workbook('Algorand.xlsx')
sheet=wb.active

row = 1
ttl = 0
while bool(sheet.cell(row,1).value):  # while not empty cell
   ttl += int(sheet.cell(row,1).value)  # add to sum
   if ttl >= summax:  # if reached limit
      sheet.cell(row,2).value = ttl  # write sum
      ttl = 0   # reset sum
   row += 1  # next row

wb.save('Algorand.xlsx')

Output (limit = 12)

Sum

Upvotes: 1

Related Questions