3 ms·
I recently discovered that Excel gained “array formulas” a few years ago. [0] It’s a little hard to grasp for me because arrays are still flattened out as indiv
by wrs 3y ago
I recently discovered that Excel gained “array formulas” a few years ago. [0] It’s a little hard to grasp for me because arrays are still flattened out as individual values in the sheet, but it has been useful to write a single formula to process a group of values rather than filling across/down and perhaps losing sync when the formula is changed.
[0] https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 https://support.microsoft.com/en-us/office/guidelines-and-ex...
- zvrba 3y agoWait until you discover PowerQuery in Excel.
- 1980phipsi 3y agoExcel has had array formulas since at least 2003.
- blowski 3y agoI believe the OP is referring to array formulas which effectively explode the results into multiple cells, which was introduced in 2018.
- wrs 3y agoExactly. I’m not sure what to call the new feature, “array spillage”? I didn’t know the original was called “array formulas”. Anyway, the change to make the output go into cells automatically is indeed what I meant.
- mr_toad 3y agoIn a programming language it would be called destructuring.
- Izkata 3y agoYour link calls them "multi-cell array formulas" (expand the first accordion).
- bhl 3y agoYeah, 'spilled array' and 'spilling' are the correct noun and verb. https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531 https://support.microsoft.com/en-us/office/dynamic-array-for...
- dspillett 3y agoSince the 90s IIRC. Possible since at least v2 ('87) which I think is what I was using (on Windows 2 running off a couple of floppy drives) at the time. He is probably referring to the new dynamic array handling which enables “array spill” for almost any built-in function and can be much more intuitive than the previous (still supported, of course) variant. This was introduced in ~2019 I think.
- mhh__ 3y agoExcel even has lambdas and map and reduce these days
- mcbishop 3y agoYup! An array formula makes it much easier to handle this problem from the blog post: > If you have a set of numbers, and you want to add some constant to them, the way you'd do that is to create a formula that adds the constant to a cell and put that into a different location, usually on the same sheet, and then paste that formula to all the cells in an area with the same size as the original data, and hope that you got the dimensions right.