Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What can't you do in Excel?

news.ycombinator.com

Post reply on HN
Print view View original

91–100 of 201 posts

Re: Ask HN: What can't you do in Excel?

#91

Post by jwilliams »

notauser wrote: post #19
rms wrote: post #2

Queries. 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 can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables.

This is true, you can do a lot with this. And if you're prepared to make a lot of columns with the various flags you need, you can build up quite complex queries - having an easy way to do this would be nice though.

I've spent a lot of time in Financial Services and it's surprising how much of the industry is run on spreadsheets, especially investment banking - and I mean online - they'll have Excel running all day, receiving real time price feeds, running a calculation and republishing. Excel is pretty much the glue that holds the whole industry together.

So I also agree on the maintenance issue - These sheets can get quite complex and it's almost impossible for someone to understand coming in cold. It would be very useful to have a workflow model on top - which I guess is really adding the algorithm aspect - but I've never seen a speadsheet metaphor that does this well.

Re: Ask HN: What can't you do in Excel?

#92

Post by Tangurena »

DenisM wrote: post #30
Tangurena wrote: post #26

Earlier quoted context omitted.

Sharepoint is fine if you are only tacking things at the file level. You can't answer stuff like "who changed the formula in E5?" Or even "how long has the formula in E5 been wrong?" Some kind of auditability of Excel spreadsheets would save enormous amouns of money and time.

You can diff two versions against each other. Of course it's not as nice as "blame", which I think is what you want here?

No. I'm not really looking for blame-generation, although others would. I don't do auditing, but some larger financial institutions do.

When investigating bugs (and more than a few times, we're turning into real code something cobbled out of an Excel spreadsheet, so the reference standard is the old spreadsheet), it helps to know why something is different from another, and why the formula in E5 is different than the formula in E4 or E6. It is incredibly easy to screw up formulas with inserting/removing rows with cut & paste.

Two sample foul ups: http://thedailywtf.com/Articles/The-Great-Excel-Spreadsheet.... http://thedailywtf.com/Articles/The-Revealing-Spreadsheet.as...

I've encountered worse situations in the past than those 2 dailyWTF episodes (as well as seen one old employer's code on that site).

Word has a feature where you can see the changes, what was previously present, and who changed it when. Something like.

Re: Ask HN: What can't you do in Excel?

#93

Post by imp »

Smart conversions. If I have a graph of something in Celsius, I have to copy the data, edit it, and create a new graph just to convert it to Fahrenheit. Same with any other unit of measurement. When I was an engineer my biggest gripe with Excel was that it didn't know anything about my data.

Re: Ask HN: What can't you do in Excel?

#97

Post by sctb »

whatusername wrote: post #68
CatDancer wrote: post #62

Earlier quoted context omitted.

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?

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)

This is probably useful in many cases, but one could imagine where the inserted row bisects other ranges in other columns unintentionally.

Re: Ask HN: What can't you do in Excel?

#98

Post by jmackinn »

I am a huge fan of Excel. It's the program that I use most for doing work.

At the entry user end, there are so many features that are simply unusable or technically way to difficult. For the power user of Excel, there is very little that can't be accomplished. It's power is basically limitless with VBA (or choose your favorite scripting language). Apart from the comment suggesting larger sizes to the sheets (larger than 1 million rows) and being online (something that would really not fly in most corporations) I can't find a problem in here that can't be accomplished with Excel and a firm knowledge of scripting for it. This may be a cop-out though as you must actually script the stuff yourself. This, however; is why I love this program so much.

So as for building a more powerful spreadsheet program for the power user market, it's going to be very hard for anyone to produce something that does more because it's already basically limitless.

For the novice user though, the program is convoluted, confusing and extremely limited. The novice user also represents a much bigger market. I have had several jobs simply because people couldn't do things in Excel that I assumed a monkey could do. Companies love Excel even though 99% of employees at them have no idea how to do basic things with it (summing columns for example). Giving some of the 99% of employees a program that they can do basic to intermediate things without having the limitless back end scripting power would put me out of many of my jobs.

Excel at the start is like a country kid visiting the big city for the first time. There is just way to much power in it and the map for getting around is far too confusing, but once you've been living there for a while everything about it becomes a breeze. Making that adjustment easier would be a huge benefit.

On a side note I enjoy using Google Spreadsheets but one of my top wishes is that Google, or someone, would implement either a Google scripting language or a allow for other scripting languages (Ruby, Python, VBA) to be used.

Re: Ask HN: What can't you do in Excel?

#100

Post by cubicle67 »

Tangurena wrote: post #26
DenisM wrote: post #17

Earlier quoted context omitted.

Did you try Sharepoint? It does versioning, version control and a bit of collaboration for Excel.

Sharepoint is fine if you are only tacking things at the file level. You can't answer stuff like "who changed the formula in E5?" Or even "how long has the formula in E5 been wrong?" Some kind of auditability of Excel spreadsheets would save enormous amouns of money and time.

No, Sharepoint is not fine. At its core, Sharepoint is one enormous ugly kludge.
Post reply on HN