How to prevent Excel to randomly add the @ character in my formulas

Question

Context

I use xlsxwriter to write some data into an xlsx file from a python code.
I need to fill a cell with a formula that calculates the average of a specific column (that contains time values).

Problem Description

I noticed that when I try to open my document with Excel, it automatically adds an '@' symbol right before my range which makes my formula calculation fail...
If I manually remove this extra '@', the formula works perfectly as expected.

It is really annoying to have to manually fix my formula like that since I have a lot of similar average calculations that are broken because of this.
I read that it is Excel that adds it so it seems to be no way to prevent this undesired behaviour programmatically from my python code with the xlsxwriter API.

Question

Does someone know if there is a way to prevent Excel to perform any modification of my formulas ?

What I have tried so far

I already tried to "protect" all of my sheets (make them read-only) but it seems to apply only for the user, not for Excel...

Example

Here is an example of my issue (to make it more understandable that this load of plain text).

From my python code, if I write the following formula:

=AVERAGE(TIMEVALUE(B2:B8))

After opening the document, Excel automatically changes the formula as:

=AVERAGE(TIMEVALUE(@B2:B8))

Which make the result of the calculation become:

#VALUE!

If I undo what Excel does by manually restoring my formula (manually edit the cell and remove the '@'), I get the desired calculated average value as I expected it to be.

@BigBen Yes I already have found that kind of answers but it's in VBA. — Fareanor
@Fareanor @ is the implicit intersection operator. BigBen's point was that the solution to your problem is to write to .Formula2 rather than .Formula. If xlswriter doesn't (yet) support that, it a failing of xlswriter, not Excel. — chris neilsen

jmcnamara jmcnamara · Accepted Answer · 2021-04-19T22:53:56

In general if Excel inserts a @ in a formula written by XlsxWriter it means that formula is an array formula and should have been written with worksheet.write_array_formula() (although there are some caveats, see below).

So with a variant of your example:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_array_formula('A1', '=AVERAGE(TIMEVALUE(B1:B6))')

worksheet.write_column('B1', ['2:30'] * 6)

workbook.close()

Output:

However, recent versions of Excel have introduced dynamic formulas and dynamic arrays. I think what you are seeing here is the Implicit intersection operator: @. I am currently working on support for dynamic formulas and arrays in XlsxWriter but it is still under investigation. Also, it is worth noting that although the formula above works it doesn't appear as it would in Excel 365 since there are {} braces to indicate an array formula. Those aren't shown in Excel 365. But as I said, I'm working on it.

How to prevent Excel to randomly add the @ character in my formulas

1 Answers