6 ms·

The author said it himself, these errors were caused by humans, not excel. I don't think making excel "smart" enough to prevent people from making dumb mistakes

The author said it himself, these errors were caused by humans, not excel. I don't think making excel "smart" enough to prevent people from making dumb mistakes is a good idea. In fact I think that would transform excel into microsoft word, which is the only thing that could make life worse for your typical competent office worker. Excel already has a lot of this crap. It already requires an act of god to prevent excel from changing your string of digits into a number or date. It already shows you little exclamation point icons when your formulas omit adjacent rows or are different from other formulas in the same row/column. A tool will never make it possible for dumb people to solve hard problems easily. It's like trying to design a knife that makes it impossible to cut yourself. Nobody with any kind of a clue would want that knife. A tool should be straightforward and intuitive, but it shouldn't aim to be smarter than it's user.

thiele 13y ago
There is low hanging fruit in the problems to solve though. For example, finding and highlighting outliers in a column. It's a relatively simple feature but yields big benefits to users.
- pyoung 13y ago
  Except it isn't that simple. Your definition of an outlier may be completely different from some one else's (95th percentile, median/regression method, or simple max/min, some of these are already possible in excel). Then, how is the information displayed? Highlighting cells? Well if you have +10k rows of data, than that won't be too useful. Creating a summary in another column/sheet will add to the already cluttered 'results' spreadsheet, etc... This is why I recommend using programming based tools for data analysis. Any non-programming tool has to find a balance between the number of features offered and the complexity/ease-of-use of the tool. With programming tools, you merely have to find the right package (or build your own) which essentially results in getting the exact set of features that you need to solve your problem.
  - hamofgobelgope 13y ago
    I know people bag on VBA, but I'm a big advocate of effectively utilizing Excels tools along side using VBA to customize where necessary. This solves the problem in your first comment (users can define their own statistical limits and methods), while keeping the simplicity of excel and its tools for other users. The problem is that not everyone is a programmer. A "programming" tool might be great for you, but when you show it to co-workers for them to work with, they'll inevitably ask "Can I get this in Excel?". Excel+VBA allows for customization when the "complexity/ease-of-use" balance is out of sync with your needs, but to everyone else it's still just Excel. With this, those that can program have the option to solve the problem their way, while allowing those that don't program the means to solve the problem the way their used to.
    - pyoung 13y ago
      I have used VBA here and there, and try not to bag on it too much. There are some cases where using it can make sense, but in general, it seems to be a compromise. I get a little confused about the programmer/non-programmer dichotomy. If you are capable of implementing a complex model in excel, you are probably more than capable of learning a programming language. If you are just tallying up a few numbers to throw into a report or presentation, then yeah, no need to switch. As I mentioned in another post, it probably has to do with exposure and motivation.
      - boomzilla 13y ago
        Excel is a tool, and it's a really good tool for what it does, which is for applications that require: - very fast iteration cycles - a particular data model: grid of numbers which is very common in business world - support for everything under the sun: persistence (just hit save :), dialog UI, math formula, stats models, string functions, date/time functions (better than even Java's Joda), internationalization, localization, utf 8, and plenty more
- wikwocket 13y ago
  Excel already does this to a degree. If a cell has a formula that is inconsistent with its neighbors, it will be flagged. If a formula omits cells adjacent to the range it is working with, it will be flagged. Check out Options: Formulas: Error checking rules. It's almost as if Microsoft has been continually developing this software for years and years and they have seen many common mistakes. ;)
calinet6 13y ago
Making Excel "smart" is not exactly what he's talking about. He's playing with ideas. It's high level, but you can do high level sometimes. You need to. Your analysis is too low-level and nitpicky for this high level idea playground. Excel is still a dumb tool. He's absolutely right. We have ways of making smart tools. It's called "programming" and "database engineering." That part's a little too hard right now, and part of why it's hard is because if you try to make it easy to mould a flexible program to try to define what your data means and how it should work, it becomes so generic as to be as unusable and amorphic as a cloud of smoke. I've tried this, moving from a space where Excel was predominantly used, and trying to capture the process into an application. We tried to keep all the customizability and malleability as Excel: but I'm now convinced that was a huge mistake. It led us into genericland. It is possible to build applications that work for these processes, using the tools of programming and good data design. But that's why we have thousands of different applications all trying to solve these different problems, all of which you could probably represent in Excel in some way. The fact that all those applications exist means that people want something smarter than Excel. It proves the case. But we haven't yet bridged the gap between Excel's extreme flexibility and an Application's intelligence and process fit. That's because it's really difficult to make that work; to fit all the pieces together into something that makes sense in both realms. It's really hard! The solution will be a user interface masterpiece. I'm convinced of that. And it will be layered, like an onion, allowing people to build any application they need just by telling the computer about their data and how it fits together and what it should allow them to do. Someday we'll have a system—some sort of super-Rails—that makes this so easy that anyone can do it. Someday Programming will be an ancient art, something that only your Grandfather did, like crafting your own tools or woodworking or making jams and jellies. Someday that will be true. But the way we get there is by thinking about the difference between Dumb Flexible Excel and Smart Rigid Applications and how we bridge that gap, because it's difficult and it's possible. We have to think at this high level, way up in the clouds—and more people should, and there's no reason to shoot them down.
- jacques_chester 13y ago
  You can have adaptation or adaptiveness, but not both. Either a system is well-fitted to a purpose and needs professional intervention to change, or it is permissive and undemanding and can be turned to any purpose and do it poorly. Life is full of tradeoffs. Sometimes the general, permissive loosey-goosey system is what you want. Sometimes it isn't. It takes judgement to decide which is which and when to switch.

thiele 13y ago

There is low hanging fruit in the problems to solve though. For example, finding and highlighting outliers in a column. It's a relatively simple feature but yields big benefits to users.

pyoung 13y ago
Except it isn't that simple. Your definition of an outlier may be completely different from some one else's (95th percentile, median/regression method, or simple max/min, some of these are already possible in excel). Then, how is the information displayed? Highlighting cells? Well if you have +10k rows of data, than that won't be too useful. Creating a summary in another column/sheet will add to the already cluttered 'results' spreadsheet, etc... This is why I recommend using programming based tools for data analysis. Any non-programming tool has to find a balance between the number of features offered and the complexity/ease-of-use of the tool. With programming tools, you merely have to find the right package (or build your own) which essentially results in getting the exact set of features that you need to solve your problem.
- hamofgobelgope 13y ago
  I know people bag on VBA, but I'm a big advocate of effectively utilizing Excels tools along side using VBA to customize where necessary. This solves the problem in your first comment (users can define their own statistical limits and methods), while keeping the simplicity of excel and its tools for other users. The problem is that not everyone is a programmer. A "programming" tool might be great for you, but when you show it to co-workers for them to work with, they'll inevitably ask "Can I get this in Excel?". Excel+VBA allows for customization when the "complexity/ease-of-use" balance is out of sync with your needs, but to everyone else it's still just Excel. With this, those that can program have the option to solve the problem their way, while allowing those that don't program the means to solve the problem the way their used to.
  - pyoung 13y ago
    I have used VBA here and there, and try not to bag on it too much. There are some cases where using it can make sense, but in general, it seems to be a compromise. I get a little confused about the programmer/non-programmer dichotomy. If you are capable of implementing a complex model in excel, you are probably more than capable of learning a programming language. If you are just tallying up a few numbers to throw into a report or presentation, then yeah, no need to switch. As I mentioned in another post, it probably has to do with exposure and motivation.
    - boomzilla 13y ago
      Excel is a tool, and it's a really good tool for what it does, which is for applications that require: - very fast iteration cycles - a particular data model: grid of numbers which is very common in business world - support for everything under the sun: persistence (just hit save :), dialog UI, math formula, stats models, string functions, date/time functions (better than even Java's Joda), internationalization, localization, utf 8, and plenty more
wikwocket 13y ago
Excel already does this to a degree. If a cell has a formula that is inconsistent with its neighbors, it will be flagged. If a formula omits cells adjacent to the range it is working with, it will be flagged. Check out Options: Formulas: Error checking rules. It's almost as if Microsoft has been continually developing this software for years and years and they have seen many common mistakes. ;)

calinet6 13y ago

Making Excel "smart" is not exactly what he's talking about. He's playing with ideas. It's high level, but you can do high level sometimes. You need to. Your analysis is too low-level and nitpicky for this high level idea playground. Excel is still a dumb tool. He's absolutely right. We have ways of making smart tools. It's called "programming" and "database engineering." That part's a little too hard right now, and part of why it's hard is because if you try to make it easy to mould a flexible program to try to define what your data means and how it should work, it becomes so generic as to be as unusable and amorphic as a cloud of smoke. I've tried this, moving from a space where Excel was predominantly used, and trying to capture the process into an application. We tried to keep all the customizability and malleability as Excel: but I'm now convinced that was a huge mistake. It led us into genericland. It is possible to build applications that work for these processes, using the tools of programming and good data design. But that's why we have thousands of different applications all trying to solve these different problems, all of which you could probably represent in Excel in some way. The fact that all those applications exist means that people want something smarter than Excel. It proves the case. But we haven't yet bridged the gap between Excel's extreme flexibility and an Application's intelligence and process fit. That's because it's really difficult to make that work; to fit all the pieces together into something that makes sense in both realms. It's really hard! The solution will be a user interface masterpiece. I'm convinced of that. And it will be layered, like an onion, allowing people to build any application they need just by telling the computer about their data and how it fits together and what it should allow them to do. Someday we'll have a system—some sort of super-Rails—that makes this so easy that anyone can do it. Someday Programming will be an ancient art, something that only your Grandfather did, like crafting your own tools or woodworking or making jams and jellies. Someday that will be true. But the way we get there is by thinking about the difference between Dumb Flexible Excel and Smart Rigid Applications and how we bridge that gap, because it's difficult and it's possible. We have to think at this high level, way up in the clouds—and more people should, and there's no reason to shoot them down.

jacques_chester 13y ago
You can have adaptation or adaptiveness, but not both. Either a system is well-fitted to a purpose and needs professional intervention to change, or it is permissive and undemanding and can be turned to any purpose and do it poorly. Life is full of tradeoffs. Sometimes the general, permissive loosey-goosey system is what you want. Sometimes it isn't. It takes judgement to decide which is which and when to switch.