Reply to Thread

Dynamic Arrays/ Spilling alternative to type 2 CSE Entry

I have been made aware by formula Guru and excelforum Moderator Dave (FlameRetired) about planned changes in how Excel handles the so called Array formula CSE stuff
There is likely to be something going under the general name of Dynamic Arrays in the future.

It seems to be only concerned with the sort of CSE stuff often referred to as Type 2 CSE Entry , that is to say the following situation: You have something, ( a formula usually ) , that is returning you a field of results, so in order to see them you have to select an appropriate sized range first then put the formula in and enter it with the famous key combination of Control+Shift and Enter

For example , if I had a formula =A1:B2 , then that has available a 2x2 field of values to give me.
Currently if I want to be sure to get all those I would need to select at range of at least a 2x2 and then do the C S E stuff
For example, what I did to get the next screenshot was
_ put some arbitrary values in cells A1:B2
_ then I highlighted cells C3:E5
_ Typed the formula =A1:B2
_ confirmed with key combination of Control+Shift and Enter
=A1_B2 CSE.JPG : https://imgur.com/Mygbbg3
Attachment 2174
_____ ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	cell A1 value	cell B1 value
2	cell A2 value	cell B2 value
3			cell A1 value	cell B1 value	#NV
4			cell A2 value	cell B2 value	#NV
5			#NV	#NV	#NV
6

Worksheet: DynamicArrays
_____ ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	cell A1 value	cell B1 value
2	cell A2 value	cell B2 value
3			=A1:B2	=A1:B2	=A1:B2
4			=A1:B2	=A1:B2	=A1:B2
5			=A1:B2	=A1:B2	=A1:B2
6

Worksheet: DynamicArrays
Note also there that if your chosen area is too small your values will be truncated, and if your chosen area is too large you get those #error things.

Currently if you type a formula that has multiple values available in a cell and just Enter normally, then usually you get an error, or sometimes you might get a single value.

Dynamic Arrays and Spilling
The change that Microsoft is currently planning is give you the full set of values after a normal Enter. They refer to the things as “spilling” : The range of output values automatically “spills” to the right and down to give all your available values.


I suppose if I had never heard of CSE stuff and started from the outset in Excel with the Dynamic Array and Spilling feature, then I doubt I would give it a second thought.
But having finally got my teeth around the whole CSE stuff, especially the type 2 entry, I have found that helpful to get a better understanding of how Excel is working. There are parallels with how some things work in VBA, and I find that further very helpful. Personally I prefer to have the CSE stuff left as it is.
Just my personal preference: I would prefer not to have the new dynamic array/ spilling feature.
By the way, CSE stuff will be kept for backward compatibility , apparently. But I feel that the availability of the Dynamic Array / automatic Spilling will further encourage user ignorance.
I think it is more healthy if users of software are also , at least to some extent, understanders of that software.

Alan


Ref
https://support.office.com/en-us/art...4-df571bd4f1b4
https://www.excelforum.com/the-water...lease-eek.html
https://www.myonlinetraininghub.com/...dynamic-arrays
http://www.excelfox.com/forum/showth...on-and-VLookUp
https://support.office.com/en-us/art...1-87a7eb36e531
http://www.excelfox.com/forum/showth...0998#post10998
https://www.youtube.com/watch?v=1HF0UGMF070
https://fastexcel.wordpress.com/2019...rray-formulas/
https://newtonexcelbach.com/2019/05/...rays-in-excel/
https://mailchi.mp/9c5dfba0e9c3/zm6l...3?e=b8b970d9f5
https://fastexcel.wordpress.com/2019...rray-formulas/
https://www.eileenslounge.com/viewto...=33843#p262121
https://www.mrexcel.com/board/thread...2#post-5401671
Roy 2020-09-08 13:22:43 https://excelribbon.tips.net/T007820...h_Numbers.html