Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I was wrong about spreadsheets (2017)

reifyworks.com

Post reply on HN
Print view View original

211–220 of 378 posts

Re: I was wrong about spreadsheets (2017)

#211

Post by hef19898 »

TeMPOraL wrote: post #199
hef19898 wrote: post #123

Earlier quoted context omitted.

So true, I used to run European cross-docking ops, planning, inventory, forecasting, scheduling, in Excel back the day. Successfully scaled by 100% YoY for three years. Was quite a lot of fun. Issue I have with that approach is not using excel per se. It is using Eycel in addition to what ever system is being used the first place (SAP for example is a pretty popular thing to circumvent with Excel sheets). That and ov…

> It is using Eycel in addition to what ever system is being used the first place (SAP for example is a pretty popular thing to circumvent with Excel sheets). (...) And circumventing existing systems with local offline spreadsheets just screws up everything. I usually side with the circumventers on this one. This misbehavior happens for a reason, which usually is that it's impossible or infeasible to do the work with…

True that. I used to both back then, first a user and then a SAP system guy. For me it is always a death spiral: people don't trust the system or it is too cumbersome, they use Excel, the system gets even less reliable, the use Excel even more,... and so on. I always saw it as my role a system guy to give users a system they can use and then force them to actually use it. Because, when providing the systemnI talked to them first so in the end it was ind of their system too.

And then there are examples like the production planning done in Excel because SAP PP isn't just good enough. Then the guy who created said Excel tool left. Years later when production had to switch to weekend shifts zhey couldn't because no one could adopt the Excel tool. Once you reach that point you are screwed.

Re: I was wrong about spreadsheets (2017)

#212

Post by tajd »

ozim wrote: post #207
halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

Yeah it works and you don't need support contracts and then: - Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of $1.3 billion Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Excel does not have "access control", "auditing…

Or tests suites for checking formulae or how the said formulae are applied across the spreadsheet?

Re: I was wrong about spreadsheets (2017)

#213

Post by LeonB »

TuringTest wrote: post #177
LeonB wrote: post #106

Earlier quoted context omitted.

The link is to "You Suck at Excel" by Joel Spolsky, which is an excellent tutorial. I sincerely wish it had a different name, though! I would love to send this video to some of my business colleagues inside a large enterprise. They need this information and they would enjou everything about this video. However, it would not be acceptable to send them a video entitled "You Suck at Excel." If it had a more enterprise-f…

You can embed it in a more professional page, and hope that no one will notice the title at the overlay ;-)

Genius idea. I have a bunch of domains, combine it with the right subdomain... and we're onto something. Adding this to my list of things to do. Cheers!

Update: okay check out https://excel.secretgeek.net/

I've re-badged it as "Secrets of Mastering Excel" and used absolute positioning to put a label to that effect over the video's title.

Ideally I'd detect when the video starts and remove the label. Hmm. Not sure of the right approach.

I recommend (at the foot of the page) that the viewer watch it in full screen (which will remove my dodgy title)

Re: I was wrong about spreadsheets (2017)

#214

Post by kyllo »

d__k wrote: post #129
kyllo wrote: post #61

Earlier quoted context omitted.

The other thing people don't realize about Excel is that it has a compressed, in-memory, columnstore analytics database built into it. It was called Power Pivot then Power Query, not sure if it changed names again, but it's actually a SQL Server Analysis Services Tabular Model, the same thing that's in Power BI as well. It's going to perform much better than a row-oriented RDBMS at aggregating columns because that's…

It was a separate data processing engine within Excel developed by another team (SQL server) for the purposes of self service BI (Power Pivot/Query). Most probably, this engine is not used for normal (traditional) calculations.

You mean if you create the pivot table from data in a sheet? It's not needed for that because you'll run out of memory trying to fit any more than about a couple million rows in a sheet first. Meanwhile you can easily load 10s and possibly 100s of millions of rows into Power Query.

Re: I was wrong about spreadsheets (2017)

#215

Post by jhbadger »

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

My main issue with them is that they often aren't reproducible as the data transformations aren't logged. As a computational biologist I often deal with spreadsheets that experimentalists give me. I have no idea how the data were transformed from the raw data, and often the experimentalists themselves can't remember either. That's why I infinitely prefer real programming languages for use in data manipulation -- you can look at the code.

Re: I was wrong about spreadsheets (2017)

#216

Post by 0815test »

halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

> It's amazing

It is amazing, just not in a good way. Maybe I should try to find and post a link to that paper where it was pointed out that Excel was munging things like gene names because they get interpreted as dates in the 'untyped' cell input, and these things were showing up in published research.

Excel is a disaster, it will seem to work until it turns out it's doing something weird behind your back, or maybe you've made a careless mistake somewhere and it has zero tools to help you catch it. And you won't know until it's far too late. Please say no, use anything else - Python, JavaScript or heck even QBASIC or whatever - but just don't use Excel.

Re: I was wrong about spreadsheets (2017)

#217

Post by deburo »

pbowyer wrote: post #140
viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

> at least you can make named tables in excel on windows I built a spreadsheet that loaded external data sources (filtered log files from a production system) into Excel tables. I then combined two log files into another table, and created a pivot table from this, which I then filtered (date ranges etc) and analysed. That worked great at the start - until I came to update the external data and found the pivot table d…

I suggest you use Power Query to load external data. If you do, pivot tables and tables connected to those queries should all update automatically on refresh.

Power Query is really the best feature in Excel to me, eventually users will stop copy pasting from Access to Excel and I'll be happy.

Re: I was wrong about spreadsheets (2017)

#218

Post by eej71 »

ozim wrote: post #207
halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

Yeah it works and you don't need support contracts and then: - Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of $1.3 billion Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Excel does not have "access control", "auditing…

I think it will be easier to solve those problems in the Excel development space than it will be to export the Excel solutions to another solution domain.

Also the kind of mistakes you've described can (and do) happen in other programming domains that are deemed more respectable.

Re: I was wrong about spreadsheets (2017)

#220

Post by ozim »

Mvandenbergh wrote: post #179

It's interesting how often people's response to the manifest problems caused by people using Excel in unfortunate ways is to either suggest tools with much steeper learning curves or to suggest additional Excel features. 99% of these problems can be solved by proper training and standards appropriate to the problem and context at hand. For instance: Always separate and label inputs, calculations, and outputs. Documen…

Hardest part of everything I do in my life is making other people follow rules, or any kind of guide lines. If you succeed with making only 1 rule of those you described consistently followed by organization of 10 people for 6 months ... please send me how you did that, then I am going to be a millionaire.

I am team lead of small dev team, if you push people by force to follow rules they will start maliciously follow them up to the point where no work is done and your spreadsheets are perfect.

Post reply on HN