Automatic change of regular formulas to array formulas between different Excel 2016 versions

Hello,

When I open a file made in Excel 2016 (Excel for Office 365 MSO (16.0.12527.202060) 64-bits) in another version of Excel 2016 (Excel 2016 (16.0.4966.1000) MSO (16.0.4939.1000) 32-bit or Microsoft Excel for Office 365 MSO (16.0.11929.20618) 64-bits), some formulas automatically change to array formulas (curly brackets { and } are added), causing the file to stop working properly. If I remove these brackets, the formula works as it should again. If I save the file and open it in the first version of Excel again, @ signs are added to the formula. Saving it once more and opening it in the second version of Excel again then yields no problems, it still works.

This happens to the formulas on several rows, but removing the curly brackets in one particular formula seems to fix everything. More specifically, the formula I'm talking about goes through these changes:

Original formula created in Excel for Office 365 MSO (16.0.12527.202060) 64-bits:
=IF(ISNUMBER(INDIRECT(ADDRESS(ROW($C$45),G37-F53+COLUMN()-G37),TRUE)),INDIRECT(ADDRESS(ROW($C$45),G37-F53+COLUMN()-G37),TRUE),"")

Automatically altered formula after opening the same file in Excel 2016 (16.0.4966.1000) MSO (16.0.4939.1000) 32-bit:

{=IF(ISNUMBER(INDIRECT(ADDRESS(ROW($C$45);G37-F53+COLUMN()-G37);TRUE));INDIRECT(ADDRESS(ROW($C$45);G37-F53+COLUMN()-G37);TRUE);"")}

Automatically altered formula after removing the curly brackets above, saving the file and opening it in Excel for Office 365 MSO (16.0.12527.202060) 64-bits again:

=@IF(ISNUMBER(@INDIRECT(ADDRESS(ROW($C$45),G37-F53+COLUMN()-G37),TRUE)),INDIRECT(ADDRESS(ROW($C$45),G37-F53+COLUMN()-G37),TRUE),"")

I do not understand why this happens to some formulas. Since it potentially causes the file not to work at all, I would like to learn what's going on.

Thanks in advance,

Kind regards
|
Might be related to https://superuser.com/questions/1471013/in-the-beginning-of-a-formulaDynamic Array formulas

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated April 19, 2022 Views 477 Applies to: