Did something change with array formulas?

Question

So this has been bugging me as I can't find out the reason they the below formulas no longer works:

=IFERROR(IF(AN:AN="",1-(AQ:AQ/(AE:AE/1.1)),1-(AQ:AQ/(((AE:AE+AN:AN)/1.1)-AP:AP))),"")

This perviously gave us our margin % and still does for older sheets or for users of older versions of excel.

However, those of us using Office 365 are now presented with a #SPILL error.

Coudln't find out way, gave in and re-implemented it using INDIRECT:

=IFERROR(IF(INDIRECT(ADDRESS(ROW(),COLUMN($AN$1),3,1))="",
1-(INDIRECT(ADDRESS(ROW(),COLUMN($AQ$1),3,1))/(INDIRECT(ADDRESS(ROW(),COLUMN($AE$1),3,1))/1.1)),
1-(INDIRECT(ADDRESS(ROW(),COLUMN($AQ$1),3,1))/(((INDIRECT(ADDRESS(ROW(),COLUMN($AE$1),3,1))+INDIRECT(ADDRESS(ROW(),COLUMN($AN$1),3,1)))/1.1)-INDIRECT(ADDRESS(ROW(),COLUMN($AP$1),3,1))))),"")

But given how much bigger that is, its not exactly ideal or easy to proof.

If I could find out why the original formula stopped working, would be greatly appreciatted.

Cheers,

EDIT:

Sample File: https://drive.google.com/open?id=1hIm2e3D2WcFr27JrQX8UE4iqM05AQbZr

AF and AG are the pertinent columns.

Answer 1

This is due to how Excel is implementing the version of implicit intersection we have always had (30+ years) to keep its utility and maintain compatibility while still wiping it out in furtherence of the new way of doing arrays in the program (called dynamic arrays).

That, by the way, is why you get the spill message. Mind you, as it is, I wager it does have an error given that you almost certainly have something else in the column OR perhaps the formula is not in row 1 of its column. Since it is written pretty expansively, covering entire columns (you don't really have over a million rows of dat, right?), it requires an entire column to hold the results.

If you had a column to spare, so to speak, it would be a message about the new wonders you are seeing.

However, that all said, there IS a solution. Edit the formula to be:

=IFERROR(IF(@AN:AN="",1-(@AQ:AQ/(@AE:AE/1.1)),1-(@AQ:AQ/(((@AE:AE+@AN:AN)/1.1)-@AP:AP))),"")

See the "@" symbols in there? That is what we are now to use to force the traditional style of implicit intersection. If you use the formula above, spilling does NOT occur, so there will be no problems in the current spreadsheet. (Also, it is NOT required, at least here, to make it a CSE formula. Just a regular formula, with the "@" symbols added.)

So that will do what you have asked for.

However, given the fact this is NOT going away (not for 30+ more years?), you may want to begin biting the bullet here, in this place, and consider adjusting the formula to a smaller range of cells examined. If you could reasonably have 200,000 rows of data, then make the ranges 225,000 or 250,000 cells. If you do, you can use the spill feature to write the single formula in the top cell in the data area, then let the results spill into a quarter million empty cells. That dynamic array could then be operated upon by the other related functions. Maybe that'd be a wonderful thing.

Since you have systems to consider that will not gain those benefits, maybe, enter this in the appropriate cells, or select the appropriate cells, enter it, and make it a CSE formula. One of the two, the first it seems, would put you EXACTLY back to where you were before this "wonderful thing."

Either way though, perhaps not avoiding things by selecting full column ranges might be a step forward to take along with the above formula. Not preaching, just saying.