Hacker News Reader

Comment by Irregardless | Hacker News Reader

The simplicity and flexibility of Excel make it great for one-offs and random hack jobs that don't require a new application or full-fledged report. At the same time, it has some minor idiosyncrasies that can be infuriating and make me wary of trusting it in the hands of average end-users who need to enter/analyze important data.

Automatic type conversion is my favorite. I can't even count the number of times I've received Excel spreadsheets where data was completely lost because of it. Leading zeroes at the beginning of your account number? Excel will gladly chop those off for you. Order number looks like a date because they used the year as a prefix? No worries, Excel will change that to a standard date and completely forget the original format.

Maybe I'm just crazy, but I don't think a business-oriented application should favor convenience that much more than data integrity.

View on HN

This is why I'm continually amazed that Excel is used in business at all. I use a command line program to record my hours and can report the time I've worked on a project to the second. Unfortunately, my Excel-based timesheet sucks at math, and shaves off time when it converts my HH:MM:SS totals to the decimal figures (HH.x) required by payroll. It's so bad, sometimes the values displayed in the columns don't add up to the displayed total if you enter them in a calculator. No sweat, I only get paid for the displayed total, and at the end of the year I compute the difference and add it to my last timesheet.

I can't imagine trusting my business finances to a program that can't deliver a reasonable amount of precision. I wonder how many fortunes are made by people well-placed enough to exploit Excel's weaknesses ("Hmmm, Excel shows that we made only $10,000,000 at our bake sale. What should I do with this leftover $700,000?" or "I can use Excel to show you that I owe you less money than I actually do.").

Let's be clear, this is a type conversion issue for seconds, Excel's not going to start adding up normal numbers strangely.

And the point of this article and the point he's trying to drive home is that it lets non-programmers actually use their computers for computing.

For most businesses a home-brewed excel spreadsheet vs a $50,000 custom program that any of us here wrote?

The harsh reality is that the Excel version written by Jane from accounting who's the Excel whizz or even the smart college temp will probably be better and cheaper than anything we could ever give them.

Until Jane leaves for greener pastures.

I do understand what you're saying though, there is a time, place, and trade offs for everything related to computing.

(imho) Most 'businessy' systems can and should start out as spreadsheets, it lets the business people solve the problems with process design, and prove its real-world value without bringing a costly developer in. The developers (like myself) should be there to take the codified mess that results, clean it up, improve usability, bring in more stability, and accountability. - Sadly, that is rarely how things go.

excel as a functional prototyping tool.

I have no problems with this. The internal logistics of a business are better a codified mess then legend and arcanum amongst the locals.

The problem is that by the time they've modified the spreadsheet app to solve issues with process design, workflow, edge cases, it's an unholy mess that nobody wants to touch.

"The harsh reality is that the Excel version written by Jane from accounting who's the Excel whizz or even the smart college temp will probably be better and cheaper than anything we could ever give them."

Too true, but I've always wondered if this is true because visual programming languages have never really been accepted as part of development or because visual programming languages were touted as a replacement for "normal" programming? Maybe it is the bad taste CASE tools left.

No, its not that.

For one Excel can be learned just after looking at it, and most people have someone who can teach them the basics.

Plus you just click one button and turn it on and then its a matter of making sure your logic pans out and the numbers get calculated correctly.

Let me restate my thesis because I sense I wasn't clear given your response.

I believe that programmers rejection of visual programming tools has lead to a situation where programmers haven't been able to easily integrate Excel into programmer's workflow in a meaningful way or evolve tools as easy to use as Excel that translate directly to code.

Actually, Excel does add up normal numbers strangely, in that like (that many computer programs) it takes user input in decimal format but stores and operates on it in binary floating point.

Its hardly as if systems that do arithmetic on exact arbitrary-precision decimal (or rational) values and only resort to approximating with binary floating point when an operation requires it haven't been around for decades.

If you pre-format the cell as text, any value input will be stored in its entirety. For one offs, prepending any string with ' will force Excel to store as text.

For date, it typically does not truncate seconds, so not sure what's happening there.

If I were to pick one thing I wish Excel handled better out of the box, it was it's annoying habit of reformatting numbers pasted in.

My first move in a fresh Excel workbook is Select All, Format Cells as Text.

I've run into those type conversion problems before. The data is usually still there, it's normally that Excel has decided to use a certain display format for the cell (such as a date). Simply set the formatting back to "General" and all is fine. Usually.

"General" is not what you want. General enables the automatic type conversion.

"Text" turns the type conversion off.

It's possible I've missed some setting somewhere in the tangled web of menus, but I'm fairly certain the data is lost in the case of both those conversions.

For example: Type 09-2012 into a cell and hit Enter. It will probably turn into Sep-12 (or some other variation depending on your default date settings). If you convert that to Text or General, it turns into 41153 -- the number of days since 1/1/1900.

Excel recognizes all dates from 1/1/1900 through 12/31/9999, so it happily converts anything from 01-1900 to 12-9999 into a date. You can create a function to convert them back, but it would have to be based on the assumption that the format was originally ##-#### (which you may or may not know for certain depending on where the spreadsheet is coming to you from).

"No worries, Excel will change that to a standard date and completely forget the original format."

I hate this particular one so much.

>Automatic type conversion is my favorite.

Amen to that!! Yes, "Clippy" (Excel) gets a little too enthusiastic at times, but the missing piece here is metadata. So Clippy does what Clippy can without metadata, and the results can be laughable at times.

My fix for that, _if I'm sourcing the data_, is to source it as tab-delimited (not CSV-delimited) from the clipboard into an empty sheet area twice:

With the first paste, I 'visually' correct the columns' incorrectly guessed data types, but Clippy oddly doesn't attempt to reformat the data. No worries, I just select all, hit DELETE, then do my second paste using the same starting cell as my first paste. Clippy doesn't interfere this time, and my data comes up all beautifully typed as I want it. I don't know why, but the DELETE key doesn't kill the data types for cells, and I'm glad it doesn't! Not visible, not obvious, but very useful.

Depending on how you want to use the data, you may be able to get around this problem by adding a ' infront of the number. It won't show on the cell display and acts as an instruction to hold leading zeros.

Reply on news.ycombinator.com