Adding strings to a formatted string

I’m trying to add on to a string that is going into excel.

sheets_to_use = ["Dermott Wind_Mast 9530", "Dermott Wind_Mast 9531", "Dermott Wind_Mast 9532"]

    for num in range(2,14): # 2 through 13
        formula_string = "=AVERAGE(" + ",".join(["{}!B{}".format(str(i), str(num)) for i in sheets_to_use]) + ")"
        _ = summary_sheet.cell(row=name_row+num, column=3)
        _.value = formula_string
        _.number_format = '#,####0.0000'
        formula_string = "=AVERAGE(" + ",".join(["{}!C{}".format(str(i), str(num)) for i in sheets_to_use]) + ")"
        _ = summary_sheet.cell(row=name_row+num, column=4)
        _.value = formula_string
        _.number_format = '#,####0.0000'

But what’s actually going into my cell in my sheet is:

=AVERAGE(Dermott Wind_Mast '9530'!B2,Dermott Wind_Mast '9531'!B2,Dermott Wind_Mast '9532'!B2)

The single quotes should be around the entire str(i), why is only the number getting the single quotes?

Answer

Not sure I understand the issue, but I also wasn’t able to reproduce your problem. Here’s how you could use f-strings to make your life easier though:

sheets_to_use = ["Dermott Wind_Mast 9530",
                 "Dermott Wind_Mast 9531",
                 "Dermott Wind_Mast 9532"]

for num in range(2,14):
    # Note the use of single quotes inside this f-string:
    sheet_refs = ", ".join(f"'{s}!B{num}'" for s in sheets_to_use)
    formula_string = f"=AVERAGE({sheet_refs})"
    print(formula_string)

Output:

=AVERAGE('Dermott Wind_Mast 9530!B2', 'Dermott Wind_Mast 9531!B2', 'Dermott Wind_Mast 9532!B2')
=AVERAGE('Dermott Wind_Mast 9530!B3', 'Dermott Wind_Mast 9531!B3', 'Dermott Wind_Mast 9532!B3')
=AVERAGE('Dermott Wind_Mast 9530!B4', 'Dermott Wind_Mast 9531!B4', 'Dermott Wind_Mast 9532!B4')
=AVERAGE('Dermott Wind_Mast 9530!B5', 'Dermott Wind_Mast 9531!B5', 'Dermott Wind_Mast 9532!B5')
=AVERAGE('Dermott Wind_Mast 9530!B6', 'Dermott Wind_Mast 9531!B6', 'Dermott Wind_Mast 9532!B6')
=AVERAGE('Dermott Wind_Mast 9530!B7', 'Dermott Wind_Mast 9531!B7', 'Dermott Wind_Mast 9532!B7')
=AVERAGE('Dermott Wind_Mast 9530!B8', 'Dermott Wind_Mast 9531!B8', 'Dermott Wind_Mast 9532!B8')
=AVERAGE('Dermott Wind_Mast 9530!B9', 'Dermott Wind_Mast 9531!B9', 'Dermott Wind_Mast 9532!B9')
=AVERAGE('Dermott Wind_Mast 9530!B10', 'Dermott Wind_Mast 9531!B10', 'Dermott Wind_Mast 9532!B10')
=AVERAGE('Dermott Wind_Mast 9530!B11', 'Dermott Wind_Mast 9531!B11', 'Dermott Wind_Mast 9532!B11')
=AVERAGE('Dermott Wind_Mast 9530!B12', 'Dermott Wind_Mast 9531!B12', 'Dermott Wind_Mast 9532!B12')
=AVERAGE('Dermott Wind_Mast 9530!B13', 'Dermott Wind_Mast 9531!B13', 'Dermott Wind_Mast 9532!B13')

Also, as mentioned in the comments, using _ as a variable name on which you perform operations is bad practice. The _ is used to signify to other readers that the value of the variable is unimportant and will not be used later in any capacity. Always give your variables descriptive names!