What the article's really trying to say is "Being good at Excel implies you have a logical mind and the ability to see a way through problems. This is something that others lack".
What the article's really trying to say is "Being good at Excel implies you have a logical mind and the ability to see a way through problems. This is something that others lack".
With any other tool, there would eventually be an onboarding process not just for the thing I built, but also for the tool I built it in. Not with Excel – that will just run, whereever you go.
How to change it, yes. How to get the correct result, probably not.
But well, they will get a result. Many people are satisfied with that.
I'm not sure a receptive t will be able to successfully edit a spread sheet. So easy, to get interdependency between cells to a degree that it is really hard to follow, if you didn't create it yourself.
Or did I just describe Perl? Not sure.
No. That is not Perl. That is C Recursion: https://www.bobhobbs.com/files/kr_lovecraft.html
Python3 is up and coming, and eventually it will get there, but old machines must die before it can reach ubiquity.
Python breaks backwards compatibility with old scripts every six months or so. In practice, this means you have to port scripts every time you switch machines. Virtual environments sometimes address this issue, but they are hit or miss.
In contrast, perl scripts from 1999 often work unchanged on clean 2022 OS installs.
awk, bc, and sh are POSIX base utilities, they are literally preinstalled on every unix derivative.
That doesn't make them linga franca, which is what Scarbutt was asking about.
Hell, ed is a POSIX base utility. Does that make ed's command mode the linga franca of text manipulation? Of course not, the thought is preposterous.
Yes, I would consider awk and sh lingua francae (?) alongside Perl – but in slightly different contexts, namely low-complexity jobs.
I find Perl slightly more suitable once things get a little complicated, mainly because it's footgun:power ratio is better than awk (few footguns, but also very low power) and sh (many footguns, not that powerful).
I have come across plenty of boxen where bc was not installed, so I don't put that in that bucket.
And yes, if I want to describe text changes in a highly portable way, I will use sed (or ed, depending on context) rather than, say, unified diffs. I could accept an argument that unified diffs are also a lingua franca for more complicated changes, but they are harder to write.
I took it to mean "so common that it's available almost anywhere" (which is consistent with the context of the comment).
I've found it extremely useful to rely in tools such as the ones you just listed. Specially because they are often present even in light vms were perl might not.
That it is an advantage does not naturally and unfailingly translate into being a linga Franca.
"vi" being so common (ie. "lingua franca") is what gave it an advantage.
Yeah, it takes a bit of time to become really comfortable, but our accountant was (after the initial take-it-away! phase) really glad i have her introduced to it
Avg←{(+⌿⍵)÷≢⍵}
Also, if you want a non-programmer to quickly be able to learn a language, the more learning resources they have available to them, the more probable it is that they'll be able to pick it up. APL probably don't have as many modern, free and online resources as other languages.> APL probably don't have as many modern, free and online resources as other languages.
Not as many, but that can be a good thing too. Lots of languages have tutorials, libraries and articles that use horrible practices. I write React + TypeScript for work, and it's an especially big problem.
I guess it would, that's not what I said though. Most programming languages use symbols already visible and available on your keyboard without having to do anything extra. APL uses a bunch of symbols you either need to learn a new keyboard combinations to enter, or otherwise extra software you probably haven't used before unless you're a mathematician.
> I'm learning one right now and that's a task much harder than learning some APL glyphs
I agree. Learning new complex glyphs is probably easier than new complex concepts, but what's for sure is harder is learning both at the same time rather than just one.
Using a language like Python or any other mainstream language would at least make the person not have to worry so much about new symbols, but mostly just new concepts.
The "Examples" section on the Wikipedia page for APL also contains a bunch of symbols I would have no idea how to enter on a computer: https://en.wikipedia.org/wiki/APL_(programming_language)#Exa...
It's easy for an English speaking person to consider programming languages in English as the only kind that's usable. To be fair, English dominates in the tech world. But it would be narrow minded to dismiss a programming language solely based on its non-English symbols.
Mathematicians have long been using symbols for efficiency in its expressiveness. Why not programming languages? I'd like to keep an open mind.
Contrast that to APL which has you enter symbols that are nowhere to be found on a keyboard. You need to manually look up how to enter the symbols, or run specific software to have it on-screen, and if you forget, you can't just look at the keyboard, you need to look it up again.
I'm sure it becomes second-nature after a while, just like entering pipe characters or curly braces. But it's definitely harder to get started if there is more to remember than just concepts.
I have not forgotten how long I had to learn English, as it's not my mother language and I had to learn it in school at a certain age. I'm also currently learning a fourth (speaking/writing/"human") language currently, so I'm well aware about that there are other languages out there.
I'm also not arguing against APL as a language as a whole, I'm personally curious about it as well and will probably give it a go as it's currently missing from my repertoire of languages, which is wide already but still missing things. I'm simply arguing against it as a first programming language for people to learn, compared to languages that don't contain symbols you cannot "normally" enter on a traditional keyboard.
Speaking as an old, enthusiastic APL programmer.
Why did you choose APL over something like GnuCOBOL. Ive been pleasantly surprised with how teachable Cobol is to a range of people.
I did chose to show her APL simply because i am somewhat firm in it myself, so it was more simple to teach it someone else.
No R, no Python, VB.NET it was.
Long/complex Excel formulae should usually be broken into smaller chunks, with intermediate results stored in separate cells. For example, if you're calculating two numbers, and then calculating their ratio, it would be better to use three formulae instead of one. That way:
1. Each formula is shorter.
2. Each formula has a single purpose which can be understood.
3. The outputs of the intermediate steps in the calculation are obvious.
This applies also to complex nested IF() statements. Instead of calculating all the conditions inside the IF(), calculate them outside, and then reference the TRUE/FALSE cell values in your IF statement. When you need to debug why you're not getting the result you want, you can easily look at the intermediate calculations all at once, without needing to step through the calculation and check it in the order of calculation.
Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'.
...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff
There were days I spent several hours clicking "Evaluate Formula" button close to 100 times in a row to debug things like this. The syntax colorization definitely helps, but I would have killed for just the ability to pretty print a formula or set a breakpoint. I'm always shocked it doesn't have features like this that programmers depend on, but I wonder if part of that is because the people that would write something this complex in Excel have never experienced all the tools you get with "real" programming languages.
At home I was getting deeper into Linux and bash/python, and that made me resent Excel even more. I finally moved into the IT department and got really into PowerShell and training myself as a sysadmin, but I would still get requests about that damn spreadsheet for at least a year or two until they finally stopped asking. I used to enjoy problem solving challenges with Excel for a while, but in the end it became a parasitic relationship because of the time needed to fix issues and the complete lack of ownership and knowledge within the department that supposedly owned the form and process.
Critical decisions are made based on Excel formulas like these, (people getting fired, financial decisions, etc.), maintained by people that barely understand programming. Yes, it's nice that it's helped democratize automation and coding for non-technical people, but Excel just wasn't designed for things this complex. When your Excel formulas and macros start to look like this, you need better tools to manage the complexity and trust the maintenance to tech people instead of business people that don't fully grasp the monsters they're creating when they just keep using the only tool they know.
Not sure if the current web/JS based excel still supports those things.
Annoying it's not offered in most versions.
I guess you could have a third sub-cell for style?
When I need to do a complex calculation like that in Excel, I break it up into multiple cells each doing a subset of the calculation, then I have a result cell that brings the results together at the end. that way I can work on manageable formulas and can check the results of each. It’s probably less efficient from a processing and memory standpoint but I rarely work on large data sets and the gains in reliability are well worth it.
I've got some output from something or a table of data I can copy-paste into Excel, quickly do some text-to-columns or string manipulation (tweaking it instantly if I see things don't look right) to get the data into a usable format, then go and build up some formulas, sorts, and plots to answer whatever question I had / get something I can share with others.
Each of those steps is doable with Python + SQLite, but takes more time: I have to write my own data parser (yes, Python's string utils are very good for this, but generally Excel is faster to use, despite using Python for years for my job. I have to then create a schema for the data, and then I have to iterate on my analysis functions, and then finally write my own output / futz around with a graphing library, rerunning whenever I want to tweak something (instead of just changing a cell or chart parameter and seeing it instantly reflected on screen.
There is a crossover point where the complexity of the analysis, amount of data, or otherwise makes Excel less practical, but it absolutely has a place as a super-helpful tool, even for those capable of using more powerful general purpose programming languages.
You might find my sqlite-utils Python library interesting - one of its key features is that it can create the right schema for you automatically to fit the data (as a Python dict or list of dicts) that you pass to it: https://sqlite-utils.datasette.io/en/stable/python-api.html#...
Excel allows non-programmers, of whom there are very many, to visually explore and play with their data. I was already a programmer when I learnt Python; it took many years to learn those skills.
Non-programmers usually do not even want to think about their data in an abstract and general way anyway.
You could likewise say "a huge amount of file operations should be performed with rsync" and just ignore the fact that it's much harder to learn than a drag-and-drop setup.
Of course, this really comes down to using the right tool for the job. Python should be used for automating batch processes, and using Excel manually should be used for data exploration. Jumping into automation before you know exactly what you want is premature, and doing something manually over and over again is wasting time. Us programmers sometimes reach for code writing and automation before we’re actually ready, and waste time writing the wrong code. (I am guilty of this.)
But if those things are not true, if I'm going to be the one to test out an idea and hand something off to my coworker or customer, building in Excel means they're likely to figure it out instead of their eyes glazing over when I explain that they need to install Jupyter, or a text editor, or Visual Studio, or to modify the Lua config file, or invoke something from the command line...they're not going to do any of those things, they're calling me when ~~it breaks~~ their inputs and requirements change.
It's similar in my primary role building industrial manufacturing control systems. Rockwell Automation is a terrible company with asinine licensing fees and outdated technology...but they're the safe choice if you want to transfer system ownership and not get calls about that one machine with the "Linux NUC" for the next 20 years.
Is there a nice library which makes it easy to use SQLite in Python even close to as easy as Excel? Or even as easy as Python lists? Anything which requires me to fill my Python code with SQL strings is (in my opinion) disqualified.
It won't even replace the edge-case uses, n/mind the majority of uses.
I have this theory that a relational database management system is basicly a grown up spreadsheet. and it is, it does great, far better that excel at storing and querying data. And you have better choices for logic, from views to full blown stored procedures(most will probably use an external program instead). all of which keep your logic out of your data.
This should be 5 cells calculating an output uniquely for each possible value of D2. Then picking the appropriate one later on.