[deleted]
Ask HN: What can't you do in Excel?
101–110 of 201 posts
Re: Ask HN: What can't you do in Excel?
#102Re: Ask HN: What can't you do in Excel?
#103I do not use Excel regularly and I thus am certainly not a power user. However, I would think scripting for plots would be useful (like I do in matlab or matplotlib) for repetitive, highly configured, high quality plots with the ability to easily export them as ps or pdf. This may very well be possible now (and I'm just ignorant), but that would be one thing that would be handy that I have not seen.
Re: Ask HN: What can't you do in Excel?
#104Queries. Excel isn't a database but a lot of people use it like one anyways.
You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables. For example, getting the sum bill of every person who lives in England looks like this in one workbook I have: {SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))} (If I want to see, say, the top 10 outstanding bills in England I have to use a pivot table). Excels biggest problem is that the workbooks…
You might find dynamic named ranges to help keep things orderly. That way you can refer to the ranges by name, and also not have to worry about your $E$1:$E10000 not being up to date when there's 15000 rows of data instead of 10000.
You could do something like this:
{SUM(IF(country_name = "England",bill_amt,0))}
Re: Ask HN: What can't you do in Excel?
#105Oh 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 s…
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
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.
Re: Ask HN: What can't you do in Excel?
#1061.) automatic smart chart creation - the spreadsheet guesses the kind of data I'm inputting and builds a chart on the fly.
2.) UI that values datasets over data points, or some sort of functionality that defines a dataset. Since most tasks deal with sets as a whole (and not individual points) this would result in a much cleaner, quicker interface. Also, you could start treating a dataset like a black box instead of a TON of cells with meaningless value, and thereby gain access to a lot of shortcuts not possible currently. This should allow easy data entry, dataset searching, and will keep all your scripting in your view. Best of all, there's no need to manipulate cells at all with a dataset approach.
Re: Ask HN: What can't you do in Excel?
#107Earlier quoted context omitted.
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/ 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.
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.
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.
Re: Ask HN: What can't you do in Excel?
#108ability to recurse.
Recursion is fun, but how would you use it in a spreadsheet?
Can't think of a concrete example at the moment, but there have been cases when I had to make a financial model with a lot of columns (and rows) where a recursive algorithm would have presented the result I was looking for using a lot less cells.
Re: Ask HN: What can't you do in Excel?
#109Automatic versioning and version control.
Or else the ability to save it as text (XML) in a way that will work with SVN so that we can have multiple people working on an Excel sheet saved that way and SVN will be able to merge it. When you try that with Excel, you get all kinds of conflicts in extra data that it's saving in the headers.
Re: Ask HN: What can't you do in Excel?
#110I would like to do conditional formatting on one column based on the contents of another column.