Adding strings to a formatted string

Question

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?

I'd suggest using f-strings instead. Also, it's not very good practice to use _ as a variable name when you are actually performing operations on it or using it later. Use an actual descriptive variable name instead. — MattDMo
– MattDMo, Commented Apr 19, 2022 at 20:10
Why do you expect single quotes there? You're not inserting them. If i comes from sheets_to_use, then str of a string is a no-op. — Tim Roberts
– Tim Roberts, Commented Apr 19, 2022 at 20:19
I wasn't actually expecting them, they were just in the output and I'm not sure why. — jc_echo
– jc_echo, Commented Apr 20, 2022 at 13:52

ddejohn · Accepted Answer · 2022-04-19 20:21:39Z

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!

This worked! I'm also not sure why the single quotes were being put around the number in the original but this fixes it. I originally tried join in a loop but it added each character individually. Also, I'm adapting the program here from what someone else wrote, so I'll keep in mind the _ var name as I move through. Thank you!

Collectives™ on Stack Overflow

Adding strings to a formatted string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related