Excel 2016 automatically converting formula to "array formula"

%3CLINGO-SUB%20id%3D%22lingo-sub-1629676%22%20slang%3D%22en-US%22%3EExcel%202016%20automatically%20converting%20formula%20to%20%22array%20formula%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1629676%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20run%20into%20this%20weird%20thing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20regular%20formula%20in%20a%20file%20saved%20with%20Excel%20365%3C%2FP%3E%3CP%3E%3Dor(inventory1%3D0%2C%20inventory2%3D0%2C%20inventory3%3D0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...when%20opened%20in%20Excel%202016%20is%20transformed%20into%20an%20array%20formula%3C%2FP%3E%3CP%3E%7B%3Dor(inventory1%3D0%2C%20inventory2%3D0%2C%20inventory3%3D0)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cell%20is%20in%20a%20criteria%20range.%20inventory1%7C2%7C3%20are%20not%20defined%20names%20and%20the%20result%20showing%20for%20the%20cell%20is%20%23NAME%3F%20(which%20is%20always%20the%20case%20in%20criteria%20range%20cells%20with%20formula%20that%20refers%20to%20columns%20in%20the%20range%20to%20be%20filtered)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20the%20advanced%20filter%20is%20not%20working%20in%202016%20because%20of%20the%20array%20formula.%20I%20can%20correct%20the%20formula%20(getting%20rid%20of%20the%20curly%20brackets%20and%20array%20formula)%2C%20and%20it%20works%2C%20fine....until%20the%20worksheet%20is%20opened%20with%20Excel365%20and%20saved.%20It%20will%20still%20be%20a%20regular%20formula%20in%20365%2C%20but%20Excel%202016%20will%20read%20an%20array%20formula%20next%20time%20it%20opens%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-R%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1629676%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

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 

 

0 Replies