[HN Gopher] Microsoft is making Excel's formulas easier
___________________________________________________________________
Microsoft is making Excel's formulas easier
Author : Tomte
Score : 66 points
Date : 2022-12-28 06:25 UTC (1 days ago)
(HTM) web link (www.theverge.com)
(TXT) w3m dump (www.theverge.com)
| logicallee wrote:
| 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.
| SoftTalker wrote:
| Or it might get it wrong, but not obviously so. Who's job is it
| to debug/prove the ChatGPT-authored formulas that business
| people will certainly start using (if they haven't already). I
| have enough trouble debugging code I write myself. Debugging
| other people's code is much harder.
|
| When a company makes a multi-million dollar error because an
| analyst used ChatGPT formulas in Excel, I expect we'll get the
| answer.
| function_seven wrote:
| 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.
| cm2187 wrote:
| Then how do you suggest you commit the change then (which is
| much more commn than adding tabs to a formula)? Hopefully not
| some combination of keys.
| TylerE wrote:
| Clocking into another cell?
| cm2187 wrote:
| Do you mean clicking? Using the mouse, really?
|
| Beside clicking on a cell already has a meaning, it inserts
| the address of the cell you are clicking on in the formula.
| function_seven wrote:
| [Ctrl]+[Enter] would be awesome. Or just a double enter would
| work. Or clicking on the sheet somewhere.
|
| I don't want Excel to change the default behavior. The way it
| works now is the right way for most people. I just want to be
| able to enter a mode where I get to freely edit the formula
| as if it were in a text editor, then exit that mode when I'm
| satisfied. Whether that is some checkbox option buried in the
| settings ("Options > Formulas > Working with formulas"), or
| an F-key, I don't care.
| cm2187 wrote:
| CTR+ENTER is already taken. Select a range of cells, press
| F2, enter your formula, CTR+ENTER applies and fills that
| formula to the whole range (very useful).
| function_seven wrote:
| 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.
| cm2187 wrote:
| 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.
| function_seven wrote:
| 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<1") }
| sokoloff wrote:
| I use Option-Enter (on Mac, which is probably Alt-Enter on
| Windows) to insert line breaks in formulas:
|
| https://imgur.com/a/kXHzU9T
| ketralnis wrote:
| Right they explicitly mentioned it in the comment you replied
| to. They're asking for a regular normal human multiline text
| entry box that doesn't require a special mode where all of
| the keys mean different things than they're used to in code
| editors.
| sokoloff wrote:
| Did they?
|
| > to do that I have to hold down [Alt] and mash the
| spacebar like a caveman.
| function_seven wrote:
| I don't know why I wrote it that way. For the indenting,
| I have to use the space bar to line things up. No [Alt]
| needed in that scenario.
| function_seven wrote:
| Yeah, I do the same, but I wish I didn't have to. It's
| opposite of how I write text in all other contexts, and is
| really annoying when I forget to hold down [Alt] and get a
| modal admonishing me for having written a shit formula. Then
| I have to dismiss that modal, click on the cell again, and
| get back to where I was.
|
| Even worse, I spend a good chunk of time in Power BI, and it
| has a similar formula field (for DAX expressions), that
| mimics Excel a bit, but there you use [Shift] to insert
| newlines. So I'm always using the wrong modifier key and
| spewing insults at my computer.
| samwillis wrote:
| > Please let me use [Tab] and [Enter] in the formula bar
| without trying to commit the change
|
| Exactly, there should be a contextual difference between
| editing in the cell directly or via the formula bar, when in
| the formula bar tab and enter should insert a tab or new line.
| Comment/Ctrl Enter (committing the change) or Escape (reverting
| the change) should be the only way to exit the formula bar via
| the keyboard.
| LeifCarrotson wrote:
| 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.
| sokoloff wrote:
| @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 Enter mode.
|
| * https://news.ycombinator.com/item?id=26388148
| function_seven wrote:
| THANK YOU. This doesn't fix my original gripe, but it
| solves another one I've had. When doing conditional
| formatting rules, I thought I was stuck with a "dangerous"
| arrow key. F2 works in those boxes as well to let me
| position the cursor instead of clobbering my formatting
| rule with cell references.
| LeifCarrotson wrote:
| 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...
| sokoloff wrote:
| > How have I only just now seen it?
|
| Haha. I'm in the same boat. When writing my comment, I
| opened Excel, clicked on a cell, and tapped F2
| repeatedly, just to see if there was anything on the
| screen that changed...
| pony_sheared wrote:
| Give AFE a whirl, you can format and comment formulas
|
| https://www.microsoft.com/en-us/garage/profiles/advanced-for...
| ec109685 wrote:
| Interesting that isn't just a default feature in Excel.
| cm2187 wrote:
| You can't stop clippy!
| LarryMullins wrote:
| Sounds great, but I hope they bring this feature to the local
| versions of Excel too. Using feature exclusivity to lure people
| into digital serfdom isn't cool.
| hermitcrab wrote:
| Will it give us beauts like this?:
|
| =IFERROR(IF(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARC
| H("Banner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie
| Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Ge
| neric",AC5)),LEN(AC5)+1)<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("Ge
| neric",AC5)),LEN(AC5)+1),MID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("
| Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC
| 5)+1),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("Ban
| ner",AC5),SEARCH("EBL2",AC5)),SEARCH("Movie
| Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT",AC5)),SEARCH("Ge
| neric",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERROR(SEARCH("Customs"
| ,AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)),M
| ID(AC5,IFERROR(IFERROR(IFERROR(SEARCH("Customs",AC5),SEARCH("Cust
| om",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1),IFERROR(IFERROR(IFER
| ROR(IFERROR(IFERROR(IFERROR(SEARCH("Banner",AC5),SEARCH("EBL2",AC
| 5)),SEARCH("Movie Art",AC5)),SEARCH("Use as is",AC5)),SEARCH("TTT
| ",AC5)),SEARCH("Generic",AC5)),LEN(AC5)+1)-IFERROR(IFERROR(IFERRO
| R(SEARCH("Customs",AC5),SEARCH("Custom",AC5)),SEARCH("Generic",AC
| 5)),LEN(AC5)+1))),"")
|
| (source: https://www.quora.com/What-is-the-longest-excel-formula-
| you-... )
| jmkni wrote:
| 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 and
| find the first occurrence of any of them in the cell
| For Each searchTerm In searchTerms startPos =
| WorksheetFunction.Search(searchTerm, cell) If
| startPos > 0 Then Exit For
| End If Next searchTerm '
| If none of the search terms were found, try finding "Customs"
| or "Custom" If startPos = 0 Then
| startPos = WorksheetFunction.Search("Customs", cell)
| If startPos = 0 Then startPos =
| WorksheetFunction.Search("Custom", cell) End If
| End If ' If none of the above were
| found, try finding "Generic" If startPos = 0 Then
| startPos = WorksheetFunction.Search("Generic", cell)
| End If ' If none of the search terms
| were found, return an empty string If startPos = 0
| Then extractText = "" Else
| ' Loop through search terms and find the next occurrence of any
| of them after the start position For Each
| searchTerm In searchTerms endPos =
| WorksheetFunction.Search(searchTerm, cell, startPos + 1)
| If endPos > 0 Then Exit For
| End If Next searchTerm
| ' If none of the search terms were found after the start
| position, set the end position to the end of the cell
| If endPos = 0 Then endPos = Len(cell) + 1
| End If ' Extract the text
| between the start and end positions extractText
| = Mid(cell, startPos, endPos - startPos) End If
| End Function
|
| Edit: Sadly this doesn't work at all lol, and after half an
| hour of prompting ChatGPT can't figure out why, it just gets
| stuck in a loop :-(
| capableweb wrote:
| 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.
| jmkni wrote:
| 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)
| mcbishop wrote:
| The VBA code seems to be a good starting point. I'm
| impressed!
| dmurray wrote:
| "ChatGPT, can you give me some test cases for the code?"
| logicallee wrote:
| (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!
| hermitcrab wrote:
| 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.
| Sharlin wrote:
| Yes, and I think that in order for helper AIs like
| ChatGPT to really break through, people (especially in
| marketing) will have to learn to embrace confidence
| intervals, or at least _some_ quantitative concept of
| confidence. The AI already _knows_ how confident it is in
| its prediction, it 's just a UI question. If plain
| numbers are too nerdy or Spock-y, at least tell me
| something like "I'm fairly sure" or "I'm not sure,
| but..."
| meltyness wrote:
| Don't even think about getting a CSE right the first time.
| cm2187 wrote:
| If I had to ask for one killer new feature in Excel, it would be
| to adopt Apple's numbers approach to the design of a spreadsheet.
| I.e. not one grid per tab, but one canvas per tab, and that
| canvas can contain multiple elements which can be grids, charts,
| pivot tables, something else.
|
| Your grids can overflow with a scroll bar, so I can put one table
| above another one without them colliding when the top one
| expands.
|
| You can do that in a backward compatible way, if a canvas is not
| defined on an old spreadsheet, just assume one canvas that
| contains one grid set to full screen.
|
| It helps presentation, it helps splitting the logic of your
| spreadsheet in discrete components, I only see upside.
| tanin wrote:
| Well, shameless plug.
|
| https://superintendent.app (paid with free trial) enables you to
| load a bunch of CSVs and write SQL on those CSV files.
|
| It's a much faster to work with if you know SQL well. It can also
| handle millions of rows easily (e.g. Loading 1GB CSV file takes
| 10s on Macbook Pro). Excel can't load a CSV larger than 1M rows.
|
| I initially built it because I had to identify the mismatched
| transactions between 2 giant CSVs using. Using "full outer join"
| with Superintendent.app took only a minute to do.
| capableweb wrote:
| You are eventually gonna have to write comments about other
| things than just plugging your own projects, especially when
| they are just barely on topic.
| cratermoon wrote:
| Great. Can they fix the autocorrect so it doesn't mangle gene
| names? https://www.nature.com/articles/d41586-021-02211-4
| sxg wrote:
| Excel is a generic spreadsheet program that's designed to be
| used by 90% of people for 90% of use cases. Accommodating for
| this niche use case will definitely break things for many
| existing users. The real solution is for geneticists (and other
| data scientists) to use software designed more specifically for
| their use cases, such as CSV files and pandas or something more
| GUI-based but meant for researchers.
| tinus_hn wrote:
| CSV files are unsuitable for any serious use and especially
| not a 'better replacement for Excel files'. It's an ill
| defined, locale dependent format of fail.
| sideshowb wrote:
| "Not mangling stuff the user types in", yes, very niche
| requirement that, highly unlikely to benefit anyone else /s
| ;-)
| pseudosavant wrote:
| 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 ) );
| fisherjeff wrote:
| Okay but when are they fixing Excel for macOS?
| blockwriter wrote:
| Trying to reference the value of a cell in another sheet seems
| to work correctly maybe 50% of the time the first time I try it
| on Mac.
| unsupp0rted wrote:
| 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 default to giving them all reporting
| via Excel, using these custom formulas.
| spockz wrote:
| 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.
| jiggawatts wrote:
| Microsoft essentially abandoned it. They hoped that a bunch
| of random Azure services would be its replacement, but
| they're too scattered and incomplete compared to Access.
| rr808 wrote:
| Any new or good ways to do this? I used Excel-Dna 10 years ago.
| unsupp0rted wrote:
| Yup, javascript: https://learn.microsoft.com/en-
| us/office/dev/add-ins/excel/c...
| rr808 wrote:
| Ah interesting thanks, I'll have to try it out.
| cm2187 wrote:
| Is it fast? For instance there is a large overhead to using
| VBA UDF, whereas XLL (and ExcelDNA) is pretty fast (matters
| if the formula is repeated over and over on a spreadsheet).
| unsupp0rted wrote:
| Not sure how it compares to XLL but I'd wager the
| bottleneck, in my case, is the API calls.
| alar44 wrote:
| Honestly, GPT is pretty good at this type of stuff.
| unsupp0rted wrote:
| GPT's code and docs for this is way out of date. I tried it
| a couple weeks ago.
| ayemojay wrote:
| What is your language of choice for building custom formula?
|
| Python + xlwings: most successful (relatively speaking) to
| build custom finance functions in my line of work. Downside is
| the slowness and deploying on other computers.
|
| Rust + xladd: really enjoyed this but feels immature still.
| Better performance than python and easier to distribute as
| single dll.
|
| VBA: options above make this almost obsolete, however can't
| beat being embedded.
| unsupp0rted wrote:
| Modern Javascript: https://learn.microsoft.com/en-
| us/office/dev/add-ins/excel/c...
|
| For the "task pane", if you need one, you can use React or
| Angular, but I prefer to use Vue (or no front-end framework
| is fine too).
|
| For the backend logic, just straight up Node + webpack. You
| could probably do this better with Vite, but I think
| Microsoft's starter templates all use webpack.
|
| You end up distributing a manifest file that tells Excel
| which server your assets and API live at.
|
| Warning: hot reload and dev debugging is quite a pain on
| macOS.
| rr808 wrote:
| Excel-Dna works great. C#, Doesn't need access to registry so
| easy install.
| cm2187 wrote:
| And combined with NetOffice for Excel COM interoperability
| without the headaches.
| phpisthebest wrote:
| 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
| SoftTalker wrote:
| 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.
| Godel_unicode wrote:
| People get really mad when you call this p-hacking, btw. I
| used to try and make this point to "analysts" that I worked
| with, that science involves coming up with a hypothesis and
| then looking at the data. The response I got often enough
| that I'm sure it's being taught somewhere was that you
| "need to let the data tell it's own story".
|
| Turns out lots of people have decided what the conclusion
| is and are taking that conclusion and the data, and then
| blindly munging until they get a path which connects them.
| axelf4 wrote:
| As my professor said: All good statistics is done before
| you have looked at the data.
| sideshowb wrote:
| But where do you get your hypothesis from?
| legitster wrote:
| Sounds like a frustrating waste of time. Autocorrect/suggest is
| already one of least useful aspects of Excel. I would much rather
| they focus on improving the UI of writing formulas - perhaps a
| dedicated sidebar that let you blow up heavily nested equations?
| crazygringo wrote:
| As noted, Google Sheets introduced this over a year ago [1].
|
| But this is exactly what competition is about -- I love seeing
| this come to Excel precisely as an answer to Google's version.
| You have to wonder if Microsoft would have tried it otherwise,
| since Excel is so entrenched there's less profit motivation for
| innovating.
|
| Sometimes it feels like "office" software hasn't changed much
| since the 90's, but when you look at cloud, collaboration, and
| machine learning, it's still constantly reinventing itself even
| if the interface still looks largely the same.
|
| [1] https://www.theverge.com/2021/8/26/22642192/google-sheets-
| in...
| pessimizer wrote:
| Is writing a headline as if it were promotional marketing copy
| just a reflex? I don't think that The Verge is owned by Microsoft
| (I could be wrong), but otherwise I don't get the tone.
|
| edit: maybe that tone is necessary to convince people that this
| is a story at all.
| [deleted]
| cratermoon wrote:
| Advertorial content is essentially a press release written by
| the business and passed off to friendly, connected journalists.
| Sometimes they only cut it for length and don't even change the
| headline.
| drexlspivey wrote:
| Microsoft is changing the meta again just before BallmerCon. They
| should drop huge patches like this after the biggest tournament
| of the year..
___________________________________________________________________
(page generated 2022-12-29 23:00 UTC)