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

51–60 of 141 posts

Re: Microsoft is making Excel’s formulas easier

#51

Post by dmurray »

capableweb wrote: post #47
jmkni wrote: post #45

Earlier quoted context omitted.

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 you actually try running the code vs the formula and compared it works the same way in the expected cases? The times I've tried using ChatGPT, it has mostly giving me code that seems like it'd work but doesn't.

"ChatGPT, can you give me some test cases for the code?"

Re: Microsoft is making Excel’s formulas easier

#52

Post by logicallee »

capableweb wrote: post #47
jmkni wrote: post #45

Earlier quoted context omitted.

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 you actually try running the code vs the formula and compared it works the same way in the expected cases? The times I've tried using ChatGPT, it has mostly giving me code that seems like it'd work but doesn't.

(not OP) over on reddit people are saying it is fantastic for giving them excel formulas they ask for. I guess it means the formulas work!

Re: Microsoft is making Excel’s formulas easier

#53

Post by function_seven »

cm2187 wrote: post #39
function_seven wrote: post #35

Earlier quoted context omitted.

Oof. I actually use that frequently as well. Okay, okay, How about this? My wished-for option would just be to swap the behavior of [Enter] and [Alt]+[Enter]. Normally the first one commits the formula, the second one inserts a newline. I want to reverse that and make a naked [Enter] insert the newline, and the [Alt]+[Enter] commit the formula.

I think shift+enter for new line would be a better choice if you were to highjack an existing shortcut. Probably barely used by anyone and it would be consistent with a line break shortcut in word/powerpoint and elsewhere. Committing a formula needs to be a simple shortcut because it has to be used by the least technical users. You don't want to create a "how to exit VIM" mess in a retail product.

You're right, I don't want to change defaults. In fact, this whole back and forth reminded me that AutoHotKey exists, and I'm actually running it already, and I have the power to make this change myself. So I did!

    #if IsExcelFormulaBox()                ; Whenever the formula edit box has focus
    Tab::Send {Space}{Space}{Space}{Space} ; insert four spaces when I hit [Tab]
    $!Enter::Send {Enter}                  ; commit the formula with [Alt]+[Enter]
    $Enter::Send !{Enter}                  ; insert a newline with bare [Enter]
    #if
does what I want, with the helper function:

    IsExcelFormulaBox() {
        ControlGetFocus, F, A
        return (F="EXCEL

Re: Microsoft is making Excel’s formulas easier

#54

Post by logicallee »

For anyone who writes Excel formulas, it is one of the use cases for ChatGPT (a currently free chatbot that appears highly intelligent in some contexts) that users rave the most about. The next time you're writing an excel formula, give chatgpt a try. It might just get it right the first time.

Re: Microsoft is making Excel’s formulas easier

#55

Post by jmkni »

capableweb wrote: post #47
jmkni wrote: post #45

Earlier quoted context omitted.

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 you actually try running the code vs the formula and compared it works the same way in the expected cases? The times I've tried using ChatGPT, it has mostly giving me code that seems like it'd work but doesn't.

Ok so you're right, looks like it will work, but actually doesn't!

I wonder if ChatGPT can fix it? (will update if so)

Re: Microsoft is making Excel’s formulas easier

#56

Post by hermitcrab »

logicallee wrote: post #52
capableweb wrote: post #47

Earlier quoted context omitted.

Did you actually try running the code vs the formula and compared it works the same way in the expected cases? The times I've tried using ChatGPT, it has mostly giving me code that seems like it'd work but doesn't.

(not OP) over on reddit people are saying it is fantastic for giving them excel formulas they ask for. I guess it means the formulas work!

That is one of the big issues with AIs like ChatGPT though. It gives you a plausible sounding answer, but that answer might be total BS. It doesn't tell you what degree of confidence it has in the answer. Maybe the formulas it gives are right 100%, 99% or 0% of the time.

Re: Microsoft is making Excel’s formulas easier

#57

Post by LeifCarrotson »

sokoloff wrote: post #15
LeifCarrotson wrote: post #5

Earlier quoted context omitted.

And [Home] without jumping to select column A, and the arrow keys should work consistently instead of sometimes moving the cursor and sometimes inserting a cell selection.

@airstrike gave me the following very helpful clue when I complained about this a couple years ago: "Just hit F2 while editing a formula to toggle between Edit and Enter modes, one of which will behave as you expect. The other mode, which you hate, is very useful when you want to add references to other cells into your formula"* There is a designation in the lower left of the window to show whether you're in Edit or…

Ah, I usually begin editing a pre-existing cell by tapping F2, but sometimes when I start entering a formula from scratch I just hit the "equals" sign. It feels like I'm editing a line of text but I'm actually in "enter" mode.

Also, the little designation in the lower left has been there, roughly 30 inches from my eyeballs, for hundreds or possibly thousands of hours. It's changed state thousands if not millions of times. How have I only just now seen it?

https://i.imgur.com/6yrULDU.png

The poor programmer at Microsoft who invented the mode switching feature would be justifiably infuriated by the blindness of his users...

Re: Microsoft is making Excel’s formulas easier

#58

Post by SoftTalker »

phpisthebest wrote: post #43
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…

Yep, we have a expensive reporting tool, can too all kinds of things, query every type of database we have... Number one function used. Export to Excel, and normally it is on reports of basic table join queries, none of the advanced things it can do with the data

This is because businesspeople like to fiddle with formulas, graphs, and filters untill they isolate (i.e. cherry-pick) the subset of data that [makes them look good | supports their agendas]. They don't want canned reports. Excel makes this easy because they already know how to do those things in Excel.

Re: Microsoft is making Excel’s formulas easier

#59

Post by mcbishop »

jmkni wrote: post #55
capableweb wrote: post #47

Earlier quoted context omitted.

Did you actually try running the code vs the formula and compared it works the same way in the expected cases? The times I've tried using ChatGPT, it has mostly giving me code that seems like it'd work but doesn't.

Ok so you're right, looks like it will work, but actually doesn't! I wonder if ChatGPT can fix it? (will update if so)

The VBA code seems to be a good starting point. I'm impressed!

Re: Microsoft is making Excel’s formulas easier

#60

Post by pseudosavant »

One of the best ways Microsoft has made Excel formulas better is by allowing line breaks (using alt-enter) and tab/spaces alignment. So instead of seeing something like this:

  =IIF(foobar, pv(a1:a100), IFERROR(fv(b1:b100), 0));
You can do this using alt-enter:

  =IIF(
    foobar,
  
    pv(a1:a100),
  
    IFERROR(
      fv(b1:b100),
      0
    )
  );
Post reply on HN