Excel adds bracket to formula when sent to another person

[jbesclapez]

Hello,

I have a file with a formula. It is a normal formula, not an array one.
When I send it to a colleague then the formula has curly brackets at the begining and send.
We tried disabling all ADDINS and COMS but it is the same.
We tried in safe mode but it is the same.
When openened from Office.com then it is good with no curly brackets.

Now when I sent it to a third colleague. It displays correctly.

I have no idea what could create that change? Anyone has an idea? We are all on 365 and computer have different regional settings.

Thanks

 

[jasonb75]

Best guess would be that the other user is behind on updates. Excel has interpreted it as an array formula, you have the dynamic array update but they don't.

 

[jbesclapez]

Thanks Jason. Will investigate that.

FYI, the user re created the file from scratch on her pc and it is working. No idea why.

 

[jasonb75]

the user re created the file from scratch on her pc and it is working

If my theory is correct then it is interpreting the dynamic array from your version as a legacy array in her version (Ctrl Shift Entered array). Because her version doesn't have dynamic arrays then it will be set however it is entered.

This would apply to either a formula that dynamically fills in your version (enter it to one cell and excel fills enough cells to show all results), which is unlikely as it wouldn't work in your colleagues version if not array entered, or to a formula that can be interpreted as an array with functions that don't support arrays by default.

Without seeing at least one formula as an example, this is nothing more than best guess.

 

[jbesclapez]

If my theory is correct then it is interpreting the dynamic array from your version as a legacy array in her version (Ctrl Shift Entered array). Because her version doesn't have dynamic arrays then it will be set however it is entered.

This would apply to either a formula that dynamically fills in your version (enter it to one cell and excel fills enough cells to show all results), which is unlikely as it wouldn't work in your colleagues version if not array entered, or to a formula that can be interpreted as an array with functions that don't support arrays by default.

Without seeing at least one formula as an example, this is nothing more than best guess.

Jason here is the guilty formula :

=IF(INDIRECT("'Fill Data'!A"&ROW(A5))="","",INDIRECT("'Fill Data'!A"&ROW(A5)))

This formula is recopied in a massive range on all cells... but even when i try with a single cell it still does it!

 

[jasonb75]

A compatibility check on the formula indicates that it has potential to spill, which means it is a dynamic array in supported versions. Whilst I don't have a source to back it up, this does support my earlier theory.

Although this actual formula will not spill, if written slightly differently it could easily do so. This could theoretically apply to many formulas, so I'm thinking that perhaps there are certain functions which are assumed to spill based on previous usage of them. the ROW() function is often used to create sequential arrays in older versions of excel, which could be one possible reason.

FYI, you could use INDEX('Fill Data'!A:A,ROW(A5)) instead, which would be non-volatile and more efficient. INDIRECT is one of several functions best avoided when and where possible.

 

[jbesclapez]

Got more info :
I am on Excel 365 MSO 16.0.13127.21336 64bit and the other person is on 365 MSO 16.0.11328.20512 32bit.
But both versions are after 2018!
Could it be because it is a 32bit?

 

[jasonb75]

Could it be because it is a 32bit?

That would make no difference, it would be the installed update and the subscription channel. Not everyone gets the same updates at the same time.

Your version number is not coming up on the update history list, your colleagues version shows to be from Jan 2020. At that point, many not all releases included the dynamic array update.