I'm pretty good with Excel, my main tool at the job for over 20 years. I understand how he did it, but it's just really humbling...
I still think quality of what you do with Excel (idea) is more important than how you do it (skill).
First of all they had an invoicing system in Excel, that pulled in data using VBS, into Excel templates, and at the press of a button in the UI generated invoices from these templates.
And the craziest part was their server inventory system made in Excel, where they had drawn all the rack cabinets, you could click on each, to drill down and show all the servers in that rack. Also a ton of VBS, you could even get monitoring status of each rack.
Excel has been OP for a long time, long before its Python capabilities.
1. You can drag down the bottom of the formula bar/field and make it multi-line
2. You can insert arbitrary[*] newlines in an Excel formula
Combining those, you can turn the absurd default format of single-line-of-code functions into something readable and manageable. Here's a simple one from a spreadsheet I have open:
=INDEX(
$C$17:$S$24,
MATCH(A6,$A$17:$A$24,0),
MATCH(C6,$C$15:$S$15,0)
)
[0] Early shows how it was done: https://news.ycombinator.com/item?id=46340638
[*] I think you can do it anywhere but I haven't tested anything crazy; mostly I just use them between expressions.
For folks on LibreOffice (currently v24.2):
* There's an downward-pointing "expand" triangle to the far-right of the formula input line.
* That button toggles the formula input area between 1-line vs 6-lines with scrolling.
* Newlines can inserted by shift-enter.
* If there are additional formula lines lines outside the viewable line(s), then a dashed line on the relevant border will be shown. (Plus the regular scrollbar, in expanded mode.)
=LET(
filterCriteria, "Fred",
filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
IF(ISBLANK(filteredRange),"-", filteredRange)
)
The end result - as you see - is quite readable.
Perfect style guide format does consume time, but pressing Alt+Enter a few times would seem to reduce errors at essentially no cost.
=LET(
h, ROWS(A2#),
names, A2#,
vals, K2:INDEX(K:K, h+1),
denoms, J2:INDEX(J:J, h+1),
k, 20,
groupAvg, SUMPRODUCT(vals, denoms) / SUM(denoms),
adj, (denoms/(denoms + k))*vals + (k/(denoms + inc, (names <> "") \* ISNUMBER(vals),
namesF, FILTER(names, inc),
valsF, FILTER(vals, inc),
denomsF, FILTER(denoms, inc),
adjF, FILTER(adj, inc),
r, ROWS(namesF),
nShow, MIN(10, r),
sorted, SORTBY(HSTACK(namesF, valsF, denomsF), adjF, -1),
TAKE(sorted, nShow)
What struck me is how similar it is to code golf or competitive programming, just with a different medium. The winner uses array formulas, INDEX/MATCH combinations, and nested functions in ways that most Excel power users would never think of.
The real insight though: Excel is probably the most widely-deployed functional programming environment in the world. Most "business users" are doing functional composition daily without realizing it.
Makes me wonder if we should be teaching programming concepts through Excel first, then moving to traditional languages. The immediate visual feedback is unmatched.
I enjoy the idea, and love watching it grow.
It’s gone quite far now - one of the many challenges was a mock terrain map where you’d calculate distances to hike while considering the weight of your pack. Even the way they walk through the tunnel is done for show.
Huh, interesting. I thought I’d been working remote so long that offices had done this to make people more excited for work.
When I was in the air force we had a complete aircraft maintenance planning and performance management system entirely in Excel. It can connect to remote workbooks on a shared drive/SharePoint too, so the higher headquarters would tie into our dashboard for their own operational readiness tracking.
It was a total shit show of undocumented pseudo APIs with zero change management or version control but it worked somehow.
Funnily Excel is the tool of adults born in 1980; The next generation will only know Canva, so I guess we’ll have great infographics about battle fronts.
The only thing I still enjoy is that any data smaller than 1M rows is sliced and diced almost without thinking. I am sometimes really grateful that MS did not break the shortcuts, while almost breaking the product overall. The muscle memory works perfectly.
You're supposed to do a $0 checkout for some reason and then download them
Doesn't really count in my opinion. I'd rather see finance/business majors stumble upon their version of LeetCode.
There is little difference between (if (> a b) c d) and =IF((A1 > B1), C1, D1)
Excel is the most widely installed functional programming language IDE.
programming languages aren't allowed to be in non-english somehow?
The possibility of incorrect parsing of equation with a variadic function that contains a decimal number in the equation.
However, this is a localization as even the functions change names.
https://www.reddit.com/r/excel/comments/1flsvyu/separator_co...
It’s just a locale setting as to which is applied. If you use English (US or UK) then your argument separator will be a comma. If you use other languages, then a semi colon will apply. You’ll find most guidance online referring to English language functions and comma separators, but ultimately it doesn’t really matter. If you ship a spreadsheet to me that you wrote in German functions and syntax that contains:
=SVERWEIS(X2;A:C;3;0)
I’ll open that and find
=VLOOKUP(X2,A:C,3,0)
> On my system, all parameter-separating commas have to be replaced with semicolons.
That implies the user has to manually change the syntax.
It does irk me a bit (though not as much as the translated VBA back in the day). But that's probably because I know English, I often look for solutions to my problems in English, not in my native language, and then would have to mentally translate that back. But that's perhaps a burden for programmers more than for typical users.
A few thoughts:
• it's been that way for decades, at this point. So changing it would annoy a lot of users
• the problem with comma and semicolon would remain unless you want entering numbers normally and within a formula to be different. I'm not sure that's good in a product built around numbers (and often numbers that should be written and formatted like any other number in that country).
• making it configurable might work, but that then requires more testing, although sometimes it's not clear how much testing Microsoft is still doing, so that might not be much of a point. But adding options also has UX limits and not just in the length of the settings screen.
Fun fact: in European and Brazilian Portuguese, the same function names can refer to different things. European SUBSTITUIR² is REPLACE (Brazilian MUDAR), Brazilian SUBSTITUIR³ is SUBSTITUTE (European SUBST).
¹ I've found this solution https://superuser.com/questions/1908516/how-to-change-the-la... but I haven't tested it since I don't have MS Excel at hand to check
² https://support.microsoft.com/pt-pt/office/fun%C3%A7%C3%A3o-...
³ https://support.microsoft.com/pt-br/office/substituir-fun%C3...
I've seen your work at Hard Work Party before, by the way! Really cool stuff, glad to see you've also got the startup going as well.
My GIS competition was fun too. They gave me a bunch of map data and I had to produce a report on Washington DC storm surge flood zones and potential rescue helicopter locations all within a couple hours.
I recall there being a video production category too. I didn't compete in it but you'd be given props and dialogue to turn into a video over the course of a day or two. Very few of the categories were contemporaneous competitions, most were long term project presentations.
Maybe let each editor request one reshoot in the first week, a committee aggregates similar requests, all editors get all the reshoots once they're finalized.
Maybe include storyboards and a rubrik for what story the film is supposed to share and how we're meant to feel, but maybe not.
Yes - but they've turned into something I'd really rather not watch: https://www.opus.pro/agent/human-creator-vs-ai
If you focus on fast typing/editing skills to level up, but still have bad decision-making skills, you'll just end up burying yourself (and possibly your team) faster and more decisively. (I have seen that, too.)
> how productive power users in different [fields] can be with their tools
There are a lot more tools in programming than your text editor. Linters, debuggers, AI assistants, version control, continuous integration, etc.
I personally know I'm terrible at using debuggers. Is this a shortcoming of mine? Probably. But I also feel debuggers could be a lot, lot better than they are right now.
I think for a lot of us reflecting at our workflow and seeing things we do that could be done more efficiently with better (usage of) tooling could pay off.
This is obviously 99% marketing. Microsoft/Waggener Edstrom tend to be really good at getting mainstream media to report on the marketing activities.
Example: For many Windows launches since Windows 3/95, there's been this media splash where Microsoft spends x million dollars on marketing and mainstream media then reports this, thereby getting (like) 100x millions worth of exposure.
Let alone the date issues.
At one point I did a deep dive on one or the other of these "quirks", and the earliest request for exactly the fix I want is from nineteen-eighty-fricking-five. Unbelievable.
What does that mean? Microsoft stopped developing new features? You think it was feature-complete?
I'll admit, on occasion having more than 65k rows is helpful but generally that's the domain of a database, not excel and it wasn't a good tradeoff IMO
Obligatory Krazam sketch: https://youtu.be/xubbVvKbUfY?si=h6QR2gzac48R6kca
Good to see that AI slop would inundate and suffocate these media houses.
The thing is, you have RTF covering a 99% of actual office cases. For the rest, a DTP would be far better, or Texmacs which is far superior for academics. The old WordPad with tables (and Ted for Unix once it's properly setup) would enough to do most boring documentation.
On Excel, just look what happened with Genomics. Also, overabusing Access for management (or worse, to handle Covid cases in a shitty XLS table) it's a nightmare.
For tons of cases TCL/Tk + a Sqlite3 backend would perfectly work and it would be accesible to any platform, from GNU/Linux to MacOS, BSD, Windows. You can stick an HTML5 frontend with ease without even needing JS to access the data (plain HTML forms would work really well).
Ah, yes, graphs, charts. Gnuplot would help you in that case, or a fast Tk package. Reports? TkHTML with some easy CSS. It would cost far more initially? The potential risks on compatibility would be nil in a future. And, as a plus, yo don't have to worry about Macro viruses and whatever.
Also, input and output shouldn't be allowed to be mixed by design. Data in one sheet, and formulae output in another. And no automatic parsing should be done, ever. Remember Excel with Genomics, or the issues mangling dates. Or worse, locales, which are another issue under Unix.
Edit: Of course, they changed the title! [1]
> I don't know how else you'd do it without the Microsoft stack
Just fine. Maybe better.