6 ms·

I think an important aspect of what makes Excel formulas (relatively) useable to people who don't think they're programmers is their functional nature. You don'

by d4nt 14y ago

I think an important aspect of what makes Excel formulas (relatively) useable to people who don't think they're programmers is their functional nature. You don't have to simulate the program's state in your mind because there is no state. You hit return and it shows you what the result is, and if your formula gets too long you can break it up into multiple cells. Giving users a procedural programming language is only going to help a small number of Excels users - the ones who like thinking in a procedural way. For me, the real pain point in Excel's formula language is in how the path of least resistance leads you towards unmaintainable code. "SUM(A3:K754)" is not very meaningful. If only named ranges were more discoverable and useable, we might end up with more examples like "SUM(ProductSales2012)". Shameless plug: I'm working on a visual, functional programming tool for querying data that's aimed at people who currently only use Excel: http://querytreeapp.com http://querytreeapp.com. I think the advance in browser technology is going to bring a number of tools like this to market to challenge Excel's dominance and hopefully improve to quality of "shadow IT" in business.

niggler 14y ago
'"SUM(A3:K754)" is not very meaningful.' That's not true. You are looking at that formula in the context of a sheet, and excel takes great care to preserve the formula under meaningful transformations. For example, if you insert a row between rows 100 and 101, the formula will transform to "SUM(A3:K755)" without intervention.
- Agustus 14y ago
  Another thing that Excel does for the user is actively show what is being pointed to. If you open the formula [F2], Excel will highlight all the data references within the formula, making for an intuitive programming language within a data sheet.
- pasbesoin 14y ago
  What happens when you insert a row at the beginning or end? That's a bit less "certain", for most people, and can lead to incorrect sums. Re the grandparent, Excel does have named ranges. Some it constructs on its own based upon column headers and whatnot. Others can be explicitly defined. Unfortunately (again), the tools/widget Excel provided -- back a decade or more ago, when I lasted used it intensively -- were "out of the way" and fairly limited in terms of their interface and behavior/functionality. Still, if you have e.g. a column headed REVENUE and a column headed COST, you can insert "= REVENUE - COST" into an adjacent column in the same row, and Excel will know what you mean. And... in this case, it is less likely to become "confused" (do what it's programmed to, versus "do what I mean" -- DWIM) if/when you move individual cells around. P.S. What I do wish, or did, was that Microsoft would better document and explain publicly some of Excel's underlying behavior and edge cases. It can take some time, exposure, and mental effort to trick some of these out. Excel is a powerful tool that does its job very well. A good part of the problem is that with its ubiquitous distribution and use, much of the time this is like handing a toddler a loaded handgun to play with. The handgun is functioning just fine. Its deployment and use in this case is, however, less than optimal.
  - niggler 14y ago
    "what happens when you insert a row at the beginning or end?" Excel's documentation clearly explains the rules: "You can insert blank cells above or to the left of the active cell on a worksheet. When you insert blank cells, Excel shifts other cells in the same column down or cells in the same row to the right to accommodate the new cells. Similarly, you can insert rows above a selected row and columns to the left of a selected column. " So lets say you are looking at the formula =SUM(A3:A5). If you click A3 and hit insert, A3:A5 are shifted down to A4:A6 and the sum formula is =SUM(A4:A6) If you click A5 and hit insert, A5 is shifted down to A6 and the new row is inserted, so the formula does include the new cell and you see that: =SUM(A3:A6). The rule is fairly defininitve, and the BIFF8 Rk transformation is fairly straightforward
    - pasbesoin 14y ago
      "for most people" I've seen a lot of incorrect ranges, often the result of subsequent grid manipulations. With hopefully honest and sufficient humility, I submit that we are not "most people" -- in this respect.
revscat 14y ago
In iWork Numbers, you can refer to cells by the column and/or row name, if they have been provided. I believe this works with ranges, as well.
sinnerswing 14y ago
you have to replace Excel's every possible use case. Actually start with the top 25, and then.. you might have a "decent" Excel alternative.
josteink 14y ago
> "SUM(A3:K754)" is not very meaningful. If only named ranges were more discoverable and useable, we might end up with more examples like "SUM(ProductSales2012)". Convert your unstructured cells into an excel table, and besides all the eye-candy, ability to filter and sort your data, you already have that.
- jonchang 14y ago
  This. Say what you will about the Ribbon interface introduced in 2007, but Tables are really the reason to use Excel.
msellout 14y ago
I'd call Excel more declarative than functional.

by d4nt 14y ago

niggler 14y ago

'"SUM(A3:K754)" is not very meaningful.' That's not true. You are looking at that formula in the context of a sheet, and excel takes great care to preserve the formula under meaningful transformations. For example, if you insert a row between rows 100 and 101, the formula will transform to "SUM(A3:K755)" without intervention.

Agustus 14y ago
Another thing that Excel does for the user is actively show what is being pointed to. If you open the formula [F2], Excel will highlight all the data references within the formula, making for an intuitive programming language within a data sheet.
pasbesoin 14y ago
What happens when you insert a row at the beginning or end? That's a bit less "certain", for most people, and can lead to incorrect sums. Re the grandparent, Excel does have named ranges. Some it constructs on its own based upon column headers and whatnot. Others can be explicitly defined. Unfortunately (again), the tools/widget Excel provided -- back a decade or more ago, when I lasted used it intensively -- were "out of the way" and fairly limited in terms of their interface and behavior/functionality. Still, if you have e.g. a column headed REVENUE and a column headed COST, you can insert "= REVENUE - COST" into an adjacent column in the same row, and Excel will know what you mean. And... in this case, it is less likely to become "confused" (do what it's programmed to, versus "do what I mean" -- DWIM) if/when you move individual cells around. P.S. What I do wish, or did, was that Microsoft would better document and explain publicly some of Excel's underlying behavior and edge cases. It can take some time, exposure, and mental effort to trick some of these out. Excel is a powerful tool that does its job very well. A good part of the problem is that with its ubiquitous distribution and use, much of the time this is like handing a toddler a loaded handgun to play with. The handgun is functioning just fine. Its deployment and use in this case is, however, less than optimal.
- niggler 14y ago
  "what happens when you insert a row at the beginning or end?" Excel's documentation clearly explains the rules: "You can insert blank cells above or to the left of the active cell on a worksheet. When you insert blank cells, Excel shifts other cells in the same column down or cells in the same row to the right to accommodate the new cells. Similarly, you can insert rows above a selected row and columns to the left of a selected column. " So lets say you are looking at the formula =SUM(A3:A5). If you click A3 and hit insert, A3:A5 are shifted down to A4:A6 and the sum formula is =SUM(A4:A6) If you click A5 and hit insert, A5 is shifted down to A6 and the new row is inserted, so the formula does include the new cell and you see that: =SUM(A3:A6). The rule is fairly defininitve, and the BIFF8 Rk transformation is fairly straightforward
  - pasbesoin 14y ago
    "for most people" I've seen a lot of incorrect ranges, often the result of subsequent grid manipulations. With hopefully honest and sufficient humility, I submit that we are not "most people" -- in this respect.

revscat 14y ago

In iWork Numbers, you can refer to cells by the column and/or row name, if they have been provided. I believe this works with ranges, as well.

sinnerswing 14y ago

you have to replace Excel's every possible use case. Actually start with the top 25, and then.. you might have a "decent" Excel alternative.

josteink 14y ago

> "SUM(A3:K754)" is not very meaningful. If only named ranges were more discoverable and useable, we might end up with more examples like "SUM(ProductSales2012)". Convert your unstructured cells into an excel table, and besides all the eye-candy, ability to filter and sort your data, you already have that.

jonchang 14y ago
This. Say what you will about the Ribbon interface introduced in 2007, but Tables are really the reason to use Excel.

msellout 14y ago

I'd call Excel more declarative than functional.