Unfortunately, most data analysis is just as bad. I have worked with SAS & R code, thousands of lines long, all without a single test. Basically a big series of hacks. Mostly, statisticians I have worked with have no concept of testing, or even why it's necessary.

I shudder to think of the gross error in research results from all I have seen!

Engineers often get away with creating monstrous spreadsheets that are difficult to follow:

- scattered logic - methodology obsficated with complex excel formulas - magic numbers that you have a hell of a time tracking down the source of - links all over the place. Are they still valid or have they gone bad due to all the copy/pasting? etc etc

As a programmer who has to write something that has logic and no ambiguity for a computer to understand it, it makes you want to beat your head against a wall trying to understand and check someones work.

Until recently (victim of the downturn) I was in a small dev team looking to automate and improve things. We had some good wins but it's a struggle.

If someone like toyota starts making aircraft seriously and develops more standardized methods rather than simply employing piles of engineers to punch numbers wildly into excel they may be able to crush everyone else in time to market.

Would it help to have a sheet of centralized assumptions (constants, magic numbers, etc.) that people could link to from any spreadsheet?

Would it help to be able to reuse formulas from another sheet without copy/pasting them? i.e. link in such a way that you can fill in your own numbers, but keep the original logic?

Would it help to be able to "lock down" certain models that people could use to do their analysis, but not make arbitrary changes to?

Would it help to be able to build some computations in a spreadsheet and wrap them up as a function that other spreadsheets could call (passing in their own arguments)?

I'm interested in what can be done to raise the level of abstraction (and consistency) without giving up the spreadsheet UI which is so productive for many users.

There are software tools for analysis developed to standardise what can be standardised and to force people to use templates for their analysis, but they are generally not great and getting them developed is always politically and financially challenging.

IMHO engineers find it difficult to identify and extract the common processes because every part is slightly different and complex and usually requires its own judgement calls to be made about how to deal with it. This leads naturally to everyone doing things their own way and therefore using the most flexible and cheap tool there is...Excel.

That's not to say there aren't common processes and higher abstractions to be made, it just requires people to look hard for them and think about things in a way that does not come naturally to the industry. They also need to have the clout to get people using them.

I think things are changing some what with modern aircraft though. My opinion is that judgement is playing less of a role in many cases and we are moving to brute force analysis i.e. Just throw it at the computer and analyse it all in great detail.

This leads to more common processes and standard ways of sharing and moving data around.

It's a big problem and I enjoy working on it but the support isn't always there to invest in it.

The cost of designing an aircraft is tiny compared to the overall lifecycle of the aircraft and the costs involved in manufacturing. So the industry would generally prefer to keep operating in the same old ways rather than take risks on new things. Especially new things that involve upfront investment. Just my opinion anyway.

This touches on one of my pet theories. Spreadsheets were remarkably successful because they never require you to express a model abstractly (i.e. they never require you to write the formulas up front and plug in data later - which is the way that programming languages work). You're always dealing with concrete numbers. It's example-driven. I believe that this is one of the reasons why the spreadsheet is the computing tool of choice for non-programmers. Good programmers "get" abstraction; most non-programmers don't.

The trouble is that when you need to make many variations on the same theme ("every part is slightly different"), the only facility provided by the spreadsheet is copy/paste. Now you can modify Examples 1 thru N to your heart's content, but you've lost any link between the stuff that's common to them all. There's lots of research showing that this kind of spreadsheet development leads to billions of dollars in errors every year. Basically, imagine programming where you have no subroutines and nothing but copy/paste. We know what a mess that would be, the minute you tried to do anything complex.

I believe an improvement would be to allow a spreadsheet to clone ranges from another spreadsheet, thus inheriting their data and formulas, but preserving the linkage back to the original. You can override things in the clone to make the parts different that need to be different. But anything that you haven't overridden (or added yourself) is a live copy of the thing you cloned. That means if you change the master, the copies get updated. In fact, you haven't actually copied anything - you're just referring back. The system would be better able to trace the true dependencies of a sheet, and users would still be able to base new work on old examples.

Since you've struggled with these issues, I'd like to hear your thoughts.

My pet theory is that people have a love of grids and graph paper -- I remember in grade school how graph paper was, for some reason, a rare thing, and it was treasured. The kid with a whole stack of graph paper and who DREW PICTURES OF TANKS AND DRAGONS ON IT was considered some kind of blasphemous rouge, but still respected because of access to graph paper. It brought some indication of order to the otherwise sloppy handwriting on looseleaf we had to do.

And that love of the grid translated into a love of spreadsheets. It's so neat and clean, ready to be filled up with MY organized madness! All those empty boxes, what fun! Look, I made a list of items! And it's arranged in neat rows and columns! And besides, who needs to be able to join these product ids with sales numbers? We can do that with a hundred clicks or so and create a pivot table!

Everything begins to look like a nail when your hammer of choice is Excel.

Interesting contrast, though, is the relative avoidance of accounting ledgers, which spreadsheets are a closer cousin to than they are to graph paper.

In making things better in engineering the first goal is first try and stop people spending so much time managing and moving data around. This really takes up a large percentage of the work done, unnecessarily.

Second is to start getting engineers to see things more like programming where things aren't repeated and data is separated out from the method. If you receive new data (e.g. new aircraft loads and geometry) your goal is for there to be nothing whatsoever required for you to update your analysis. Its already setup, you just point at the new data and it's done. This is rarely the case I find.

Finally, I think is the issue you are talking about which is to tackle the problem of engineering by forming new abstractions and figuring out how to best represent those abstractions with existing tools or something new. This is definitely promising but honestly there's lots of easier stuff to do first that would make a big difference.

The thing that makes it so hard in engineering is that every job has its own set of "approved" methods and the amount of work required for each part varies for a number of reasons. There's a lot stuff you're never going to get the world to agree on but a spreadsheet type thing that separates the data and formulas completely is a good start.

The formatting of your comment reminded me of one of the many reasons I actively avoid Excel and other Microsoft products - those "helpful" agents. The ones that always pop up when I least expect and offer suggestions that are never useful.

Whatever path you decide to take on your quest for abstraction, please don't pursue that particular route. That way lies madness.

I think you're on to something here, actually. The great advances in the first generation of desktop apps were done by the early 1990s. Instead of making the tools more powerful by evolving the core concepts, MS (and other vendors) piled on feature after feature and gimmick after gimmick (like those "agents"). Now they have bloated monsters that can't be evolved any which way. It reminds me of programs that are heaps of special cases piled one on top of the other with no thought for how to effectively generalize (nor would it be surprising if that's just what these particular applications are). MS are now so hamstrung by this legacy that perhaps a window of opportunity for real innovation in the space has finally opened.

The general one size fits all solution that is useful but does not lead to the mess Excel creates seems to be what you have in mind.

In aerospace the second most popular tool is Mathcad it seems. Have you seen that?

Although there's rarely enough licenses around (why spend when everyone has Office installed) and a lot of people aren't familiar with it.

The core principle of what we're doing is to build everything around the familiar spreadsheet UI. That UI is hugely valuable for two reasons: it fits the way that users' minds work, and after nearly 30 years it's what everybody knows and likes. The problem isn't with the spreadsheet UI as such, it's with the current generation of spreadsheets that were designed for the problems of 10-20 years ago. The problems have gotten way more complex, but the tool hasn't evolved.

If you think about all the different issues with Excel that lead to messes, in each case you can kind of imagine a spreadsheet that doesn't have that particular problem. Consider the problem of copy-paste creating massive duplication: you can imagine a spreadsheet that gave you the ability to copy something without duplicating it (the cloneage idea). Here's another example: in Excel, it's really hard to do analysis over top of a bunch of different spreadsheets. Even just adding up the totals of the same variables in different sheets is a pain. But one can imagine a spreadsheet that didn't have that problem. Other examples: it's hard to track the history of who changed what in an Excel spreadsheet; it's hard to share Excel spreadsheets or link from one file to another; it's hard to restrict access to certain ranges while allowing it to others; it's hard to modularize complexity. All of these issues together mean that people get into big trouble when they try to do serious work with Excel. Yet it's possible to imagine a modern spreadsheet application that doesn't have these weaknesses.

The question is, if you build this new thing, will it still be a spreadsheet that users recognize and feel comfortable with? Or will it be some foreign thing that nobody wants to learn? That's what killed many attempts to improve on spreadsheets in the past, so we're focused on making something that looks and feels a lot like Excel (but which doesn't create so many messes... or at least makes it easier to create non-messes).

Good lord, I did not intend to write this much. Thanks for listening!

p.s. I'm not familiar with Mathcad. It may well be a good vertical app. Those exist too!

pp.s. Email me (address is in my profile) if you'd like to take a look at what we're working on.

It's surprising that statisticians would fail to understand the importance of testing, given that a lot of the root issues in statistics (e.g. the Bayesian/frequentist dispute over "what is a probability?") are epistemological.

Of course, we had to run all this on VMS and print it out on a fan-fold line printer, and it wasn't very interactive. SAS products seem to be more visual and interactive now.

I don't understand why they don't get testing either. Not one I have met realises just how error ridden their scripts probably are. Still, a lot of programmers don't understand the value of testing yet.

You can also start the cell entry with a single quote to make a single cell store text.

But there's silly people everywhere, even in science.

That people who don't know the domain and tools well enough, and don't have the pernickety edge-case worrying mindset will introduce errors like this. Writing a report that doesn't take into account a new database table, editing permissions and leaving a big hole in them, etc.

What I'm not sure about though, is whether it really matters. Whether the tradeoff with only allowing certain people to make changes saves enough trouble that it's worth the limitations. Businesses with people are very good at surviving errors, after all...

Everyday practice gets me a bit more and more annoyed at all the automatisms that software office packages come with. And they are nearly all on by default! It takes a skilled person about 10 minutes to disable nearly all automatisms in MSWord or in OpenOffice Write, in several menus. In MSExcel, I don't even know where to look myself (having switched to Gnumerics long ago).

We would all appreciate one single button that says: "Don't do anything unless I told you to."

For a moment there you made me have a bad flashback to Microsoft Frontpage circa 1997.

The very not-smart people would try to use Excel for this, and get hilariously (and obviously) incorrect results due to rounding errors.

One possible solution in Python:

  import operator
  fact = str(reduce(operator.mul, xrange(2,36)))
  print len(fact) - len(fact.rstrip('0'))

  >>> 8

k being a sum from 1 to m of a sequence whose term is [n / p^m] ([] meaning taking inteer part) where m is such that p^m =< n that is m being the integer part of logarithm base p of n.

In this example

  [log5(35)] = 2

  k = [35/5^1] + [35/5^2]

  10333147966386100000000000000000000000000 (Open Office Calc)
  10333147966386100000000000000000000000000 (Excel)
  10333147966386144000000000000000000000000 (Gnumeric)
  10333147966386145000000000000000000000000 (Windows Calculator)
  10333147966386144929666651337523200000000 (Python)

Your average wannabe-banker/Wharton undergrad has never heard of Python, however, but has used Excel. Quants are familiar with Python, but they get harder problems.

Most quants don't, however, get to use Python. For some inexplicable reason, a lot of them are mired in C++, of all languages, and tend to be poor-to-mediocre programmers. There are exceptions, though; the one I worked at used an FP language and had excellent programmers.

Actually, they did show the result by default as floats in scientific notation, it was me who changed number display options for easier visual comparison.

I assumed quants preferred C++ because of performance. When I occasionally have to go back from Python to C/C++, I'm surprised much faster it is.