3 ms·

'"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 th

by 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.

by niggler 14y ago

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.