Hacker News Reader

Comment by lph | Hacker News Reader

20 minutes in: the dawning horror that, yes, I'm watching a video about Excel for fun

using excel isn't not fun, it's just that the mental complexity of excel is offloaded to the user. the user keeps track of the various computations and transformations. anything more than a quick check on some data exponentially taxes the brain leading to an ever increasing dislike of the software

being handed someone else's excel sheet is an infuriating experience because they're also handing you whatever convoluted mental model they used

I would say that they're _not_ handing you their mental model. Instead using their spreadsheet forces you to come up with a mental model which is congruent to theirs.

On ships they sound the tanks daily (water, oil, fuel) and use the depths to calculate the volumes. There are dozens of tanks. Part of my job was then to use that data to figure out the change in the ship's draft from day to day. Which could be done two ways: drawing lines on Xerox copies of an odd set of nomograms, or brute calculation. I found the nomograms fraught with opportunities for error. Luckily the oil king responsible for the soundings kept them in Excel. So I wrote a sheet that took his data and did all the brute force calculations. So when he printed the daily soundings, instead of handing them to me, the draft report just printed out as the next page. It was more accurate and saved me 1-2 hours a day. And looked nice and crisp coming off the laser printer. Everyone thought I was a magician.

The guy who took over for me had a nervous breakdown.

In investment banking, one of the first things you are taught is excel formatting and how to write readable excel. How to write clear and concise comments, what color different cell types should be (black for formulas, blue for inputs, green for links to other cells, red for weird formulas), how to structure large files (when to make a new tab vs keeping a monolithic file), when to break up a formula into multiple cells so it is easier to understand, even that you need to hit "ctrl home" before you save so that the next person to open the file starts at the right place (the group that trained me called it "turning off the lights before you leave a room")

So if someone is good at exce you can easily understand what they're doing and why they did it

They have standardized their mental models

Which cannot be said for software engineers.

This is called "Architecture" or "Code Patterns".

It can absolutely be said for Software Engineers. It just can't be said of every Software Engineering Org.

I'm intrigued by this, are these standard codified and documented anywhere? Would love to know more about Excel best practices.

I'm not sure if there is public documentation on this, but investment banks definitely have training materials that document it.

A lot of it is just high level stylistic suggestions, sort of like the Python style guide, though some are more specific

The strangest one I remember is: when you are summing a column, put one row in between the cell with the sum and the bottom-most number to be summed. Change the row height to something like 10% of normal cell height and in the cell in the new row, right above the cell with the sum, add a set of dashes (-------).

I don't remember why we were told to do this but definitely remember doing it, and in my next job people laughing at that habit. I think that it ensures that, when you add a new row to the column to be summed, that your sum formula automatically picks up the new row

Would be nice if someone had documented these conventions somewhere.

When I was in the agency world, I was frequently inheriting spreadsheets from other teams that needed to be updated with new data, or often overhauled completely.

You definitely have a lot of moments similar to reviewing others' code where you go "Hmmm, this doesn't seem to make sense. Am I missing something? Or did they just do this flat out wrong?" Often the answer was "yes."

There's also a radical spectrum of Excel skills (much like coding). I took a 50MB Excel report that took hours to update manually and brought it down to <1MB that refreshed when you clicked a button via web queries. There were significant savings for the agency as a result as that had to be updated weekly, simply from cleaning up a spreadsheet.

Whenever possible now, if I suspect a spreadsheet tool or report I've built is too complex to grok just by quick perusal of some formulas and clear naming conventions, I document the hell out of it in a separate sheet or our knowledgebase. The pain of deciphering those things is real, and I would never wish it on anyone.

Reply on news.ycombinator.com