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

Microsoft is making Excel’s formulas easier

theverge.com

Post reply on HN
Print view View original

111–120 of 141 posts

Re: Microsoft is making Excel’s formulas easier

#111

Post by function_seven »

layer8 wrote: post #105
function_seven wrote: post #2

My current #1 wish for Excel formula entry: Please let me use [Tab] and [Enter] in the formula bar without trying to commit the change! I like to format my formulas with multiple lines and indentation, but to do that I have to hold down [Alt] and mash the spacebar like a caveman. I am happy with the new features I've been seeing. LAMBDA() and the new TEXT functions are nifty.

You can insert a tab character by pasting it. I think you could use AutoHotKey to detect when you're in the formula bar (the class of the active control is "EXCEL<1"), and then have it map Enter to Alt+Enter and Tab to pasting a tab character.

I assume you didn't see my other comment buried elsewhere in this thread, but that's exactly what I just did today. :)

https://news.ycombinator.com/item?id=34178298

(Except I decided to go with 4 spaces from the tab key. I'll see what's up with pasting a literal tab. Maybe that's better)

Re: Microsoft is making Excel’s formulas easier

#112

Post by layer8 »

function_seven wrote: post #111
layer8 wrote: post #105

Earlier quoted context omitted.

You can insert a tab character by pasting it. I think you could use AutoHotKey to detect when you're in the formula bar (the class of the active control is "EXCEL<1"), and then have it map Enter to Alt+Enter and Tab to pasting a tab character.

I assume you didn't see my other comment buried elsewhere in this thread, but that's exactly what I just did today. :) https://news.ycombinator.com/item?id=34178298 (Except I decided to go with 4 spaces from the tab key. I'll see what's up with pasting a literal tab. Maybe that's better)

Oh, right, I missed that. I generally prefer spaces, but tab should be possible. It might be worth a try to ControlSend a tab directly to the control; that could conceivably work without having to clobber the clipboard.

Re: Microsoft is making Excel’s formulas easier

#113

Post by Tagbert »

kurthr wrote: post #107

One aspect that Excel almost seems to enforce for any significantly complex computation is... testing. If you don't known have test input (e.g. an array of values) for which you have known output to observe for such an opaque programming language, you will be bitten! This innovation just makes it even more important. That said Excel lets non-programmers create visual representations of data on a regular basis, and ev…

I tend to deal with that by using intermediate calculations. I can verify subsets of the formula and then use the results of those subsets in the next stage of the overall formula

Re: Microsoft is making Excel’s formulas easier

#114

Post by function_seven »

user3939382 wrote: post #101
ilyt wrote: post #80

Earlier quoted context omitted.

That's just ugly hack, formulas are essentially code and should be treated liek that

We break up code into functions right?

I've used both VBA functions as well as the new LAMBDA() function, and they have their place. But there are legitimate reasons I don't want "magic" cells in my worksheet that exist only to be referred to by other cells. That kind of indirection comes with its own headaches. I try to make each cell useful for someone looking at that cell. Sometimes it makes sense to show the user the intermediate calculations. That's kind of the fundamental reason for spreadsheets—the paper kind!—in the first place. But I don't think it's right to spread a specific calculation out over many cells solely to avoid a complex function. Keeping it in the single cell—and using line breaks and indentation to make it more readable—is easier for me to maintain later, rather than bouncing around different locations in the sheet trying to reason about a given formula.

Here's a real example where I'm listing the unique items from a data table that meet user-supplied threshold criteria:

    =UNIQUE(
        FILTER(
            data[Front Page Formatted],
            (data[Completed Month]         = L$27) *
            (data[Expedite Rate in Month] >= cutoff_rate) *
            (data[Tickets in Month]       >= cutoff_volume),
        "None"
        )
    )
Those three filter criteria are booleans that are multiplied together. (Huh, should I have used AND() instead?) If all three are true, then the resulting list is UNIQUE'd and shown on the report page.

Re: Microsoft is making Excel’s formulas easier

#118

Post by dmaevsky »

twobitshifter wrote: post #90
KMag wrote: post #86

Quick shout out to Ellx[0] (I presume, Excel pronounced backwards), which I discovered on HN a couple of years ago when discussing where I'd like to see spreadsheets evolve. It's basically functional reactive spreadsheets in JavaScript, with a storage format that's friendly toward code review, source control, diffs, etc. It automatically performs function lifting, so functions over single values become time-varying f…

Seems like something I’d really like, but when I went looking at the demos and docs it was spitting lots of errors on Safari Example: https://ellx.io/ellx-hub/lib

Creator or Ellx here. The reason is quite prosaic: I haven't maintained the site for almost a year :/ For various reasons. However, I'm getting back at it now. Ellx as a framework has evolved during this period, and is capable of much more than just a spreadsheet now, but this progress hasn't made it to ellx.io just yet. May I ask what you liked most about Ellx? What is your use case?

Re: Microsoft is making Excel’s formulas easier

#119

Post by russfink »

jmkni wrote: post #45
hermitcrab wrote: post #21

Will it give us beauts like this?: =IFERROR(IF(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1) (source: https://www.quora.com/What-is-the-longest-excel-formula-you-... )

For fun, I took this formula and asked ChatGPT to convert it to a VBA Function (with comments): Function extractText(cell As Range) As String ' Declare an array of search terms Dim searchTerms As Variant searchTerms = Array("Banner", "EBL2", "Movie Art", "Use as is", "TTT", "Generic") ' Initialize start and end positions to 0 Dim startPos As Long startPos = 0 Dim endPos As Long endPos = 0 ' Loop through search terms…

Did it write the comments, too?

Re: Microsoft is making Excel’s formulas easier

#120

Post by rhaway84773 »

spockz wrote: post #63
unsupp0rted wrote: post #13

Recently I started building Excel addons with custom formulas (e.g. =company.inventory(upc, WAREHOUSE), which call an api. This has blown the minds of non-tech folks among my clients. They equate this to magic. I was forced into doing this because after a year of digging to find out what reporting they wanted in the dashboard (“oh a thousand things… where’s the Excel spreadsheet export button?”), I gave up and now de…

Isn’t this exactly what Microsoft access was invented for? I’ve seen technical but non-cs people built magical things in forms by (or views). And when access got the ability to talk to MSSQL it really blew up. The quintessential low-code environment. I wonder why it never caught on.

Access as an idea was awesome.

The execution sucked because Access is/was a terrible database. Poor data integrity, really low limits on the DB size, etc.

I think if Access was as good as SQLlite, it would have really taken off.

Post reply on HN