Hacker News Reader

Comment by spywaregorilla | Hacker News Reader

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 wanted to unpack it you could drag a formula that was something like =$A$1?0, =$A$1?1, =$A$1?2, etc.

Or maybe just one single black magic affects-nearby-cells function called "UNPACK"

View on HN

I somewhat agree, but I've bent Excel to do things that aren't "excel thinking" for literally decades.

Reshaping is something could have used many times in the past. I used to pull data into PERL first to reshape it but that took a bit of code, then I learned about numpy doing it in one line. But I still have to import back into Excel. The fact that it is in there now is useful to me.

I 1000% agree with your matlab retval suggestion however. I hate how Excel fudges array return values by just blasting a range of cells one time!

My excel knowledge has been somewhat stagnant in the past decade. Have they added an ARRAYFUNC() like in Google Sheets, or do I still need to hit "ctrl-shift-enter" to designate one?

> 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.

I had a go at writing a DLL plugin for Excel that did this years ago. I ended up with a kind of SQL, where each cell has a result set of records. The purpose was to make a functional language for consultants starting with a familiar environment to them. I even integrated a system where you clicked the cell and a pop up would show the data records. It was an ugly proof-of-concept, using strings that just identified each result set, and using custom functions. Excel is beautifully functional, with some nice parallels with SQL, and your data flow/dependencies are naturally visible. Excel is far less scary to most consultants than imperative programming is. I wanted to be able to model the data flows, use sheets for consultants to define custom pure functions for our system, and the final outcome was a reactive data system where data updates could flow (push) into outputs. I failed to get it delivered because I failed to get the COM interfaces working working: I failed to tie together Excel automation as a library engine (Excel COM API), Excel custom functions (plug in DLL), Delphi 7, and my own code.

>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. you mean kinda like json and jsonb in postgres?

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

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.

All they need to do is implement Google Sheets ARRAYFORMULA().

Reply on news.ycombinator.com