Excel is pretty dang cool
buttondown.email
buttondown.email
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.
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.
The cell formulas were actually stored in Reverse Polish Notation with each function represented as a number. The actual ASCII you entered was never stored anywhere.
It's probably the most interesting and amusing file format detail I ever stumbled upon.
The name you type in - say - a German version is saved as a "function number" on file.
When the file is opened in - still say - an English version, these "function numbers" are rendered as the English names.
I never understood this argument. To use a function you have to look up its name anyway*, so what does it matter which language inspired the name? I wouldn't mind if sum() was known as hezrtsh() as long as it was consistently so, I could have a chance of learning it.
----
* I still sometimes struggle to remember if it's average() or mean() and whether it's count() or length() -- different programming environments use different names. So even as an English speaker, I have to look up the word for each specific programming environment.
"Instead of writing the formula =A1B1, you can do =WidthHeight 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.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
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.
> 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-...
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
>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.
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.
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Forgive the spruik but while we’re here: https://www.learnwithlucy.rocks/courses/excel?coupon=earlych...
It’s probably not for anyone reading this thread but it might be for your partner or kids. And it definitely works, because Lucy can use Excel now.
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.
That sounds like a potentially great approach for consultancy business/product discovery.
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.
I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call.
I wrote this to make that easy: https://GitHub.com
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.
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
I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.
I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
I like it more than index match.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
They somehow cooked up a spreadsheet that make them hit the limits much earlier.
Excel has grown hugely since then.
You can’t exceed these limits without being heavily alerted/warned by the application too.
Every spreadsheet application and database has these sort of limits anyway, eg:
* Google Sheets limits total cells, although can only handle a tiny fraction of what excel can.
* Postgres limits columns to 1600 (much less than excel)
* Mongo limits document size
Instead of writing the formula =A1\*B1, you can do =Width\*Height
Instead of writing the formula =A1*B1, you can do =Width*Height
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...
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
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.
It's so handy typing
=SUM(tbl_Sales_Transactions[Total])
Within the table, you can do this syntax to use items on the same row of data.
=[@[Price]*[@[Tax Rate]]But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
Asking for software is like a gestapo interrogation - your always not right and in the end all motives are questioned.
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system. What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
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.
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.
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
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).
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.
Spreadsheet Compare, an official diff tool, is excellent
Yup, so easy to audit.
sales-2005-(copy)-Final-2015-(copy)-real-final-(copy).xlsx
https://support.microsoft.com/en-us/office/overview-of-sprea...
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.)
I think later versions of VisiCalc did, too.
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.
The name manager was added in Excel 2007.
https://developer.microsoft.com/en-us/graph/
Going half in on microsoft is terrible.
And ‘all in’ implies usage of Teams..,
There are actually a couple of departments still using custom software and it doesn't talk to anything and you can't talk to it (but you can manually import/export data, in excel/csv format haha)
The other thing is that every time they want to add a feature they have to find 50k for software development and watch for 6 months as the request winds its way through the bureaucracy. We just whip it up in an afternoon and keep moving...
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.
I don't think Graph has triggers, but powerautomate does and admittedly I find them a little flaky across the board - I wouldn't be relying on them, we just poll for changes every 60 seconds or so if it's critical.
Teams for the most part is just a wrapper around sharepoint/outlook groups. The API there seems stable enough
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)
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.
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.
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.
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.
Getting the same level of copy-paste, drag-drop, drag-auto-complete, "tweak until it looks right before I move on" data-entry experience is non-trivial.
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)
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!
I will XLOOKUP() this job into the ground.
PS: = INDIRECT("YourTable[@header]") for structured references in online Excel
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.
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.
It isn’t. For pro use it’s by and large the same cost as O365, $12/user/month if you get a usable plan. (Both offer a $6/user/month plan, but on MS side that doesn’t have desktop apps, and on Google side it doesn’t have storage.)
// As of 1 August, all the grace periods for Workspaces expired. There remains a three month no charge period from your individual cutover day, and then a year of 1/2 price.
I couldn't agree more. It's a bit convoluted, but one thing you can do is write code in plain text files, in the Power Query M language, and load it using a combination of Folder.Files [0], BinaryFormat.Text [1], and Expression.Evaluate [2] (in the #shared environment).
On the downside, everything is no longer self-contained in the workbook. On the upside, everything is no longer self-contained in the workbook, and you can actually, like, check the code into git.
[0]: https://docs.microsoft.com/en-us/powerquery-m/folder-files
[1]: https://docs.microsoft.com/en-us/powerquery-m/binaryformat-t...
[2]: https://docs.microsoft.com/en-us/powerquery-m/expression-eva...
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.
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.
If you want to up your skills in this area, Wes McKinney's "Python for Data Analysis" is pretty great.
And Wes (the creator of the Pandas library) has made the book available for free on his website!
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!
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.
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.
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.
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.
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.
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
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
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.)
LET(X, A1, Y, X*2, Y+1)
=LET(foo, ComplicatedExpression(),
bar, AnotherComplicatedExpression(),
baz, AnExpressionInvolvingPreviousVariables(foo, bar),
ExpressionThatIsTheReturnValue())
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.)
=LAMBDA(... args, calculation)
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)
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).
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.
You can do this in Powerquery.
>VLOOKUP when the data type of the 2 columns is not exactly the same for example).
Use XLOOKUP.
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?
Use INDEX and MATCH.
CSV of all types can be imported with power query.
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:
And you could extend it by creating graphs not in “dashboard” etc, because it was just excel. In some ways it was better than today’s fancy dashboards, where you can do what the devs thought of but no more.
I suppose I should just get better at using numpy and pandas.
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
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.
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…
Lots of people thought AOL WAS the Internet.
And to this day many think EXCEL is everything.
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.
- 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.
I don't count computation as programming (Sum/count/avg).
But most people use count/max/avg prebuilts, with little to no logic. If it ain't Turing Complete, it's not programming :P
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.
There is at least 1 tool that can run SQL within Excel. I forget the name. You can probably find it via Google.
>Also, EDT doesn‘t look like it can do joins?
Easy Data Transform can absolutely do Joins (plus 58 other transformations).
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...
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.
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."
Google Sheets has custom functions in Javascript; excel has them in VBScript. Both are part of the 'product', but they’re not integrated into the spreadsheet user interface at all. Using them is more like writing an extension to the Excel/Google Docs app, than it is like editing a document.
To define a custom function you need to leave the spreadsheet UI and define it in a totally different programming language. It’s a major barrier to entry that most users don’t cross, and the context-switching is a hassle even if you’re comfortable with both.
Lambdas let you write a function that takes arguments in the same way you would write a formula that references other cells.
In my case it was real-time interactive data acquisition from a scientific instrument computer to the PC, into one spreadsheet as a database with live charting, followed by tabular calculation and a client requirements filter before final reportable data was selected and placed on a deliverable Word document.
All automatically to mimic the established 1000-step manual process that produces the same paperwork product.
Which was either emailed or sent to the clients from the old built-in faxmodems we all used to have. Once this was fully established, I had the "paperless" office.
The hard part was the object-oriented VBA in Excel to interface with the antique host's 1979 BASIC through the COM port, and write & read the files to disk (which the antique never had).
One cool thing since it was a COM port (when land lines were everywhere), instead of plugging the antique host into a PC in the lab for Windows data handling, you could alternatively plug the host into an external telephone modem which was set to answer mode.
Then from a remote PC, use that modem to dial in to your instrument and operate from there. Long-distance charges may apply.
Early laptops all had COM ports and good ones also had infrared for communication with business cellphones. You installed the infrared driver and it was a virtual COM port to Windows. You could really call in to the lab from anywhere, no more dependence on a land line. This was before USB or Bluetooth.
Over the years as serial mice had been replaced by PS/2 mice, and external modems replaced by convenient built-in internal ones (since there were so many people on dial-up), laptops no longer had physical COM ports, just telephone jacks. USB and Bluetooth were used as virtual COM ports for cellular sessions but then back in the lab with the same laptop you had to use its built-in modem and a plain telephone cord. You had to connect to the host's external modem by disconnecting it from the phone line, and plugging in your PC to the host modem instead of directly to the host COM port now. Then use modem commands intended for leased-line operation without a dial tone.
Without relying on the continued operation of the antique hosts, it could also open and write files in a common format used by instrument makers to this day.
(I don't recall the details about the bad arithmetic.)
One is not really an error, it is more like GIGO in the Average function:
https://exceloffthegrid.com/excels-average-function-the-hidd...
The other one is actually an error due to internal number storing (floating point):
https://docs.microsoft.com/en-us/office/troubleshoot/excel/f...
More generally there is 15 digit precision that can create issues, but the "real bug" was in Excel 2007
77.1 multiplied by 850 gave apparently 100,000 instead of 65,535
https://news.ycombinator.com/item?id=59392
https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
I always hoped things like Google Sheets would supplant Excel - but its not feature complete...
https://stackoverflow.com/questions/4221176/excel-to-csv-wit...
That would be cool.
I'm not sure what "proper tables" would look like, but Google Sheets has JavaScript integration, which is nice (if a bit slow).
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.
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.
No named ranges for one.
I'm not keen to run Windows just for this, so it's a Jupyter Notebook for me.