Sunday, March 8, 2020

Alert -- EXCEL changes for array-entered functions

Microsoft has made some interesting changes for functions that return multiple data items. If you don't array-enter the formula, EXCEL will now automatically expand the range of the formula, "spilling" it into additional rows and columns as needed.

For example, create a new workbook and enter this in cell B2:

=smfPricesByDates("MMM","12/31/2019","03/06/2020")

...and it should automatically fill the results in cells B2 and C2, "spilling" into cell C2.

Now try array-entering it into a single cell, B3, and you will see it doesn't expand the range to include the second returned item.

Now, put "A" in cell C4 and then put that formula in cell B4. It will TRY to expand the range to accommodate the two items being returned, but since C4 already has something in it, EXCEL will return a "#SPILL!" error to indicate it could not "spill" the returned results into more cells.

For the most part, this won't affect many of the SMF add-in array-entered functions. Most are designed to only return enough data to fill the range the function is array-entered over. So if you enter such a formula into a single cell, EXCEL has nothing to spill over.

For example, enter this in cell B6 and you'll only get a single data item (i.e. "Symbol"):

=smfGetYahooPortfolioView("MMM,IBM","01091523",,1)

However, enter this in cell D8 and you'll get a 3x4 range filled as EXCEL spills it over.

=smfGetYahooPortfolioView("MMM,IBM","01091523",,1,3,4)

That's because the last two parameters are telling the function to override it's default of only filling the range it was entered over and instead returning 3 rows and 4 columns of data.

A big advantage of allowing EXCEL to "spill" the function over the range is that it makes it a lot easier to edit. For example, just edit that formula in cell D8 so that the last two parameters are both 10:

=smfGetYahooPortfolioView("MMM,AAPL","01091523",,1,10,10)

After you hit enter, you should see that formula "spill" into a 10 by 10 range. You can edit it again, adding a ticker symbol and a field number:

=smfGetYahooPortfolioView("MMM,AAPL,IBM","0109152324",,1,10,10)

...and it will automatically update what it can in the 10 by 10 range.


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.