4 ms·

Oh my goodness, you're going to get my spreadsheet rant. This is what frustrates me every single time I use Excel (or the Google Docs spreadsheet, for that mat

Oh my goodness, you're going to get my spreadsheet rant. This is what frustrates me every single time I use Excel (or the Google Docs spreadsheet, for that matter). I may not be answering your question since I'm talking about usability instead of more powerful features, but I can't but imagine that there'd be a market for simple and easy to use, even if it turns out it's not going to be addressed by your particular startup... I have a table, some data that I've laid out in rows and columns. Something simple. How much money I've been paid on my invoices to clients, for example, one invoice per row. Then I want to sum the column, to get how much I've been paid in total. (Yes, I'm talking about a very simple spreadsheet. But that's my point, that something so simple is still messed up!) So I type in a formula: =sum(C2:C10) Now I add a row, to put in another entry. Does my sum change, to include the new row? (C2:C11) No, it does not. So I do not want to be saying sum(C2:C10). I want to say, here is my simple table, and give me the sum of this column. Which, I don't know what the language would look like, but if I named my table "invoices" maybe it would be sum(invoices.C) or sum(invoices.amount) or something. Every time someone comes out with a new spreadsheet (Excel, OpenOffice, Google Docs...) I look to see if it is easier to use. Nope! Everyone is too busy being compatible with the last guy.

Timothee 18y ago
Numbers, from the iWork suite, does this. You can name your rows and columns and just them in your formulas: http://www.apple.com/iwork/numbers/ http://www.apple.com/iwork/numbers/ Of course, it's not perfect if you look at the proprietary format, at the smaller number of formulas than Excel, and so on. But it's a nice piece of software as far as I'm concerned.
- CatDancer 18y ago
  I don't have a Mac so I don't have a way to tell if iWork does what I want or not, but note that simply being able to name a range doesn't do it.
  - deleted 18y ago
    [deleted]
  - Timothee 18y ago
    It's actually simpler than naming a range because you can use the names of the rows and columns that you have in the header of the table. It's a moot point since you don't have a Mac but from what you describe, Numbers does what you're missing. I saw in one of your later comments that you were also talking about multiple tables on the same page. Numbers actually manages tables as independent objects of a page. So, in a table you can ask for the sum of a whole column without getting the numbers from another unrelated table on the same page. That's something that always bothered me in Excel.
    - CatDancer 18y ago
      Tables as independent objects on the page does sound like what I'm getting at. Of course I'd need to see it to see if they're doing it the way I want ^__^
ctkrohn 18y ago
Excel allows you to do named ranges. Select a range, then type its name in the address box. Then, in another cell, you can type =sum(myrange).
- CatDancer 18y ago
  Doesn't help. The point is Excel doesn't know what range I want when I extend my table, not whether I can give the range a name or not.
  - ecommercematt 18y ago
    Am I missing something, or wouldn't =sum(C:C) work?
    - CatDancer 18y ago
      Would that sum the entire column in the spreadsheet? But what if I wanted to have a couple tables on a page (which I often do), or my sum below the numbers?
      - whatusername 18y ago
        see my post above. if you use insert row - then formulas respond and will go from C2:C10 to C2:C11 (tested in excel2003 at least)
        sctb 18y ago
        This is probably useful in many cases, but one could imagine where the inserted row bisects other ranges in other columns unintentionally.
quantumhobbit 18y ago
Apple Numbers allows you to simple say =Sum(C) to sum up an entire row.
whatusername 18y ago
You can do most of that (pretty simply) in excel. For your data C2:C10 - Do a field: sum(C2:C11) Then when you want to add more data - right-click on the row (11) and "insert" That will update your sum calculation. Also - you can do named fields - so that if you select the fields C2:C11 - then you can name them as "invoices" (in excel 2003 it's in the top left corner - there's a selection box you can type in. Just select and type a name in there). The lets you do the command sum(invoices) Also - don't forget you can do something like sum(C:C) which will just give you everything in C column..
- CatDancer 18y ago
  Do a field: sum(C2:C11) I.e. leave a blank row at the bottom of my table, and have the sum include that blank row? I actually know about that trick (thanks :)... what I want is a spreadsheet that does what I want without my tricking it.
  - amobilebiz 18y ago
    You don't have to trick it in Excel 2003. If you have 10 rows (i.e. c1 thru c10) and in c11 you have the sum if you right click on row 11 and insert row it will insert a row above 11 and update your formula for you in c11 to include the new row.
erso 18y ago
You can retain formulas when you add a row but it only works if you add a row before the last row where your formula applies. You can do what you're wanting with dynamic named ranges. From reading some of your other responses it seems like you don't want to sum the entire row, maybe because you have the sum listed at the bottom of the dataset or something. With a dynamic named range you can add rows to the bottom of the range, and you also get a nice name to reference it by. It works by using offset and count/counta to deliver a range based on how many occupied cells there are (depending on if you use count or counta). There are a few ways of doing it listed here: http://www.ozgrid.com/Excel/DynamicRanges.htm http://www.ozgrid.com/Excel/DynamicRanges.htm In my experience you can do an incredible amount of things in Excel before you even break into doing stuff in VBA. You just have to look at any of the numerous resources out there that have tricky formulas available.
gruseom 18y ago
CatDancer, the problem is that the system can't always know what you intended: should the range be "greedy" and jump to incorporate the new numbers as you append them, or should it be "strict" and keep to the boundaries you originally gave it? Sometimes the latter behavior is what is desired, and expanding to C2:C11 would be wrong in that case. So what's really needed here is a lightweight way to communicate your intention to the system. (Actually, you can do this in Excel - http://tinyurl.com/26b78 http://tinyurl.com/26b78 - but it's far from lightweight.) I'm assuming that (in terms of your example) the invoice amounts that you're adding up would form a contiguous range of numbers, and that this range would be bounded by whitespace. That is, you might have some other range that used column C -- say "expenses" -- but it would be lower down, say starting at C15, and there would be at least one blank cell between the two. Is that correct? If it weren't for that lower range, you could just take the sum of the whole column and you'd be good. But it's too inconvenient (and not the "spreadsheet way") to force everything into separate columns. If the above is correct, how would you feel about being able to define a range with a notation like this: "C2:C✱", meaning "the range of cells that starts at C2 and goes down until it hits whitespace"? Then as you add numbers to C11, C12, etc., the range would automatically expand to include them. But you'd still have to be careful to ensure there was a "moat" of whitespace around your invoice range. If you filled in the last non-whitespace cell before your other table, you'd now have connected the two tables in such a way that "C2:C✱" would leap down to the end of the second table. In other words you'd be lumping "invoices" together with "expenses" which is probably incorrect.
jaxn 18y ago
When Excel asks if you want to use the list builder, you do. That is exactly what it does. With the list builder you are always given an extra row at the bottom to continue adding to the list. Any formulas below the list builder will be pushed down and expanded. The list builder also turns on Auto Filters for the list as well.
CatDancer 18y ago
I see I wasn't very clear about the point of my rant... I apologize to everyone who has taken the time to thoughtfully offer me solutions of how to get Excel to do this, but I know about that. I should have explained that I know about getting Excel to extend a range when I insert a row using techniques such as having the range include a blank row at the bottom, and I'm not surprised to hear that Excel has a feature like "list builder" bolted on. When I said, "I'm frustrated every single time I use a spreadsheet", it's not that I can't do whatever it is that I need to get done, I just get annoyed when products are made hard to use when they don't have to be. It's not so much a personal frustration as that I've spent a lot of time at non-profits helping non-computer people use computers, and it's a huge waste of their time and of my time to have to train them how to manipulate the software to get what they want instead of the software just doing it. C2:C11 was a tremendous advance in 1979 when personal computers had 48K of memory and 40x25 character screens, but goodness gracious, it's thirty years later! Making something easier to use is a tremendous amount of hard work, but it isn't conceptually all that hard to understand: you look at what people are doing, and you write software to implement that, instead of making them manipulate the software to do the implementation themselves. I haven't looked at it myself so I don't know if Apple got it right or not, but from Timothee's comment that "Numbers actually manages tables as independent objects of a page", it sounds like they're at least trying.
- mattmcknight 18y ago
  I think your rant is somewhat misplaced as inferring the user desire in this case is not always possible. In general, I find it to be dangerous behavior when merely adding data changes formulas. I think the proper action is to insert a row. On the other hand, having defined tables with in a workbook is great idea, but it makes the whole application a wee bit more complicated. I'd like to see it in something of a hybrid between Access and Excel, where you can mix structured and tabular data.
  - CatDancer 18y ago
    No, I don't want the spreadsheet to "infer" my desire or for it to change my formulas when I enter data. The formula should describe the calculation I want performed and it should continue to work even when I enter new data. For example, if I have a "defined table" as you say, I should be able to ask it for a sum of a column in the table, and have it continue to work even if I add new data to the table, with hacks or trickery or invoking obscure commands.

Timothee 18y ago

Numbers, from the iWork suite, does this. You can name your rows and columns and just them in your formulas: http://www.apple.com/iwork/numbers/ http://www.apple.com/iwork/numbers/ Of course, it's not perfect if you look at the proprietary format, at the smaller number of formulas than Excel, and so on. But it's a nice piece of software as far as I'm concerned.

CatDancer 18y ago
I don't have a Mac so I don't have a way to tell if iWork does what I want or not, but note that simply being able to name a range doesn't do it.
- deleted 18y ago
  [deleted]
- Timothee 18y ago
  It's actually simpler than naming a range because you can use the names of the rows and columns that you have in the header of the table. It's a moot point since you don't have a Mac but from what you describe, Numbers does what you're missing. I saw in one of your later comments that you were also talking about multiple tables on the same page. Numbers actually manages tables as independent objects of a page. So, in a table you can ask for the sum of a whole column without getting the numbers from another unrelated table on the same page. That's something that always bothered me in Excel.
  - CatDancer 18y ago
    Tables as independent objects on the page does sound like what I'm getting at. Of course I'd need to see it to see if they're doing it the way I want ^__^

ctkrohn 18y ago

Excel allows you to do named ranges. Select a range, then type its name in the address box. Then, in another cell, you can type =sum(myrange).

CatDancer 18y ago
Doesn't help. The point is Excel doesn't know what range I want when I extend my table, not whether I can give the range a name or not.
- ecommercematt 18y ago
  Am I missing something, or wouldn't =sum(C:C) work?
  - CatDancer 18y ago
    Would that sum the entire column in the spreadsheet? But what if I wanted to have a couple tables on a page (which I often do), or my sum below the numbers?
    - whatusername 18y ago
      see my post above. if you use insert row - then formulas respond and will go from C2:C10 to C2:C11 (tested in excel2003 at least)
      - sctb 18y ago
        This is probably useful in many cases, but one could imagine where the inserted row bisects other ranges in other columns unintentionally.

quantumhobbit 18y ago

Apple Numbers allows you to simple say =Sum(C) to sum up an entire row.

whatusername 18y ago

You can do most of that (pretty simply) in excel. For your data C2:C10 - Do a field: sum(C2:C11) Then when you want to add more data - right-click on the row (11) and "insert" That will update your sum calculation. Also - you can do named fields - so that if you select the fields C2:C11 - then you can name them as "invoices" (in excel 2003 it's in the top left corner - there's a selection box you can type in. Just select and type a name in there). The lets you do the command sum(invoices) Also - don't forget you can do something like sum(C:C) which will just give you everything in C column..

CatDancer 18y ago
Do a field: sum(C2:C11) I.e. leave a blank row at the bottom of my table, and have the sum include that blank row? I actually know about that trick (thanks :)... what I want is a spreadsheet that does what I want without my tricking it.
- amobilebiz 18y ago
  You don't have to trick it in Excel 2003. If you have 10 rows (i.e. c1 thru c10) and in c11 you have the sum if you right click on row 11 and insert row it will insert a row above 11 and update your formula for you in c11 to include the new row.

erso 18y ago

You can retain formulas when you add a row but it only works if you add a row before the last row where your formula applies. You can do what you're wanting with dynamic named ranges. From reading some of your other responses it seems like you don't want to sum the entire row, maybe because you have the sum listed at the bottom of the dataset or something. With a dynamic named range you can add rows to the bottom of the range, and you also get a nice name to reference it by. It works by using offset and count/counta to deliver a range based on how many occupied cells there are (depending on if you use count or counta). There are a few ways of doing it listed here: http://www.ozgrid.com/Excel/DynamicRanges.htm http://www.ozgrid.com/Excel/DynamicRanges.htm In my experience you can do an incredible amount of things in Excel before you even break into doing stuff in VBA. You just have to look at any of the numerous resources out there that have tricky formulas available.

gruseom 18y ago

CatDancer, the problem is that the system can't always know what you intended: should the range be "greedy" and jump to incorporate the new numbers as you append them, or should it be "strict" and keep to the boundaries you originally gave it? Sometimes the latter behavior is what is desired, and expanding to C2:C11 would be wrong in that case. So what's really needed here is a lightweight way to communicate your intention to the system. (Actually, you can do this in Excel - http://tinyurl.com/26b78 http://tinyurl.com/26b78 - but it's far from lightweight.) I'm assuming that (in terms of your example) the invoice amounts that you're adding up would form a contiguous range of numbers, and that this range would be bounded by whitespace. That is, you might have some other range that used column C -- say "expenses" -- but it would be lower down, say starting at C15, and there would be at least one blank cell between the two. Is that correct? If it weren't for that lower range, you could just take the sum of the whole column and you'd be good. But it's too inconvenient (and not the "spreadsheet way") to force everything into separate columns. If the above is correct, how would you feel about being able to define a range with a notation like this: "C2:C✱", meaning "the range of cells that starts at C2 and goes down until it hits whitespace"? Then as you add numbers to C11, C12, etc., the range would automatically expand to include them. But you'd still have to be careful to ensure there was a "moat" of whitespace around your invoice range. If you filled in the last non-whitespace cell before your other table, you'd now have connected the two tables in such a way that "C2:C✱" would leap down to the end of the second table. In other words you'd be lumping "invoices" together with "expenses" which is probably incorrect.

jaxn 18y ago

When Excel asks if you want to use the list builder, you do. That is exactly what it does. With the list builder you are always given an extra row at the bottom to continue adding to the list. Any formulas below the list builder will be pushed down and expanded. The list builder also turns on Auto Filters for the list as well.

CatDancer 18y ago

I see I wasn't very clear about the point of my rant... I apologize to everyone who has taken the time to thoughtfully offer me solutions of how to get Excel to do this, but I know about that. I should have explained that I know about getting Excel to extend a range when I insert a row using techniques such as having the range include a blank row at the bottom, and I'm not surprised to hear that Excel has a feature like "list builder" bolted on. When I said, "I'm frustrated every single time I use a spreadsheet", it's not that I can't do whatever it is that I need to get done, I just get annoyed when products are made hard to use when they don't have to be. It's not so much a personal frustration as that I've spent a lot of time at non-profits helping non-computer people use computers, and it's a huge waste of their time and of my time to have to train them how to manipulate the software to get what they want instead of the software just doing it. C2:C11 was a tremendous advance in 1979 when personal computers had 48K of memory and 40x25 character screens, but goodness gracious, it's thirty years later! Making something easier to use is a tremendous amount of hard work, but it isn't conceptually all that hard to understand: you look at what people are doing, and you write software to implement that, instead of making them manipulate the software to do the implementation themselves. I haven't looked at it myself so I don't know if Apple got it right or not, but from Timothee's comment that "Numbers actually manages tables as independent objects of a page", it sounds like they're at least trying.

mattmcknight 18y ago
I think your rant is somewhat misplaced as inferring the user desire in this case is not always possible. In general, I find it to be dangerous behavior when merely adding data changes formulas. I think the proper action is to insert a row. On the other hand, having defined tables with in a workbook is great idea, but it makes the whole application a wee bit more complicated. I'd like to see it in something of a hybrid between Access and Excel, where you can mix structured and tabular data.
- CatDancer 18y ago
  No, I don't want the spreadsheet to "infer" my desire or for it to change my formulas when I enter data. The formula should describe the calculation I want performed and it should continue to work even when I enter new data. For example, if I have a "defined table" as you say, I should be able to ask it for a sum of a column in the table, and have it continue to work even if I add new data to the table, with hacks or trickery or invoking obscure commands.