How do you confirm array formulas that spill in older MS Office versions?

[HansDouwe]

I have a theoretical question about confirming a formula that spills in older MS Office versions.

When you create an array formula that spills, in which MS Off versions should the maximum spill area be selected first before confirming the formula with Ctrl-Shift-Enter?

And in which MS Off versions (if any) is it only necessary to select the cell containing the formula before confirming the formula with Ctrl-Shift-Enter?

I would like to know the answers for Office 2007, 2010, 2013, 2016 and 2019.

Note: This question is only about array formulas that (can) spill.

[MrShorty]

My personal experience extends to 2013 (my main version is 2007). I must select all the cells expected to have a result and confirm with ctrl-shift-enter when entering array formulas that return multiple results in all of those versions.

From what I gather in the forum, I am confident that 2019 supports spilling without ctrl-shift-enter. I am not certain about 2016.

[AliGW]

As far as I am aware, nothing will spill in any of those versions. I believe the oldest version where spilling can occur (with the introduction of FILTER) is 2021.

[DJunqueira]

When did Excel introduce spill?
September, 2018
In September, 2018 we introduced dynamic array formulas. Dynamic array formulas can automatically populate or "spill" into neighboring blank cells, and eliminate the need for legacy Ctrl+Shift+Enter (CSE) array formulas.

Does Excel 2016 support dynamic arrays?
Dynamic Arrays are only available on Microsoft 365 versions of Excel (Current Channel). It's not available on Excel 2019/2016 (or earlier versions). If you save a worksheet with Dynamic Arrays and somebody tries to use the sheet on an older version of Excel, the Dynamic Arrays will be converted to traditional Arrays.8 de ago. de 2023

[HansDouwe]

Thanks for all the responses.

But I don't think any response has given the entire answer yet.

Even Excel 2007 already has a function that Spills:
That is TRANSPOSE. See https://support.microsoft.com/en-us/...9-4b6dfac76027.
This formula only works in Excel 2007 to 2016 when the maximum spill area is selected in advance and the formula is only then confirmed with Crtr-Shift-Enter.
Behind the scenes, something is recorded in all cells of the possible spill area, making it look as if the formula is spilling.

I thought what applies to TRANSPOSE in older Excel versions also applies to other formulas that can spill.
Or am I wrong here?

For example, what about =ROW(1:10) or =COLUMN(A:Z) in versions 2007-2016.
Or what about =A1:A10?''

The question also remains to what extent Ctrl-Shift-Enter is still necessary in Excel 2019 and if so in what way (with or without selection of the maximum spill area)?

[MrShorty]

Maybe I don't understand what you mean by "spill." TRANSPOSE() is far from the only multi-cell array function in 2007. Others include the other matrix manipulation formulas (MINVERSE(), MMULT()), LINEST(), and FREQUENCY(). There might be others; those are just the ones I use with some regularity. In order to use these functions, I select the desired output range, enter the function, and confirm with ctrl-shift-enter. The function fills the selected range with its outputs. If the range I selected is too small, it will fill the selected range and discard the values that don't fit. If I select a range that is too large, it returns #N/A# error in the extra cells. Excel keeps careful track of these "arrays" and, if I ever try to edit only a portion of the array (like inserting cells/rows/columns in the middle of the array), I get an array message that I cannot edit only part of an array.

As for the other examples (=ROW(1:10), =COLUMN(A:Z), =A1:A10), I can use those examples like the array functions that they are. If I select E21:E30 and ctrl-shift-enter =A1:A10, I get a copy of the values in A1:A10 in E21:E30. I actually find this, coupled with the error message I mentioned, useful when I want to make a copy of A1:A10 elsewhere in the sheet. When A1:A10 represents a "set" of values (like the output of a LINEST() regression), I may use =A1:A10 to make a copy of the regression parameters, and, because Excel knows it's an array, I am prevented from accidentally changing one of the values in that copy.

I'm not sure if that helps. I think, what you are calling "spill" behavior, I think of as "array" behavior in my older versions. There are some significant differences in how these features are implemented, but the end result is the same -- the ability to have a formula output multiple values to multiple cells.

Does that help?

[HansDouwe]

This certainly helps Mr Shorty.

Regarding the ROW and COLUMN functions, I have found a video from 2008 that confirms this.

This video shows how the formulas ROW and COLUMN can spill in Excel 2007.

See the attached video from 2008 (from 0:46 to 1:02): https://www.youtube.com/watch?v=fRdpEhNAm_k&t=48s

[HansDouwe]

To be precise, with Spill formulas I mean calculations that operate on an array of items and which deliver an array of answers called a "resultant array"

In the meantime, I have found a video from 2016, which addresses my question in great detail for Excel 2016. See https://www.youtube.com/watch?v=RDP1uF7HafU2

It also appears that in some situations formulas can spill in Excel 2016 without the need for confirmation with Ctrl+Shift+Enter.

[HansDouwe]

Ali, DJunqueira and MrShorty, thank you very much for your contributions.

Your answers together with the two videos about Excel 2007 and Excel 2016 on this subject that I found later (see post #7 and #8) sufficiently answered my questions for Excel 2007 through Excel 2016.

The question remains how exactly this works in Excel 2019?
Maybe Pete_UK as guru of Excel 2019 or someone else can say more about this?

[DJunqueira]

In this video of Mr.Excel he talks about spill array functions 5 years ago, but only for Excel 365, not for Excel 2019 and I think this makes us confuse, because I didn't use Excel 2019, but I know that spill array formulas are here for a good amount of time as I jumped from Excel 2016 to Excel 365.

https://www.youtube.com/watch?v=ZmLu0vMRrGs

[HansDouwe]

... about spill array functions 5 years ago, but only for Excel 365 ...[/URL]

Thanks, I know how things work in 365. My question is about spilling into older versions, as I already showed in Post #7 and Post #8.

[AliGW]

Regarding CSE, my understanding remains that it must be used in 2019 and earlier. Only in 2021 and 365 can 'array' formulae be entered without it. My experience with members here would seem to confirm this to me.

[HansDouwe]

... it must be used in 2019 and earlier. Only in 2021 and 365 can 'array' formulae be entered without it.

My question here is only about array formulas that spills. The video from Post #7 shows that this is already possible in Excel 2007 by first selecting an area that is (at least) as large as the SPILL area and then pressing CSE. The video in Post #8 shows that a formula in 2016 also can SPILL without CSE.

[Glenn Kennedy]

I can only think of ONE function (that I ever had cause to use) which required selecting the output RANGE, before CSE. LINEST.

There may be others, but I never used them. In all cases where I encountered arrays, I CSE'd the formula in a single cell and dragged it down.

Excel has a "compatability checker" which can "tell" you if a formula needs CSE in earlier Excel versions. It doesn't work properly, so forget it!

On a side issue, it seems that sometimes SUMPRODUCT entered normally on O365 (by me) shows up on John Topley's 2010 Excel as CSE.

Excel is home to more bugs than a compost heap.

[HansDouwe]

I can only think of ONE function (that I ever had cause to use) which required selecting the output RANGE, before CSE. LINEST.
There may be others ...

Indeed, in the meantime there appear to be more functions in older Excel versions that allow or require this to be done in this way. Such as =ROW, =COLUMN and =TRANSPOSE. And I'm also thinking of =MMULT and =FREQUENCY (all functions that can already spill in Excel 2007)

[AliGW]

I used TRANSPOSE with older versions and selected the range first, but that was the only one for me.

Lots of grey areas here!

[HansDouwe]

Lots of grey areas here!

Certainly. I have always found that Microsoft is not clear and complete in describing the possibilities and impossibilities in Excel. Very different from many other programming languages, where the possibilities of the language are shown in a very formal way. This means that it is not possible to look up matters for Excel in formal documentation. At least not in the sources that I know of.

[Glenn Kennedy]

That was the INPUT range you selected... not the OUTPUT range though.

[HansDouwe]

That was the INPUT range you selected... not the OUTPUT range though.

I saw a video about TRANSPOSE in an older Excel version, where the OUTPUT range had to be selected before CSE. As soon as I find that video again, I will post it here.

[AliGW]

Possibly, but it's the same principle of range definition by selection.

[Glenn Kennedy]

Is it? No coffee onboard yet. However, by your definition, wouldn't =SUMIFS(A:A,B:B,">2",C:C,">3") then also count.

[HansDouwe]

wouldn't =SUMIFS(A:A,B:B,">2",C:C,">3") then also count.

This quote has been taken out of context, but this is an aggregate formula, my question is exclusively about "spill formulas", as I defined them in Post #8:

with Spill formulas I mean calculations that operate on an array of items and which deliver an array of answers called a "resultant array"

[romperstomper]

Any function that returns an array of results in earlier versions should be array-entered into sufficient cells. If you relied on implicit intersection, you could use a function that returned an array without array entering it, since only the intersecting value would be returned to each cell (or an error if there was no intersection).
Array-entering one cell and filling down means that the formula returns one value which changes due to changing inputs for each cell (or for some obscure reason you only want the first value from a resultant array). None of that was ever called spilling that I am aware of.

[HansDouwe]

Any function that returns an array of results in earlier versions should be array-entered into sufficient cells.

Thanks Rorya. This is indeed necessary very often, but it has now become apparent that this is not always necessary. For example the function: =SUM(LARGE(C23:C31,{1,2,3})) in Excel 2016. See video in Post #8.

Array-entering one cell and filling down ...None of that was ever called spilling that I am aware of.

Indeed, but that's not what my question is about.

[romperstomper]

Thanks Rorya. This is indeed necessary very often, but it has now become apparent that this is not always necessary. For example the function: =SUM(LARGE(C23:C31,{1,2,3})) in Excel 2016. See video in Post #8.

That does not return an array of results, it returns 1 result. As it uses an array constant, it does not require array entry at all, but it also doesn't meet your definition of a "spill formula" so I don't see the relevance?

[HansDouwe]

That does not return an array of results, it returns 1 result.

No this function =SUM(LARGE(C23:C31,{1,2,3})) returns the Top 3 (in 3 separate cells). See video Post #8

[josephteh]

With Microsoft Office Home & Business 2019, you need to hit CSE and cover the exact range in order to get the result you desired.

WhatsApp Image 2024-01-30 at 5.54.05 PM.jpeg

[HansDouwe]

With Microsoft Office Home & Business 2019, you need to hit CSE and cover the exact range in order to get the result you desired.

Hi Joseph, Hi Sorry. I overlooked your important post. Thank you very much for the explanation about 2019. Clear and educational.

[HansDouwe]

Everyone, thank you very much for your contributions.

Your answers together with the two videos about Excel 2007 and Excel 2016 on this subject that I found later (see post #7 and #8) sufficiently answered my questions for Excel 2007 through Excel 2016.

The question remains how exactly this works in Excel 2019

[romperstomper]

Excel 2019 does not have dynamic arrays so the same rules apply as for prior versions.

[HansDouwe]

Excel 2019 does not have dynamic arrays so the same rules apply as for prior versions.

OK, that is clear. So nothing has been delivered yet in Excel 2019 to simplify these types of functions?

All answers together (including my own research in between) have provided sufficient new insights.

My preference was for a theoretical approach to this subject, but that turns out to be a difficult exercise in the Excel domain.

[romperstomper]

No, it doesn't. The use of SUM means that it returns the sum of the top 3, which is one result, not 3.

[HansDouwe]

No, it doesn't. The use of SUM means that it returns the sum of the top 3, which is one result, not 3.

You are right. I need coffee. I mean =LARGE(C23:C31,{1,2,3}) returns in Excel 2016 3 separate cells without CSE.

[romperstomper]

You are right. I need coffee. I mean =LARGE(C23:C31,{1,2,3}) returns in Excel 2016 3 separate cells without CSE.

No, it won't; you would have to select three cells in a row and array-enter that formula.

[romperstomper]

Thanks for the rep, BTW.

[DJunqueira]

I tend to agree with rorya, spill array is a different "animal", you write just one cell and the result is up to Excel .
An array formula you need to "copy down" or you won't see all the results.

[HansDouwe]

@DJunqueira,

My question was not about array formulas that you have to copy to see all results.
These formulas only return one result in 1 cell.

My question was about formulas in Excel 2007 - Excel 2019 that can return an array without having to copy the formula itself, such as MMULT, FREQUENICY, TRANSPOSE, ROW(1:10) or COLUMN (A:Z).
This question has been sufficiently answered above. There is even a video from 2008 about this in Post #7.

[josephteh]

With MO H&B 2019, enter formula with 1 cell or 3 cells selected without CSE, array formula won't work.

WhatsApp Image 2024-01-31 at 9.37.31 AM.jpeg

[HansDouwe]

@Josephteh,

Thanks for the continuation last night.

Apparently the operation in 2019 is different from the operation in older versions.
Unfortunately, Microsoft does not publish the exact details about this per version.

And the knowledge about this among the members is also not complete.

[AliGW]

How can the membership have a full understanding when Microsoft don't even publish this information? Our advice here is all based on experience - that's all we can base it on, really, but glad the hive brain has helped somewhat.

[romperstomper]

I don't see any difference between the behaviour in 2019 and earlier versions. It all appears to follow what has been previously said in this thread.