Excel 2016 automatically converting formula to "array formula"

[BilloCL]

Hi All.

I've run into this weird thing.

This regular formula in a file saved with Excel 365

=or(inventory1=0, inventory2=0, inventory3=0)

...when opened in Excel 2016 is transformed into an array formula

{=or(inventory1=0, inventory2=0, inventory3=0)}

The cell is in a criteria range. inventory1|2|3 are not defined names and the result showing for the cell is #NAME? (which is always the case in criteria range cells with formula that refers to columns in the range to be filtered)

Of course, the advanced filter is not working in 2016 because of the array formula. I can correct the formula (getting rid of the curly brackets and array formula), and it works, fine....until the worksheet is opened with Excel365 and saved. It will still be a regular formula in 365, but Excel 2016 will read an array formula next time it opens it.

-R 

[#2]

@BilloCL I am not familiar with this happening. But if it happens to you, I will take a stab at an idea. After opening in 365, when ready to save, you might try specifying to "Save as" in a particular format. Worst case, you could save in "Excel 97-2003 Workbook" format which doesn't support newer features. Only you can determine whether this cure is worse than the disease! You could either leave it in this format, or, on the other computer, "Save as" back into the "normal" Excel workbook format.

Another possible thing to try is to write an Excel macro to remove the surrounding curly braces from all array formulas. Run this macro before saving (or have the macro itself finish by doing the save operation). I don't know if this will actually work, but it might be worth a try.