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