Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Increment formula
#1
Hello,

How would I make it so that this formula keeps incrementing for example

'F2','=C2-E2')
'F3','=C3-E3')
'F4','=C4-E4')

This is what I have but it doesn't increment like I want it to as shown above.
ws3.write('F2', '=C2-E2')
Reply
#2
what does actual code look like?
this command ws3.write('F2', '=C2-E2') is I/O
Reply
#3
Here is the code. So the way I currently have it, it does increment from cell F2, to F3, to F4 but the formula doesn't change it just continues with 'C2-E2' and I need it to change as it increments so next would be C3-E3.

import openpyxl as xl 
import os
import pandas as pd
import xlsxwriter
import xlrd


input_dir = 'C:\\work\\comparison\\NNM'
Summary = 'C:\\work\\comparison\\Summary.xlsx'
newFile = 'C:\\work\\comparison\\Comparison.xlsx'




files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]

i=0
ii=0

wb3=xlsxwriter.Workbook(newFile)
ws3=wb3.add_worksheet('Comparison')


format = wb3.add_format()
format.set_align('center')
format.set_align('vcenter')
format.set_bold(True)
format.set_font_size(14)
format.set_bg_color('#C9C9C9')
format.set_border(style=1)


formula=('=C2-E2')

ws3.write_row("A1:Q1", ['Name','Segment','Chainage(ft)','Segment','Chainage(ft)','Difference(ft)','Segment',' Chainage(ft)','Segment','Chainage(ft)','Chainage Difference(ft)','Stops','Stops','Distance','Distance ','Distance Difference (ft)','Comments'], format)
ws3.set_column(1, 17, 35)
wb3.close()

wb3 = xl.load_workbook(newFile) 
ws3 = wb3.worksheets[0]

wb2 = xl.load_workbook(Summary) 
ws2 = wb2.worksheets[1]


for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]
   

   series = pd.read_excel(file,usecols = "F",squeeze = True)
   counts = series.value_counts()
   z = counts[0]/2

   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{ii+6}'].value   
   ws3[f'M{i+2}']=z
   ws3[f'F{i+2}']=formula
   i += 1
   ii +=1          
  


   wb3.save(newFile)
Reply
#4
That's kind of bad design. Better would be to have F, C, and E be lists.
The below can be improved upon, but I quickly threw it together
c = [10,20,30]
e = [5,6,7]
f = [c[i]-e[i] for i in range(len(c))]
print(f)
Output:
[5, 14, 23]
Reply
#5
(Jul-20-2020, 09:44 PM)jefsummers Wrote: That's kind of bad design. Better would be to have F, C, and E be lists.
The below can be improved upon, but I quickly threw it together
c = [10,20,30]
e = [5,6,7]
f = [c[i]-e[i] for i in range(len(c))]
print(f)
Output:
[5, 14, 23]

I don't see how this would work? I have many values that get populated under column C and E. And I need this to run on 100's of files. I just need the formula C2-E2 to increment under column F.

F2 = C2-E2
F3 = C3-E3
F4 = C4-E4

I don't know the values for C and E because the script finds them and pastes them into C and E. Column F is blank and needs the formula.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  help to increment a third list hermine 7 1,269 Nov-29-2022, 04:19 PM
Last Post: perfringo
  mysql id auto increment not working tantony 10 2,304 Oct-18-2022, 11:43 PM
Last Post: Pedroski55
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,639 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  Character Increment AnokhiRaaz 1 2,459 Apr-22-2021, 04:29 AM
Last Post: buran
  Increment text files output and limit contains Kaminsky 1 3,135 Jan-30-2021, 06:58 PM
Last Post: bowlofred
  [openpyxl] Increment cells being pasted into Template Kristenl2784 4 3,507 Jul-16-2020, 10:00 PM
Last Post: Kristenl2784
  How can I increment a List item with in a "for in" msteffes 4 3,485 Aug-14-2019, 08:55 AM
Last Post: DeaD_EyE
  How define iteration interval increment SriMekala 5 4,247 Jun-01-2019, 01:06 PM
Last Post: ichabod801
  SQlite3 quickly increment INT value? jmair 1 2,400 Mar-04-2019, 08:03 PM
Last Post: stranac
  increment variable in while loop Naito 3 2,927 Jan-20-2019, 12:30 PM
Last Post: Naito

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020