Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New Text and Array Functions for Excel

techcommunity.microsoft.com

Post reply on HN
Print view View original

11–20 of 55 posts

Re: New Text and Array Functions for Excel

#11

Post by evandwight »

spywaregorilla wrote: post #8

Array functions feel like they're just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow. I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell. So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") and if you w…

Don't they have arrays in single cells?

https://www.lifewire.com/excel-single-cell-array-formula-312...

Re: New Text and Array Functions for Excel

#12

Post by evandwight »

armchairhacker wrote: post #10
omarhaneef wrote: post #5

It’s 2022 and how come they don’t have GPT4 and some stable diffusion built in?

seriously though, I think a copilot-like ML to improve flash fill is a genuinely amazing idea which could save insane hours, especially with people less familiar with complex formulas/coding

Someone just created that!

https://excelformulabot.com/

Apparently Microsoft is creating a plugin.

(Not affiliated)

Re: New Text and Array Functions for Excel

#13

Post by dhosek »

The functionality I want is a count-by-format function. I had to write my own in VBA and of course that means every time I open that sheet I have to approve its use of macros (and it also doesn’t always catch format changes that impact the calculation)

Re: New Text and Array Functions for Excel

#14

Post by cm2187 »

evandwight wrote: post #11
spywaregorilla wrote: post #8

Array functions feel like they're just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow. I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell. So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") and if you w…

Don't they have arrays in single cells? https://www.lifewire.com/excel-single-cell-array-formula-312...

No, these functions use an aggregation function (like SUM). If you don't use an aggregation function the value of the cell is the top left element of the array. The parent suggests a cell which value is an array object, which can then be queried by another formula.

Re: New Text and Array Functions for Excel

#15

Post by spywaregorilla »

evandwight wrote: post #11
spywaregorilla wrote: post #8

Array functions feel like they're just too far from the excel design thinking. A single function that affects nearby cells is hard for me to swallow. I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell. So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") and if you w…

Don't they have arrays in single cells? https://www.lifewire.com/excel-single-cell-array-formula-312...

It's been a while, but I think these functions must be map-reduce expressions. You cannot return an array and pull out it's elements elsewhere. You cannot do operations with the resulting array. Like a merge or whatever.

Re: New Text and Array Functions for Excel

#17

Post by evandwight »

cm2187 wrote: post #14
evandwight wrote: post #11

Earlier quoted context omitted.

Don't they have arrays in single cells? https://www.lifewire.com/excel-single-cell-array-formula-312...

No, these functions use an aggregation function (like SUM). If you don't use an aggregation function the value of the cell is the top left element of the array. The parent suggests a cell which value is an array object, which can then be queried by another formula.

Ah thanks for clearing that up

Re: New Text and Array Functions for Excel

#19

Post by auxym »

dhosek wrote: post #13

The functionality I want is a count-by-format function. I had to write my own in VBA and of course that means every time I open that sheet I have to approve its use of macros (and it also doesn’t always catch format changes that impact the calculation)

Encoding data in cell formatting is questionable practice.

Re: New Text and Array Functions for Excel

#20

Post by auxym »

waynenilsen wrote: post #2

I am very surprised they have not yet embraced the dplyr/tidyr-style melt/cast gather/spread pivot/unpivot functions for arrays

I am not familiar with the R ecosystem, but have you tried PoweredQuery?

I had become my go-to for all sorts for all sorts of data munging in excel.

Post reply on HN