[HN Gopher] Excel is pretty dang cool
___________________________________________________________________
Excel is pretty dang cool
Author : todsacerdoti
Score : 274 points
Date : 2022-08-12 15:23 UTC (7 hours ago)
(HTM) web link (buttondown.email)
(TXT) w3m dump (buttondown.email)
| AtlasBarfed wrote:
| I did a stint in document management for a company.
|
| Let me disagree: All of Office is a very very very bad thing from
| a "shared information" standpoint. If you put information into an
| office document/spreadsheet/etc, it effectively locks it from use
| in the rest of the company, publishing, and programmatic
| analysis.
|
| Suuuuure, there's lots of products out there that will offer
| services to extract data from your word docs, or all that adhoc
| data in your spreadsheet.
|
| Extracting data from word documents is definitely in the "with a
| million lines of code you can do anything" camp. It's just a lot
| of ugly crap but its doable.
|
| But... why?
|
| Why have companies allowed Microsoft to make their storage
| formats utterly inaccessible and integration resistant? Well, it
| made them mints of money in monopolizing/stopping switchover to
| competitors, and allowed them to make money on the tools to
| extract/use their insufferable storage formats.
|
| Soooo much efficiency lost.
|
| Excel the programming and UI app is an amazing achievement
| (here's to Lotus 123 for making the first spreadsheet, as with
| everything, Microsoft didn't create it, it copied it).
|
| But for a data creation, manipulation, and "database", it is a
| tragedy.
| proamdev123 wrote:
| I don't understand. How is putting information in a document or
| spreadsheet "lock it from use in the rest of the company "?
|
| Are you talking about some specific use case, such as data that
| should be in an ERP or CRM being stored in a spreadsheet
| instead?
|
| Please elaborate.
| layer8 wrote:
| One thing I'm missing in Excel is the ability to define an auto-
| updating table that is basically an SQL-like view
| joining/filtering/projecting/grouping data from some other tables
| in the same Excel file. Or is there actually a way to do this? I
| understand that Power Query allows to do something like that with
| an external data source. Being able to have the result of a
| spillover formula automatically form a table (or a named range)
| would also be helpful.
| hermitcrab wrote:
| This is a standard use case for ETL (extract load transform)
| tools such as Easy Data Transform at the budget end (which i
| wrote) or Alteryx at the corporate end.
| layer8 wrote:
| I want the expressiveness of SQL queries within a single
| Excel file, so that data entry and derived views are within
| the same tool and same file. Also, EDT doesn't look like it
| can do joins?
| MgB2 wrote:
| PowerQuery can also use ranges inside the same worksheet as
| data sources. You can define either cell ranges or named tables
| as data sources and then use them in PowerQuery just like you
| would use any external data source.
| bernardv wrote:
| Thing is, Excel could have been so much cooler, much, much
| earlier. Decades of unused potential.
| hammyhavoc wrote:
| Just don't get any ideas like doing a track-and-trace system for
| an entire nation in Excel.
| xxpor wrote:
| The most successful functional language ever.
| Ygg2 wrote:
| You mean second most? JavaScript exists.
| xxpor wrote:
| I guarantee you there are more writers of Excel than JS in
| the world. (Which are more important to a language? Consumers
| or writers?)
| chadash wrote:
| once you count consumers, you get into semantics. Like is
| C/C++ more used than javascript, because all the javascript
| essentially runs in browsers compiled from C++?
| Banana699 wrote:
| By that metric, all programming languages including
| assembly have exactly 0 consumers, since nothing human-
| readable actually runs on computers.
|
| Look at that, I have saved programming from language
| popularity contests, no need to thank me. I will humbly
| accept your bitcoins though.
|
| ---
|
| The most reasonable definition of a language consumer is
| as follows: If a program text P is written by a human-
| level intelligence in source language L, then every user
| of any automatically-produced-artifact from P is a
| consumer of L.
|
| Suppose a programmer use typescript to write a web app:
|
| - The programmer, who must compile the typescript to
| javascript, is a consumer of whatever language the
| typescript compiler happens to be written in.
|
| - The user, who must run the resultant javascript files,
| is a consumer of (1) typescript,
| because the JS files is an artifact produced
| automatically from the typescript text the
| programmer, who is a human-level
| intelligence, wrote. (2) The languages the
| browser/JS engine is source-written in, typically C++,
| Rust, or Zig. (3) Possibly javascript,
| because typescript is a strict superset of
| javascript, and thus there is a probability that at least
| part of the original text of the webapp is
| valid javascript, and thus qualify in the
| definition. Take note that this has nothing to do with
| the typescript program text compiling to
| javascript, this is solely due to the fact
| that typescript is a strict superset of javascript, any
| language that is not would not have this
| property.
|
| From (3) we see that languages are not completely
| mutually exclusive : There is a language called
| Arithmetic ('+','*','-','/' and numbers) that has a vast
| userbase unparalled by any single programming language,
| since it's a subset of a lot of programming languages.
| Other consequences of this definition is that :
|
| - Languages with no human-level-intelligence writers have
| 0 consumers
|
| - Every producer is a consumer, since the only human-
| level intelligences currently writing code are humans,
| and human producers are always consumers as well since
| they run the artifacts produced from their own program
| texts.
|
| - Auto-Generated code add to the consumers of the
| generating language, not the generated language. If part
| of a C++ app came from the output of a python script,
| every consumer of the app is a consumer of python, since
| some of the artifacts they consume ultimately came from
| python source. If the script generates (say) Rust or
| Fortran code that is then compiled and linked into the
| final executable, the only languages the users consume
| are C++ and Python. Auto-generated code is an
| automatically produced artifact.
| chadash wrote:
| Excel is "written" by _many_ more people than javascript
| lvass wrote:
| I'll take plain text over WYSIWYG any day, thanks. Excel gives
| you some obvious way to do some things and stacks up a huge load
| of features so you can hack your problem into the Excel way. But
| the Excel way tends to be terrible from start to finish. People
| stopped doing Double-entry bookkeeping because it's somehow hard
| in the world's most used accounting software while saner programs
| like ledger-cli and beancount make it very easy. For any
| moderately complex work, coupling data and data manipulation is a
| very bad idea, and having both in an opaque, non-diffable, non-
| VCS-able file is just making hell out of everyone's life for no
| good reason.
|
| I understand if you just want to put your data in a table and do
| some math with it. Sometimes it's all you know how to use and you
| end up doing something important with it, that's okay. Sometimes
| it's all your peers know how to use and you choose Excel because
| of them, that's great. Laziness and inertia are just facts of the
| world and we have to accept it exists, but please don't pretend
| we can't do better than Excel most of the time.
| jader201 wrote:
| > _I understand if you just want to put your data in a table
| and do some math with it._
|
| But this is _huge_ and what most people use Excel /Sheets for,
| in their day-to-day work.
|
| I have used Excel (and nowadays Sheets, because it's free) for
| years in both my personal and professional life, just for this
| purpose. I've probably used this more than any single tool/app.
|
| Plain text has its place, but saying that it is a replacement
| for Excel/Sheets is overlooking a huge reason it's so useful.
| lvass wrote:
| Never said everyone should simply replace it. I, myself,
| would prefer not to use Excel because I am proficient in
| ledger and org-mode which absolutely does supplant that use
| case (and much more). The criticism here is when you have
| complex data and manipulation and attempt to use a terrible
| tool for that job. Excel in fact IS good for some things, but
| unfortunately happens to be usable, and commonly used, for
| things it's terrible at.
| racl101 wrote:
| If it weren't for the bugs still being carried over from the
| early 2000s it would be a lot a cooler.
| dreamcompiler wrote:
| Excel is the only reason I still pay money to Microsoft. I buy
| Office just for Excel. Word and Powerpoint have been dumpster
| fires for decades and both have good alternatives.
|
| But Excel works well and nothing else even comes close to its
| capabilities. Now that it has lambda it's an actual dataflow
| programming language.
| DavidPeiffer wrote:
| If their mind is blown by named ranges, they are going to faint
| when learning about the nice structure a proper table (Insert ->
| Table) gives!
|
| It's so handy typing
| =SUM(tbl_Sales_Transactions[Total])
|
| And knowing you don't have to adjust the range of the named area
| as long as the table is the correct length. Far nicer than
| SUM(F:F) or SUM(F2:F1000) which can cause performance issues and
| could be exceeded when pulling in new data respectively.
|
| Within the table, you can do this syntax to use items on the same
| row of data. =[@[Price]*[@[Tax Rate]]
| gxqoz wrote:
| Control + T is an easier way to do this
| hwayne wrote:
| With power query, named tables, linked data types, lambdas, and
| xlookup, you can hack structs into excel, letting you write
| `Person("John").age` in formulas.
| anonu wrote:
| Excel has been cool for a few decades now. There are entire
| trading strategies run on Excel - down to actually sending and
| cancelling orders. Companies have banked their entire success on
| operating via the spreadsheet.
|
| I always hoped things like Google Sheets would supplant Excel -
| but its not feature complete...
| kasajian wrote:
| I quote, "Excel has actual programming affordances now"... Range
| names "fixes one of the biggest problems that makes spreadsheets
| illegible"
|
| "Instead of writing the formula =A1 _B1, you can do =Width_
| Height like you should have been able to 30 years ago."
|
| Not sure what this dude is talking about. Range names have been
| in Excel for decades.
| zweifuss wrote:
| Namend cells and ranges was one of the major features of
| Microsoft Multiplan (1982). See p. 60 in [1].
|
| MS Excel 2.0 (1987) could do the same, but entering names for
| ranges was not as easy as in Multiplan. Look in the menu for
| Formula | Names...
|
| [1]
| https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
| listenallyall wrote:
| It's like a TIL post on the front page of Reddit that states
| something you assumed everybody knew, yet has 30,000 upvotes.
| mathteacher1729 wrote:
| XKCD 1053 is relevant here. You and I knew it, but there are
| going to be lots of people who learn about it for the first
| time today, and that's a net positive.
| ms512 wrote:
| https://xkcd.com/1053/ (to make a link available)
| [deleted]
| datavirtue wrote:
| Yeah, but not a post for HN. Did you know Excel has a
| programming language!!!??? Whahhh!!!
| hwayne wrote:
| Yes but did you know about the yoga pose button
| kurupt213 wrote:
| i still don't know what that meant
| hwayne wrote:
| The feature name is "linked data types", under the "data"
| tab. Put, say, "downward dog" into a cell and click the
| "yoga" linked data type.
|
| There's other, more useful data types, like cities and
| ZIP codes and stocks, I just listed the yoga one because
| it's the funniest.
| cookie_monsta wrote:
| Looks like the yoga button is a thing of the past :(
|
| > After June 11, 2023, data types by Wolfram will no
| longer be supported and can't be refreshed. However,
| Bing, Power Query, and Organization data types will still
| be supported.
|
| https://support.microsoft.com/en-us/office/what-linked-
| data-...
| Stratoscope wrote:
| Just an HN formatting note... To have * characters taken
| literally instead of italicizing the text in between, prefix
| each one with a backslash, like this: Instead
| of writing the formula =A1\*B1, you can do =Width\*Height
|
| which formats as:
|
| Instead of writing the formula =A1*B1, you can do =Width*Height
| moomin wrote:
| He's conflating it with LET and LAMBDA, which are new.
|
| I worked for a firm that did extremely expensive training
| course for Corporate Finance back in the 90s. We taught people
| how to use named ranges. There's probably still good money in
| that business.
| monkey_monkey wrote:
| Excel has also finally added sum() and other functions that
| allow the spreadsheet to do the hard work instead of making you
| use a calculator to add all the numbers up.
| extr wrote:
| Lol, literally came across this situation circa ~2012. I was
| an intern at a big but old school corporation. Someone asked
| me to help someone on a PM team with Excel, come to find out
| she was running some accounting numbers and was literally
| doing this. Reading numbers, putting them into a handheld
| calculator, and typing them back out. I had the pleasure of
| being the first person to ever show her sum(). Talk about
| blowing someone's mind.
| tragomaskhalos wrote:
| Anecdotally I believe this sort of thing is terrifyingly
| common - in fact I wonder about the person-millennia of
| effort wasted globally as office workers across the globe
| huff and puff using Excel as a glorified typewriter.
| _dain_ wrote:
| You joke, but: https://www.reddit.com/r/excel/comments/a0wot5
| /excelgore_sto...
|
| >A elderly guy - maybe in his 60s - was writing his book of
| poems on his computer and brought in a floppy disk because he
| wanted some advice on printing. We managed to find a plug in
| floppy drive but there was only an Excel file on the disk. I
| opened the file and he had written his poetry book in Excel
| cells, with widened columns and rows, complete with spaces to
| center text and indent paragraphs etc. When one cell got full
| of text he moved to the next. New poems were started a couple
| of columns over. I remember he also asked how to change the
| size of the font for the initial letter of each verse. He
| must have been using Excel 2003 or something because when he
| saw the ribbon, which was new to Excel 2007 he said it might
| not work properly because he used Excel. I tried explaining
| he should use MS Word. He said "oh I got a disk with that
| on." He pulled out another floppy and there was a file called
| houseke~.doc. I feared the worst. He had a Word table over
| several pages where he kept his home accounts, all
| beautifully typed in by hand, decimal points all lined up
| (hell I can't even do that now), not a calculation in sight -
| they were all done by a calculator and hand-entered.
| smikhanov wrote:
| I wish I would be like that in 20 years (I'm in my 40s now)
| emerged wrote:
| That's pretty artistic whether intended or not.
| themadturk wrote:
| I long for the days of all-in-one word
| processor/spreadsheet/database apps.
|
| Eons ago I owned the long-forgotten Cambridge (formerly
| Sinclair) Z88, their 1987 entry into the laptop market. Its
| main software was called Pipedream, and as I remember did
| everything in what was essentially a spreadsheet, with all
| three application types available in the same file. The
| software was available for DOS as well.
| kickingvegas wrote:
| Side observation; I've found Emacs org-mode to give me a
| lot of the functionality I wanted from combined word
| processor/spreadsheet/database apps.
| jkaptur wrote:
| I once spoke to someone who didn't know about formulas yet,
| so they just used the summary data on the status bar +
| copy/paste: https://support.microsoft.com/en-us/office/view-
| summary-data...
| jbverschoor wrote:
| Always reminds me of this video..
| https://www.youtube.com/watch?v=0nbkaYsR94c Excel has proper
| tables, named columns, better structured data etc.
| detaro wrote:
| The structure of that initial part isn't great given the
| headline, but I don't think its intending to claim that that's
| really new, just fairly unknown. And then talks about about the
| new stuff.
| anamexis wrote:
| The "like you should have been able to 30 years ago" bit
| suggests otherwise, since you have been able to for at least
| 20 years.
| mcdonje wrote:
| Custom formulas have also been around for decades, it just
| required VBA.
| andylynch wrote:
| _but_ it's somehow underused. Often people don't even realise
| they should be creating tables at all. Power query and friends
| are black magic at that point.
|
| It sounds slightly absurd but advanced Excel training is
| something I think many people should do. At $oldfinancejob the
| guys who ran the client professional development business
| clearly picked up on this and ran a very nice 'Excel for
| financial modelling' course as a free intro kind of thing -
| after using Excel for decades there were plenty of things I
| didn't know about and now use. Excel is an incredibly deep
| product.
| lenkite wrote:
| If your data is <10k rows, Excel is great. After that lots of
| stuff stops working. Stick to R or Python data science
| libraries for real work.
| MikusR wrote:
| Which stuff exactly?
| lenkite wrote:
| filtering drop-down 10k list limit is the most obvious
| one. There is also the hard traditional 1,048,576 limit
| which many hobbyist Covid tracking folks ran into.
| lostlogin wrote:
| Hobbyists and the UK government.
|
| They somehow cooked up a spreadsheet that make them hit
| the limits much earlier.
|
| https://www.bbc.com/news/technology-54423988.amp
| feoren wrote:
| > they should be creating tables
|
| Tables are a wart on Excel. They don't fit the established
| idioms at all. There's a very long list of common & simple
| things that either break or get very clunky with tables,
| including:
|
| - Multi-row headers - Merged-cell headers - Headers with the
| same name (sometimes useful) - Formulas that cross rows (e.g.
| iteratively refer to the previous row) - Different table
| sections (e.g. a table-width merged row with one header) -
| Merged rows
|
| The benefit of tables is ... what? Slightly simpler formulas
| when all operands are in the same row? More automatic (and
| annoying) formatting? Almost everything people try to do with
| Tables is actually easier without them, and if it's not, you
| really just want a database.
| tda wrote:
| Merged cells and multi row headers make data processing
| very difficult. These are best avoided in any sheet doing
| any computation. Only for reporting they are useful,
| especially when auto-generated as part if a pivot table.
| But I have never seen a legitimate use of merged cells in a
| computation.
|
| Row referencing formulas work fine in tables, but there
| might be better ways to achieve your goals if you need that
| a lot.
|
| Other benefits are input data type checking, auto "freeze
| panes" for header row, much easier plot and pivot tables,
| niver formatting, summary rows if needed. Best is of course
| referencing columns by name
| tda wrote:
| At my former company I first naivly tried to get more (non
| software) technical experts to use python and databases. That
| was quite an uphil battle, and we quickly pivoted to teach
| them how to make better use of excel. First and foremost: use
| tables. That easily leads to clearly deliniating input data,
| computations and output. Then we proceded to provide template
| sheets that use powerquery to fetch shared input data from
| centralised API's. This way we could let the end user do all
| the work, even though it might be a bit more messy/error
| prone. For well extablished workflows I could then take the
| excel program as a spec, and build a e.g. a webservice from
| that. Ss all the exceptions etc are already dealt with by the
| end-users, you can basically reverse engineer the spec from
| the excel sheet. As long as you can guide them to making
| legible excel sheets, it saves so much misunderstandings vs
| writing specs from scratch and building from that.
| ethbr0 wrote:
| Excel excels as an exploratory / rapid prototyping tool.
|
| Excel fails as an app development and execution platform,
| and specifically one integrated into a core business
| process.
|
| Everyone who's worked in enterprise long enough has seen
| both. It'd be great if there was an enforceable "modern
| mode" Excel flag that kept people from going nuts with
| macros and programmability, while retaining all its
| strengths.
| MrsPeaches wrote:
| > I could then take the excel program as a spec, and build
| a e.g. a webservice from that
|
| That sounds like a potentially great approach for
| consultancy business/product discovery.
| lappet wrote:
| you can already do this with Google Sheets. I wonder if
| Office 365 has similar capabilities.
| henning wrote:
| A lot of SaaS startups with a shitty React frontend should
| actually exist as a PDF report that gets emailed out every
| morning plus a site with a button to upload a CSV/XLSX file for
| tomorrow's report.
| bee_rider wrote:
| If everything that could be implemented as a spreadsheet/PDF
| combination was, what would we do for work?
| fragmede wrote:
| > All of the online comparisons say they're about equal, but
| Sheets doesn't even have proper tables, much less lambdas
|
| I'm not sure what "proper tables" would look like, but Google
| Sheets has JavaScript integration, which is nice (if a bit slow).
| _dain_ wrote:
| >I'm not sure what "proper tables" would look like
|
| This is the third time this has come up on Hackernews in the
| past week.
|
| Excel lets you designate a specific rectangle of cells as a
| named "table" (this is _not_ the same thing as a pivot table).
| Each named column in the table automatically gets its own named
| range, which belongs to a namespace of that table (rather than
| the global namespace). A table will automatically expands its
| bounds when you insert data into cells immediately bordering
| it. Inserting a formula into the first row automatically fills-
| down that formula into the entire column. There is special
| syntax for referring to rows, columns, and ranges of columns
| within a table. Look up "structured references".
|
| They are very useful. They make big spreadsheets tractable and
| maintainable. Think of them as mutable, expandable, reactive
| dataframes. If you're dealing with inherently tabular data in
| Excel and you're not using the tables feature, you are doing it
| wrong.
|
| LibreOffice Calc and Google Sheets do not have them, to my
| endless bafflement.
| pseudosavant wrote:
| Not having proper tables in a spreadsheet app is like a
| programming language not having arrays.
|
| You can get by without them through countless crappy hacks,
| but nobody would ever choose a language that couldn't support
| arrays.
|
| IT chooses Google Sheets for whole companies every day even
| though they aren't sophisticated, or often even daily, users
| of any of the apps.
| hwayne wrote:
| In excel you can convert any range into a dedicated table
| object, which lets you give it a proper name, select
| rows/columns (with ctrl/shift+space), and use column names in
| formula (with `tablename[columnname]`). You can also load the
| table into powerquery to do things like decompositions or
| augmentations without modifying the original data.
| quacked wrote:
| ^ seconded. If you learn tables with Excel you become a
| demigod, and if you learn Power Query with Excel tables you
| become a being of pure energy and are then tasked by
| management to fix every data problem in your organization.
| janvdberg wrote:
| Obligatory Joel Spolsky Excel video:
| https://www.youtube.com/watch?v=0nbkaYsR94c
| nashashmi wrote:
| And then place in C1 =A1:A3 + B1:B3, you'll get C1=3,
| C2=7, C3=11. Now, if you place in D1 =C1^2, you'll just
| get D1=9. But if you instead place C1#^2, it
| instead applies to C1's spillover array, meaning you now
| have D1=9, D2=49, D3=121. Oh also if you
| instead do C1 = A1:A3 + TRANPOSE(B1:B3) you get this:
|
| When excel launched spill arrays in beta I was super excited, and
| immediately searched all of the ways this could be used, but the
| author really has shown me something I never thought of. I no
| longer have to drag and drop formulas like I used to. Hurray!
| iLoveOncall wrote:
| Sure, now try to open a CSV that has line breaks in some cells
| (which is something basic and that follows the CSV spec) and tell
| me if Excel is still cool.
|
| https://stackoverflow.com/questions/2668678/importing-csv-wi...
|
| It is literally impossible. The best solution is "Import it in
| Google Sheets and export to Excel format"...
|
| Excel is powerful, but it is not cool at all, the UX of even
| basic features is awful and some of the most used functions have
| annoying behaviors (VLOOKUP when the data type of the 2 columns
| is not exactly the same for example).
| navjack27 wrote:
| Xlookup exists
|
| CSV of all types can be imported with power query.
| bee_rider wrote:
| RFC 4180 came too late. I think CSV is more commonly taken to
| be just a general description of the data, than a reference to
| a particular standardized spec (I mean it is notorious for ad-
| hoc implementations, right?).
| bzxcvbn wrote:
| It sounds like SO isn't really an expert on Excel. The correct
| answer (Data > From Text/CSV) is sitting at the bottom with 0-1
| votes. Most answers are from the early 2010's and outdated.
| iLoveOncall wrote:
| This does not work even in Excel 2019.
|
| In any case, not supporting when you just double-click a CSV
| to open it with Excel means that it's not usable for the vast
| majority of users.
|
| We faced the issue when we were exporting data to customers
| in CSV format, and "Replacing new lines with '/'" ended up
| being a preferred solution over having the user perform any
| action.
| bzxcvbn wrote:
| Maybe stop using outdated software?
| iLoveOncall wrote:
| The mainstream support for Office 2019 ends on October
| 10, 2023 and the extended support on October 14, 2025.
| It's not outdated software.
| bzxcvbn wrote:
| Supported doesn't mean current. They'll fix security
| holes and help paying customers use the software as it
| is, but that is it. Not introducing new features or
| backport improvements.
| _dain_ wrote:
| >It is literally impossible.
|
| You can do this in Powerquery.
|
| >VLOOKUP when the data type of the 2 columns is not exactly the
| same for example).
|
| Use XLOOKUP.
| iLoveOncall wrote:
| > You can do this in Powerquery.
|
| Yeah, pretty sure that having to use an ETL layer before
| Excel qualifies as "It's literally impossible to do it in
| Excel".
|
| > Use XLOOKUP.
|
| It's only available after Excel 2019, and the behavior is
| different. There's also nothing that seems to indicate that
| it doesn't have the same issue with column types?
| cfiggers wrote:
| > Use XLOOKUP.
|
| Use INDEX and MATCH.
| papandada wrote:
| Excel literally has the power to perform better than multimillion
| dollar consultant implementations on all kinds of things (having
| been on those projects and unable to supplant Excel
| successfully).
| eastbound wrote:
| But is hard to audit. Web apps are committed in Git and have
| code reviews and client testing. If Excel made it possible to
| commit the formulas into Git, it would have taken over a
| million of accounting apps and SAP for 50 additional years.
|
| But the lack of reliability of "a spreadsheet made by John in
| Accounting" sorely limits the success it can have.
| tfehring wrote:
| You can use git hooks or CI tools to unzip xlsx files when
| they're committed and run an autoformatter on the resulting
| xml files. The xml diffs aren't the most readable, but
| they're usable.
| conductr wrote:
| Isn't this why people always save excel files a certain
| points of time? So you can go back to it and see it's state
| at certain "commit" points?
|
| For me, it often looks like this.
|
| 2023 Budget v3 - 2022.08.01.xlsb
|
| 2023 Budget v3 - 2022.08.05.xlsb
|
| 2023 Budget v3 - 2022.08.08.xlsb
|
| 2023 Budget v3 - 2022.08.12.xlsb
|
| The dates indicate minor updates/changes to the data (eg. the
| database is different but the logic is the same). Where as
| the version number is a change of business logic &/ formulas
| &/ file architecture (so v3 files are usually compatible with
| v3 files, but v4 files may break everything when comparing
| back to v3).
|
| Granted, it doesn't help any with collaboration. My team
| still passes around the "current" file and deal with read-
| only locking issues (we find shared mode to be highly broken
| and conducive to file corruption). But, it does help with
| auditing because we can always go back and find why a number
| was what it was on any given day. So in that sense, Excel
| documents are as auditable as open source software. You have
| to want to read the code but it's all there for the reading.
| Valgrim wrote:
| Integration into Office365 pretty much integrates
| collaboration and automatically saves every change, so you
| can go back to any version back in time.
| airstrike wrote:
| Except "automatically" is always less transparent than
| explicit manual saves
|
| We want to save after Big Change X has been made, not at
| a random interval in time.... so everyone at the office
| rightly disables autosave
| conductr wrote:
| Same. Also the problems I've encountered when one file
| tries to autosave, triggering a calculate before save,
| when I am actually working in something large that is
| calculation sensitive (manual calculation mode is on),
| and my whole application freezes/locks because it
| calculates when I'm not expecting it. Always happens 5
| minutes before a deadline too. So yeah, disable that.
|
| It's also a Workbook level setting so, you might have it
| turned off, but someone sends you a file with it enabled.
| For that reason, I have a macro that disables it at Open
| for all files (same for calculate before save, I really
| dislike that setting too).
| polmuz wrote:
| Shameless plug:
|
| We built xltrail[0] (cloud and self-hosted SaaS) that lets
| you see the diffs for sheets, VBA, and a couple other things.
| You put in your git repo and it just works. You can also have
| a manual versioning option where you upload new versions of
| the same file and you get the same result.
|
| We also have the open source git extension Git XL[1] that
| lets you see VBA diffs locally.
|
| [0] https://www.xltrail.com/
|
| [1] https://www.xltrail.com/git-xl
| decafninja wrote:
| I've heard traders at banks literally yell something like
| "Just fire the entire useless IT department and just let me
| use my Excel!"
| analog31 wrote:
| The IT department is limited by Turing's famous Halting
| Problem:
|
| _Given a complete set of requirements and a working
| prototype, will the IT department ever finish writing
| software that meets those requirements?_
|
| Okay, that was sarcastic, but illustrates a real problem,
| which is that doing it in Excel is _making_ something,
| whereas getting IT involved is _managing_ something -- a
| category error. The problems of managing software
| development are unchanged, half a century after _The
| Mythical Man Month_.
| macintux wrote:
| I have to say, "complete set of requirements" is quite
| the oxymoron.
| analog31 wrote:
| Indeed, communicating requirements is one of the hardest
| parts of managing anything technical. Especially if
| you're working with people who are a couple steps removed
| from the background domain knowledge.
| [deleted]
| renewiltord wrote:
| Haha, but traders are traders. What they say is mostly an
| effect of whether their stuff is performing or not.
| Seanambers wrote:
| IT departments has a perverted love of locking everything
| down, which makes basically everything useless except
| what is already provisioned.
|
| Asking for software is like a gestapo interrogation -
| your always not right and in the end all motives are
| questioned.
| renewiltord wrote:
| Haha, I can totally believe that at a big firm. I'm at a
| prop shop and we're much more outcome focused than that.
| TAForObvReasons wrote:
| https://support.microsoft.com/en-us/office/overview-of-
| sprea...
|
| Spreadsheet Compare, an official diff tool, is excellent
| kurupt213 wrote:
| that's what internal validation processes are for
| [deleted]
| jaygray0919 wrote:
| We store our XLSX files as XML file on GitHub and take
| advantage of all GH services. Excel will re-render a modified
| XML file.
| hbarka wrote:
| That's not true. You can validate outcomes. This is what
| accounting auditors do. Do you insist in doing a code review
| of your bank statements?
| adamsmith143 wrote:
| Sounds great until you have multi-million dollar programs
| running off single point of failure spreadsheets with little or
| no backup or overwrite protection.
| kevmo314 wrote:
| That sounds pretty great. I'd love to have a multi-million
| dollar program.
| insane_dreamer wrote:
| how do you do versioning and review?
| fzumstein wrote:
| We built https://www.xltrail.com to make Excel work with Git.
| vlunkr wrote:
| You give your files helpful names like:
|
| sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx
| insane_dreamer wrote:
| LOL. And diffing?
| HPsquared wrote:
| There's always Spreadsheet Compare, which is Microsoft's
| attempt at a diff utility for Excel. It works fairly
| well.
|
| https://support.microsoft.com/en-us/office/overview-of-
| sprea...
| lpapez wrote:
| Why of course you open two windows one on top of another
| and you Alt-Tab between them rapidly to see the
| differences.
| dogma1138 wrote:
| Obligatory Power Point is Turing complete plug.
|
| https://m.youtube.com/watch?v=uNjxe8ShM-8
| hgsgm wrote:
| kretaceous wrote:
| The talk, Rethinking Reactivity by Rich Harris[0] is an amazing
| talk which talks about how spreadsheets were the OG reactivity
| software. Highly recommended.
|
| 0: https://www.youtube.com/watch?v=AdNJ3fydeao
| jkaptur wrote:
| > All of the online comparisons say they're about equal, but
| Sheets doesn't even have proper tables, much less lambdas or a
| "get yoga pose" button.
|
| I was on the Google Sheets team from about 2011 to 2018 (opinions
| are, of course, VERY much my own). I worked in finance before
| that, so don't worry, I'm aware of Excel's features.
|
| One thing that I found interesting is that, over my time on
| Sheets, I didn't perceive much change in the volume* of "Sheets
| is missing critical features" criticism, but I did notice a
| marked difference in the features that the critics brought up.
| It's pretty cool that "get yoga pose" now makes the list - it's a
| long way, for example, from the things Joel Spolsky noted (in
| 2006) that "you can't really do well in a web application":
| https://www.joelonsoftware.com/2004/06/13/how-microsoft-lost....
|
| * In either sense, really.
| vxNsr wrote:
| This blog post by Joel is a wealth of knowledge that does a
| great job of presciently explaining the last 15 years of
| Microsoft.
|
| He even offers a great reason for why IE stagnated for years:
| MS was afraid of the web and felt sabotage was the best way to
| prevent it from flourishing. The only thing they actually
| accomplished was giving google a browser monopoly.
| kriro wrote:
| The one thing that drives me crazy about Excel is that function
| names are localized. So if you use the German version you'll have
| to use "SUMME" instead of "SUM" etc. this is really annoying and
| I wish there was a way to always use the English word. I'd love
| to talk to the person who made the decision that this was a good
| idea. In the CAS tool Rhino, you can always do _command to use an
| English command even in localized versions...wouldn't be that
| hard to provide this feature.
| sbierwagen wrote:
| Excel is for end users. Not every Microsoft customer speaks
| English.
| ezconnect wrote:
| English is not universal, that's the main reasoning for that.
| You would be amazed during the decade of "Internationalization"
| it was the main talking points of every magazines and computer
| shows.
| the_af wrote:
| > _English is not universal_
|
| I think only because Excel is not, at its core, a programming
| tool.
|
| For programming languages, English is universal. You are
| pretty much forced to learn English in order to take your
| first programming steps, and -- as a non-native speaker -- I
| find this is a _good_ thing. This way, we have a lingua
| franca of programming.
|
| It's not because of a particular love of English. It is what
| it is. I would have welcomed French or Italian or whatever
| had it been the lingua franca instead. I would probably NOT
| have welcomed Japanese or Chinese written with ideograms
| because those writing systems are completely extraneous and
| hard to match for my Western brain, but anything else is fair
| game.
|
| A "Tower of Babel" situation would be the worst.
| LtWorf wrote:
| They should support both spellings, and a file saved in
| germany should work in france.
| layer8 wrote:
| Sometime in the 90's, even VBA keywords were localized.
| zweifuss wrote:
| Yes, this happened in Excel 5/95. The first version with VBA
| in addition to Excel 4.0-Macros. By the way, Excel 4.0-Macros
| were also localized. The backlash was nearly immediate
| because localized xls-files were not always interchangeable.
| Support for localized VBA was removed in Excel 97.
| jmt_ wrote:
| I never knew Excel localized function names. If I have an xlsx
| that uses a German-localized function like SUMME, will it error
| out when ran on an English-localized machine? Since they can
| localize function names, effectively mapping (presumably)
| English function names to characters in each supported
| language, shouldn't an inverse function be feasible? One that
| would map SUMME to SUM, or some other unique identifier?
| tomsmeding wrote:
| Under normal circumstances it gets mapped to the right
| localised variant when the sheet is opened on a different
| machine. So on your own machine you see and enter the things
| you're used to. The issues come when working on someone
| else's machine, or when that automatic process fails for some
| reason -- a friend of mine sometimes runs into that, where it
| just inexplicably... doesn't.
| simlan wrote:
| I am also annoyed by this and just resort to an all English
| office installation most times. However, for the office user
| market it is a clear advantage. This way anyone can pick up
| the tool and discover.
| rvba wrote:
| It works 99,9% of the time.
|
| There are few obscure functions that have parameters and
| those parameters can come in home language - so they will
| stop working. Also there are some issues with charts.
|
| In general Excel is made for corporations and people from
| different countries send each other files all the time.
|
| However it would be nice to be able to dynamically switch
| language. There are few websites that translates formulas.
| mhd wrote:
| Is there a way to do the variable assignment inline? I mean, it's
| nice to have an overview of all of them, but it would be neat to
| get them in there by just "tagging" a cell (preferably
| textually).
| mechanical_berk wrote:
| Not sure if this is what you mean but IIRC you can give the
| current selection a name by just typing it into the box in the
| top-left (the one that shows what is currently selected).
| memcg wrote:
| Excel is my Swiss army knife. I started using Lotus 123 in the
| late 1980s to automate data input via the serial port from
| scales, pH meters and such in a PCB shop. Also used it for
| editing Gerber drill and router files. Later, I used Excel with
| VBA and Sysinternals utilities to manage desktops and VMs. I
| still use Excel weekly with a barcode scanner to track household
| stuff.
| pete_nic wrote:
| VisiCalc was conceived in 1978, before the internet and all the
| data that comes with it. Excel has had to catch up with these
| developments and is finally doing a good job with it.
| roflyear wrote:
| Excel is great. It is so good most business apps at some point
| ask you to replicate many of its popular features (though most of
| these features are not exclusive to Excel by any means).
| [deleted]
| __warlord__ wrote:
| Yes, but when are they fixing the regression in character spacing
| when mixing unicode and non-unicode?
| https://www.youtube.com/watch?v=xubbVvKbUfY
| mguerville wrote:
| As someone who lives in Excel, has done quite a bit of BI as
| well, and is learning programming I love that Excel gives you the
| ability to build complex algorithms but also to sometimes bypass
| it and just hardcode some stuff that would require complex loops
| or recursion, and create exceptions by breaking a formula into a
| few pieces in different cells that would likewise require a lot
| of ugly code.
|
| It empowered me to build what I needed, but may have held back my
| progress in building better mental models for data schemas and
| algos. You win some, you lose some I guess.
| jeffwask wrote:
| It may be the greatest, most feature rich application ever
| written
| spitfire wrote:
| I'm surprised this thread got so large without anyone mentioning
| Lotus Improv or Quantrix. Quantrix was the later re-
| implementation of Lotus by lighthouse design, after Lotus had
| canceled Improv.
|
| Improv/Quantrix was a multidimensional spreadsheet, Each model
| could contain multiple tables, and of course multiple views.
| Formulas were separate from data, so it was easy to audit a
| model. Clicking on a formula (in 1991) showed you all the related
| input and output sources.
|
| It was the first spreadsheet with pivot tables.
|
| Of course since it's secretly a database down below, you could do
| databasey things like joins and group by. In 1991.
|
| Videos of using Improv.
|
| https://www.youtube.com/watch?v=TbsfvdZXE7s
|
| https://www.youtube.com/watch?v=TbsfvdZXE7s
|
| https://www.youtube.com/watch?v=lTko_Pt2ZZg
| bambax wrote:
| > _Instead of writing the formula =A1*B1, you can do
| =Width*Height like you should have been able to 30 years ago._
|
| I don't have access to super old versions of Excel, but I just
| confirmed that it worked perfectly well in Office 2003. I believe
| it's been the case forever.
|
| (I wrote whole apps in Excel4 super-weird macro language in
| 1992-93 and distinctly remember we had named zones.)
| wesnerm2 wrote:
| Former Excel 97-XP developer here. Excel 97 had English
| language formulas. It automatically detected names in tables;
| however, it was buggy. The feature was removed in a later
| version (2007?).
| Someone wrote:
| Microsoft Multiplan for CP/M supported that in 1982 (http://www
| .bitsavers.org/pdf/microsoft/cpm/Microsoft_Multipl..., page 99)
|
| I think later versions of VisiCalc did, too.
| adrian_b wrote:
| Already Lotus 1-2-3 for MS-DOS had named ranges.
|
| As Excel was made since the beginning as a replacement for
| Lotus 1-2-3, I believe it also must have had named ranges at
| least from its version for MS Windows 3.0.
|
| IIRC, when I have first used Excel, which was the Windows 95
| version, it had them.
| pwinnski wrote:
| I recall it _not_ have named ranges initially, but I remember
| them being added pretty quickly. Not sure what year, but
| _very_ early.
|
| The name manager was added in Excel 2007.
| jamal-kumar wrote:
| At one job doing data centre forensics I had spent like half a
| year building up excel spreadsheets on what needed to be
| investigated... Then I realized the date formats were American,
| so I changed my system clock to use dd/mm/yyyy instead...
|
| This automatically mangled the date in every single file I opened
| from there on and left me having to redo all that work. Fun!
|
| A bunch of what I do for work right now is writing code which
| actually generates spreadsheets for end users to use but
| personally I wish that Excel was more like a relational database
| with more data safety guarantees and I'm glad I don't have to use
| it every day.
|
| Has Microsoft integrated python into it yet? Does that embedding
| come with a full networking stack? That choice always struck me
| as kinda wild, as if there isn't enough security problems as-is
| with this stuff... It kind of seems like Microsoft's whole plan
| to make Excel more secure is trying to move everyone to that
| cloudy office 365, but I just use libreoffice anyways
| kube-system wrote:
| I always assume that if dates/times aren't ISO 8601, then
| they're not in the format I'm expecting.
| guhidalg wrote:
| Better reject those ISO8601 timestamps if they don't have
| time zone specifiers too! In one case, I wrote an API that
| rejected anything that wasn't in UTC time just to make
| everyone's life equally uncomfortable.
| cja wrote:
| I really don't get this. It does nothing when I press
| Ctrl+Shift+V. If they can't even implement that, what's cool
| about it? The resulting formatting mess certainly isn't
| stuckinhell wrote:
| It is and it isn't.
|
| For one of my first jobs, I had to spend a lot of time converting
| excel into python.
|
| Excel is great for small places, but the moment you need to have
| oversight and accountability in an organization. You need a
| different centralized, permission controlled, and auditable
| solution.
| narush wrote:
| Totally agree that audibility is an important part of the story
| here!
|
| Having spent the past 2 years building a spreadsheet [1], it's
| really interesting how often we run into design problems that
| pit "audibility" against "what you expect from a spreadsheet."
|
| A simple example: imagine you add a filter to column to remove
| null values, you then go and create a new formula that is
| dependent on this filtered column, before finally going back to
| edit the filter.
|
| On one hand, effective audibility usually implies a nice,
| linear story you can follow and understand. On the other hand,
| users expect filters to update based on the most recent values
| - but the filter is applied in a way-old step. In practice,
| there's a bunch of extra state you have to store to make sure
| things work properly, and even then, there are lots of foot
| guns!
|
| [1] https://trymito.io
| hgsgm wrote:
| qsort wrote:
| I think the fatal flaw is that there is no upgrade path towards
| sanity. If I start from a jupyter notebook or a script or
| whatever, it's trivial to build upon it. In Excel it's just an
| endless descent into madness.
|
| All those funky features are just bandaids over a fundamentally
| wrong abstraction.
|
| We have to acknowledge it's pretty much the only real tool for
| citizen developers out there, and as such it deserves some
| credit. But I don't really think it has any real advantage over
| $(your favorite scripting language); if you are proficient at
| coding I don't see why you would implement anything nontrivial
| in Excel given the choice.
|
| The truly infuriating thing is that it wouldn't take much for
| spreadsheets to become literal 10x magic, but it's one of those
| path-dependent things. Sad.
| plonk wrote:
| > But I don't really think it has any real advantage over
| $(your favorite scripting language); if you are proficient at
| coding I don't see why you would implement anything
| nontrivial in Excel given the choice.
|
| If you wanted to implement a spreadsheet, you'd be wasting
| your time with Python though. I'd rather manage and visualise
| my monthly budget in Excel.
| mhd wrote:
| > You need a different centralized, permission controlled, and
| auditable solution.
|
| So, Access? ;)
|
| But seriously, I really miss "PC" databases, Clipper etc.;
| Would often fill niches between spreadsheets and webapps.
| (Airtable etc. are trying to do part of that, but I'd say
| that's more a symptom than a solution)
| bee_rider wrote:
| It seems like quite a flaw in Microsoft's ecosystem that those
| requirements can't be met by some integration between Excel
| and, like, Sharepoint or whatever.
| gchamonlive wrote:
| Excel as a product and a brand is very confusing.
|
| On the one hand you have all these great features, but on the
| other I can't even import a simple spreadsheet with checkboxes to
| excel online without breaking it because of active objects not
| being supported online.
|
| It rides on the massive weight Microsoft market share has, so it
| doesn't have to be portable, open and consistent.
| april_22 wrote:
| 100% agree. Is there any good alternative with the same kind of
| functionality? I don't know of anything.
| gchamonlive wrote:
| I really have no idea. I sometimes receive excel forms from
| clients and contractors that even though they are nothing
| fancy they won't open on excel online. I am a full time
| DevOps and systems architect can't work without Linux you
| see.
|
| So sometimes I have to resort to a Windows VM and Excel, the
| real deal to fill out those forms.
|
| I used wine a long time ago, but it would only support a very
| specific, very ancient version of Excel, so a VM is actually
| less of a hassle (when already prepared. The process of
| installing windows on a VM was quite frustrating for me).
|
| But no, I don't think there is any substitute to excel.
| Microsoft made sure of that.
| abrichr wrote:
| Can you please describe your use case?
| extr wrote:
| I know for me, I have a personal budgeting spreadsheet that
| is fairly complex. I'd love to simplify aspects of it with
| macros and checkboxes. However I can't use any macro-
| related features because then it becomes incompatible with
| the web interface, and I sometimes like to use that when
| working on Linux.
| vangelis wrote:
| Google Sheets REGEX* and QUERY kick ass. I don't want to use
| PowerQuery, I want to use formulas.
| bbkane wrote:
| If only Google Sheets let you name cells and ranges!
| onlyrealcuzzo wrote:
| Wait. Can't you name ranges in Sheets??
| hwayne wrote:
| You can name cells in the upper left corner and name ranges
| under `Data > Named Ranges`. You can't create a value
| that's not part of a cell or range (which you can do in
| Excel under the Name Manager.)
| oxfeed65261 wrote:
| I use regular expressions all the time in Excel, with the
| relevant functions implemented in VBA.
| oger wrote:
| Over the years I have probably tried (and used) every feature
| that was added to Excel.
|
| With growing wisdom I came to the conclusion that I will only use
| the most basic features (i.e. simple formulas using adding,
| subtraction, multiplication, division) when I need to share a
| sheet with third parties. Nearly all users are not proficient in
| the more advanced features - making collaboration an error prone
| hell.
|
| To make things worse: Excel by nature is not made to be audited.
| That's why I tend to add a generous amount of checksums or
| similar (visual) flags to my worksheets in order to catch errors
| early.
|
| Excel is dangerous!
| bloaf wrote:
| The dangerous things about excel, imho, are:
|
| 1. Too many different places for functionality to hide. Are
| these numbers updated by external links? VBA? Pivot tables?
| Equations? Some addin? 2. Difficult to version control. To
| capture all the functionality its not enough to just strip the
| VBA code into git, you need to track a lot of other config
| variables related to the features in 1. 3. Crap accretion.
| Complex spreadsheets have a tendency to accumulate broken
| external links, redundant named ranges, and extraneous
| scratchpad sheets as people copy data around. This makes
| troubleshooting harder just by increasing the amount of noise.
| scubakid wrote:
| Excel is great until suddenly it's not. When I started building
| ProjectionLab, it began as an excel spreadsheet that (at first)
| was easy to share and explain to friends and family who wanted a
| better retirement plan. But as they asked for more flexibility
| and new features, it spiraled out of control into a complex web
| of advanced Excel features that became opaque and unmaintainable.
| No doubt Excel is powerful and feature-rich, but sometimes
| layering on more and more of those advanced features isn't the
| answer.
| max59676 wrote:
| Would it be any different if you used a different tool and/or
| programmed everything manually?
|
| In my experience, it would be 50x worse because Excel handles
| for you a lot of things that are extremely time consuming to
| code manually. (eg. error handling)
| scubakid wrote:
| I guess it depends on what you mean by worse. In my case, I
| abandoned excel and rebuilt the tool as a web app using
| Vue.js, Chart.js, Firebase, etc., which is what ProjectionLab
| is now. More overall development work, but the end result is
| way better than a spreadsheet.
| proamdev123 wrote:
| But would you say using that web stack initially would have
| been overkill?
| LtWorf wrote:
| Maintainability >> everything else.
|
| Basically on the long term the possibility to use real code
| that is easy to change and understand will save you
| incredible amounts of time. If you reach a point where your
| code is too complicated that you are afraid to change
| anything, you're basically stuck.
| cookie_monsta wrote:
| I was never really a big fan of Excel, but since I started
| working at a company that does literally _everything_ in 365, I
| 've come back around - power queries, the graph API, power
| automate and BI put a lot of tools at your disposal. Having
| typescript as the builtin scripting language is nice for me, too,
| because I never really liked vba
|
| https://developer.microsoft.com/en-us/graph/
|
| https://make.powerautomate.com/
|
| https://docs.microsoft.com/en-us/office/dev/scripts/
| beckingz wrote:
| Going all in on microsoft is a solid move. It's a powerful
| platform.
|
| Going half in on microsoft is terrible.
| lostlogin wrote:
| All in may be fine for certain businesses, but surely most
| companies end up needing some software MS doesn't provide?
|
| And 'all in' implies usage of Teams..,
| ethbr0 wrote:
| The squicky thing about the Microsoft online ecosystem of tools
| is their first party vs everything else approach. Where first
| party is easy and everything else is a second class citizen.
|
| I'd feel a lot more comfortable if they categorically committed
| to only using APIs for their products that were also open to
| everyone. Have they done this?
|
| Graph also seems unstable in regards to refactoring and
| deprecating over the past years. Look at something simple like
| "set up a trigger when a user receives an email." It's been
| through 2+ architecture changes (nothing, webhooks, delta,
| subscriptions?). Or Teams API coverage (beta or not?).
|
| But most of my feelings are probably classic HN "Has MS really
| turned a page, or are they extending 90s behavior via cloud
| lock-in, with a veneer of nice tools?" paranoia. And only the
| future knows the answer.
|
| Disclaimer: Work for a company that competes with Microsoft in
| some areas. We see a lot of customer Azure AD admins
| floundering in supporting non-MS integration.
| renatovico wrote:
| My use of sheets is crazy insane, my team of 180, use the sheets
| for everything, instead of create a crud for control my system, I
| use sheets and connect with retool ou via api and have instant
| app, instead of create a complex app for assign tasks, generate
| the queue with app scripts and big query, and have a nice and
| simple interface for the team use
| tomjakubowski wrote:
| That's awesome. Has your team written more on it anywhere?
| MrsPeaches wrote:
| Is this what you use?
|
| https://retool.com/
| insane_dreamer wrote:
| heretic
| photochemsyn wrote:
| Python's pandas makes working with large numbers of Excel /
| LibreOfficeCalc files a lot easier. Excel is very convenient for
| the non-programming user of course, for example making graphic
| visualization is far easier. With pandas you need to interface
| programmatically with matplotlib or similar.
|
| If you want to up your skills in this area, Wes McKinney's
| "Python for Data Analysis" is pretty great.
| proamdev123 wrote:
| +1 for "Python for Data Analysis".
|
| And Wes (the creator of the Pandas library) has made the book
| available for free on his website!
| di4na wrote:
| I am surprised that you did not use the L1C1 syntax Hilel, as it
| would probably make it a bit easier too. As it allows proper
| relative work.
| hwayne wrote:
| I never said I was _good_ with Excel :P
| antupis wrote:
| Now when I think somekind spreadsheet wrapper around sqlite would
| be pretty cool.
| hairofadog wrote:
| My dream is either what you're describing here or a company
| like BareBones (of BBEdit) making a data crunching app with no
| formatting whatsoever. People send me large spreadsheets all
| the time and Excel (at least on a Mac) has a hard time keeping
| up.
|
| I suppose I should just get better at using numpy and pandas.
| reportgunner wrote:
| You can just connect to SQLite directly from excel via ODBC.
| smm11 wrote:
| I once thought Aldus Pagemaker WAS the Mac.
|
| Lots of people thought AOL WAS the Internet.
|
| And to this day many think EXCEL is everything.
| narush wrote:
| Excel is an extremely cool piece of software. As someone who
| spent the past two years of his life implementing a spreadsheet
| [1] (built to solve many of The issues with spreadsheets
| mentioned in this thread: data size limits, speed limits, and
| repeatability problems), it's unbelievable how many different use
| cases are supported by just Excel.
|
| One of the funnier Excel use cases I've seen in the wild, when
| talking to users, is someone who had implemented the game 2048
| entirely within a spreadsheet (ofc VBA). And it wasn't just for
| fun - they weren't allowed to play games on their work computer,
| but they needed their daily gaming fix. If you can dream it,
| Excel can probably get it done...
|
| [1] https://trymito.io
| zamadatix wrote:
| I bet... if you use a column as a list of the wasd inputs (i.e.
| "w enter" records as an up move) you could get 2048 without VB.
| Maybe even without the new lambdas but it'd be significantly
| easier thanks to that. A new thing for the "I better not waste
| my time on this" list ;).
| captainmuon wrote:
| > Excel has actual programming affordances now
|
| In case the Excel devs read this, one thing I've always wanted is
| the ability to define a new function by referring to cells
| containing a calculation. Not in a separate window, but in sheet,
| so you can use multiple cells to break it up nicely.
| A B C +-------------------------
| 1 | =VARIABLE(X) 2 | =A1*2 3 | =A2+1
|
| And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)`
| in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or
| `=EVALUATE(A3; 'X')` or something.
|
| I often have complicated, multi step calculations. Currently I
| copy-paste them for every row (and Excel helps with keeping the
| formulas in sync). But it would be great if you could do it once
| on an example (maybe even on another sheet) and then turn it into
| a custom formula.
|
| (I used to think ancient Excel 4 macros were that from what it
| looked like, but unfortunately they are something completely
| different.)
| downboots wrote:
| Custom formulas/functions exist using VBA
| MikusR wrote:
| https://www.microsoft.com/en-us/research/blog/lambda-the-ult...
| hwayne wrote:
| It's not a perfect solution to your problem, but you can
| simplify multistep calculations with LET. So it would be:
| LET(X, A1, Y, X*2, Y+1)
|
| I just checked and the Evaluate Formula button can sequentially
| step through a LET. Though this doesn't give you intermediate
| results you can use in other calculations.
| tlb wrote:
| My experimental spreadsheet does something like this. You can
| refer to the value of a cell given some different values in
| other cells, and it calculates the value of the cell given just
| those changes. Syntax is cellname{othercell=othervalue, ...}.
|
| So you can use a group of cells like a function with parameters
| (with defaults).
|
| Fizzbuzz example:
| https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb Use
| right and left arrow keys to scrub through the program's
| execution. The help link in the NE corner gives more details.
|
| (This is still evolving, and uses WASM to run in the browser so
| it's desktop only. LMK if it doesn't work for you.)
| rad_gruchalski wrote:
| You can also use lambdas: =LAMBDA(... args,
| calculation)
|
| Those can be defined under Formulas > Name manager.
| g8oz wrote:
| Will LAMBDA be available for the standalone Office 2021
| though? Or is it only going to be there for the subscrption
| based Office 365?
| [deleted]
| _dain_ wrote:
| Use the LET formula. It gives you lexically scoped variables
| inside a formula. =LET(foo,
| ComplicatedExpression(), bar,
| AnotherComplicatedExpression(), baz,
| AnExpressionInvolvingPreviousVariables(foo, bar),
| ExpressionThatIsTheReturnValue())
|
| Excel now also has (will have?) a LAMBDA formula for defining
| your own functions.
| Someone wrote:
| That's more or less how Lotus-123 did that kind of thing.
|
| As others said, you can use lambda, but the 'old modern way' to
| do that is to write a function in VBA and call that
| (https://stackoverflow.com/a/16296990)
| scubbo wrote:
| > Sheets doesn't even have proper tables, much less lambdas
|
| I never formally studied computer science, so it's possible that
| I'm missing some distinction between lambdas and functions, but
| doesn't [0] indicate that Google Sheets supports custom named
| functions?
|
| [0] https://developers.google.com/apps-
| script/guides/sheets/func...
| jcrawfordor wrote:
| The simplest answer is that "named functions" and "lambdas" are
| mutually exclusive, since a simple definition of a lambda is an
| "anonymous function," or one that doesn't have a name. That's
| all both practically and technically correct but I call it the
| "simple" answer because it kind of misses the point that named
| functions and lambdas are used in different ways. One
| articulation would be that named functions serve primarily as a
| means of reuse (to avoid repeatedly writing out the same thing)
| while lambdas serve primarily as a mean of containment (both in
| terms of scope and to package up a bit of logic in a form that
| can easily be passed).
|
| This is all a bit more complicated in the spreadsheet world
| because spreadsheets have historically maintained a separation
| between "the document" and "extra code." In Excel, this is the
| separation between the spreadsheet itself and VBScript macros
| which might be packaged with it. In Google Sheets, it is the
| separation between the spreadsheet itself and AppScript.
|
| In Excel, it is possible to implement functions _in the sheet_
| by use of the LAMBDA function in a cell. Amusingly, because of
| Excel 's general concept of named cells, these lambdas can
| actually have names if you want, and can be called by those
| names. This allows easy implementation of e.g. recursive logic
| within the sheet, something that was not historically possible
| without the use of VBScript.
|
| Google Sheets still doesn't have that capability, and the
| functions you mention are an AppScript feature that can be
| called from within the sheet.
|
| You will notice in reading this that part of the confusion is
| that the difference between a conventional "programming
| language" and the spreadsheet environment means that the terms
| "function" and "lambda" have somewhat different meanings in
| spreadsheets than in a more general-purpose programming
| language.
| dreamcompiler wrote:
| > a lambda is an "anonymous function," or one that doesn't
| have a name.
|
| I'd frame this slightly differently. The process of defining
| a function involves:
|
| 1. Creating a function object.
|
| 2. (Optional) Assigning a name to it.
|
| Lambda is Step 1, and it happens even in programming
| languages that don't have a so-called "lambda" feature. Such
| languages always automatically do both steps. Those that do
| have "lambda" simply make it available to the programmer
| explicitly, and programmers who use it usually omit Step 2.
|
| In Scheme and occasionally in Common Lisp it's not unusual to
| perform both steps explicitly and use lambda to create named
| functions. It's fairly common in modern Javascript too.
|
| The thing that's new in Excel (since 2020) is the ability to
| create new functions _at all_ and it 's reasonable to just
| call that "lambda."
| [deleted]
___________________________________________________________________
(page generated 2022-08-12 23:00 UTC)