News for hackers. Stuff that matters.
Re:Damn Excel – How the 'most important application' is ruin... by crazygringo on Saturday April 20, 2013 @07:53PM (#5583279)
Obviously, Excel isn't exactly at fault -- it's the people using it, as echoed by many comments here.

HOWEVER, spreadsheets are rather unique in the fact that they hide the very formulas used to generate everything, unless you have a specific cell selected.

So, opposite of computer programs, they're almost impossible to "read through" and verify that everything makes sense. If 99 cells have identical formulas, and 1 in the middle has a slight typo, how on earth are you ever going to see that?

I wonder if there are other similar spreadsheet-like tools that one might use, that make a point of showing everything going on in the spreadsheet, rather than trying to hide it?

Re:Damn Excel – How the 'most important application' is ruin... by vorg on Saturday April 20, 2013 @10:13PM (#5583600)
> If 99 cells have identical formulas

If we want to enter 99 identical (or relatively identical) formulas into 99 cells, then we should be able to select all 99 cells, and enter the formula once without any dragging. If we want to change that formula, we should be able to change it once and not need to remember to redrag. We should be able to enter a formula into the column heading and know it'll apply whenever relevant to every cell in a column. That we can't means Excel (and Lotus and OpenOffice) is defective.

What QA is to blame for isn't letting unfinished spreadsheets out the door, but rather for letting users in a company use defective tools like Excel and Lotus and OpenOffice in the first place.

Many of us developers learnt the hard way that visual programming tools from the 1990's don't work, which is why scripting languages became popular. Users in corporations (and the IT staff who authorize what tools that can use) need learn the same lesson.

Re:Damn Excel – How the 'most important application' is ruin... by jodrellblank on Sunday April 21, 2013 @11:32AM (#5585145)
then we should be able to select all 99 cells, and enter the formula once without any dragging.

Double-click the fill-handle (thing you click on to drag) and Excel will fill-down as far as the next empty row.

If we want to change that formula, we should be able to change it once and not need to remember to redrag.

How would it know whether you want to change it in one place for a special case, or update many places?

Re:Damn Excel – How the 'most important application' is ruin... by Someone on Monday April 22, 2013 @02:01PM (#5590763)
"That we can't means Excel (and Lotus and OpenOffice) is defective"

You can complain about the UI (I certainly do. For example, try inserting a row in an array formula), but the feature is there: http://office.microsoft.com/en-us/excel-help/introducing-arr...

Re:Damn Excel – How the 'most important application' is ruin... by pcl on Sunday April 21, 2013 @11:02AM (#5585043)
> we should be able to select all 99 cells, and enter the formula once without any dragging.

Agreed. You can do this by creating your own macros, but it's a pain. Considering how often I do this for personal spreadsheets, you'd think that this would have been simplified long ago.

And, this deficiency has been ported to both Numbers and Google spreadsheets.

Re:Damn Excel – How the 'most important application' is ruin... by celticjames on Saturday April 20, 2013 @08:12PM (#5583309)
After reading your comment, I did some googling to find something that could convert an existing spreadsheet into code. I found a few commercial solutions that turn Excel into C++ or Java. I think the idea is that Excel becomes the prototyping tool for non-coders. That seems like a better idea than treating Excel as the final product. I feel like there's a new paradigm for both spreadsheets and programming in there somewhere but I need a bigger brain to see it.
Re:Damn Excel – How the 'most important application' is ruin... by gruseom on Sunday April 21, 2013 @02:10AM (#5584050)
I suspect the reason those commercial solutions aren't very prominent is that they don't work very well. Spreadsheet compilation is a harder problem than it appears to be.
Re:Damn Excel – How the 'most important application' is ruin... by greiskul on Saturday April 20, 2013 @08:35PM (#5583365)
Some sort of http://en.wikipedia.org/wiki/Reactive_programming to the masses would be nice.
Re:Damn Excel – How the 'most important application' is ruin... by Too on Sunday April 21, 2013 @03:15AM (#5584168)
Related to this is that there are no descriptive variable names either. Just a massive three dimensional grid of, non typesafe, memory indexed by letter and number: sheet/row/column. Imagine programming like this:

    A5 = (C6*C6 * Sheet5:D1 + Sheet6:D1 / B3)
I'm not saying you have to use excel like this, there are options to do for example Sum(myTable[myRow]) and pivottable reduces alot of manual equations also. However both of these alternatives require that you format everything nicely into one table per sheet like a proper database and use the "Format as table" option, you almost need a DBA to use excel properly! The problem is that normal people don't use excel like this, they paste random data around everywhere, they want to have the sum, the average and lots of other calculations both below and on the right side of the data so you can see both simultaneously but once you do this you've screwed up your table and we're back on square one.
Re:Damn Excel – How the 'most important application' is ruin... by jodrellblank on Sunday April 21, 2013 @10:53AM (#5585016)
Related to this is that there are no descriptive variable names either.

Sure there are; You can add a name for a cell or range of cells (and have been able to since at least Excel 97):

http://excelsemipro.com/wp-content/uploads/2010/09/Named-Con...

Re:Damn Excel – How the 'most important application' is ruin... by adventured on Saturday April 20, 2013 @07:57PM (#5583285)
The software should have pattern hints.

If you've got 99 cells with identical formulas, and one without, it's not far fetched for Excel to speculate that there may be a mistake (particularly if they're within X% of being identical). At which point it can provide a simple, non-intrusive hint.

And going further up the wishful thinking tree, into more difficult territory, Excel should be able to grasp what you're attempting to do by understanding, say, the top 10,000 (arbitrary number) tasks commonly performed by people using spreadsheets. That 'understanding' could then tip Excel toward deciding whether there's a problem, and whether it should leave you a nice little illuminated icon at the top right of the program with an exclamation mark.

Microsoft should have insane amounts of data on everything people do with spreadsheets, and they should be able to make it radically more intelligent (without being obnoxious or intrusive - it should be optional and highly passive).

Re:Damn Excel – How the 'most important application' is ruin... by streptomycin on Saturday April 20, 2013 @08:14PM (#5583317)
If you've got 99 cells with identical formulas, and one without, it's not far fetched for Excel to speculate that there may be a mistake (particularly if they're within X% of being identical). At which point it can provide a simple, non-intrusive hint.

It already does this, actually. Enter some data in two columns, like:

    4	1
    2	3
    5	6
    3	2
    4	2
    2	1
Then enter a sum formula in the next column like =SUM(A1:B1) and drag it all the way down. Change one in the middle to something else, like =SUM(B3:B3) instead of =SUM(A3:B3) and a little green arrow appears in the corner. Hover above it an it explains there is an "inconsistent formula".
Re:Damn Excel – How the 'most important application' is ruin... by james1071 on Sunday April 21, 2013 @04:56AM (#5584349)
Spot that triangle among the million cells in a 1000 by 1000 table.
Re:Damn Excel – How the 'most important application' is ruin... by jodrellblank on Sunday April 21, 2013 @10:59AM (#5585028)
Click the "take me to possible formula errors" button.

http://www.c-sharpcorner.com/UploadFile/7e39ca/error-check-f...

Re:Damn Excel – How the 'most important application' is ruin... by EdiX on Sunday April 21, 2013 @04:42AM (#5584326)
> The software should have pattern hints.

I think a better idea would be to scrap the datasheet concept completely. Make people use a dataflow programming language, it will give you:

a) versioning of source code with standard tools

b) separation of inputs and process

c) program source that doesn't look like a game of battleship unless who wrote it was disciplined

d) strong typing

But it won't happen, god forbid we are faced with an initially steep learning curve.

Re:Damn Excel – How the 'most important application' is ruin... by unholyalliance on Saturday April 20, 2013 @09:07PM (#5583458)
You can actual show formulas in excel, instead of the values.

But that's not to say that it'd be easy to spot the one that is different. This is why excel has a little tooltip that pops up and says 'inconsistent formula' when it finds a cell that doesn't seem to belong.

Re:Damn Excel – How the 'most important application' is ruin... by rmah on Saturday April 20, 2013 @08:31PM (#5583356)
Excel does have that. Exactly that.
Re:Damn Excel – How the 'most important application' is ruin... by watershawl on Saturday April 20, 2013 @08:13PM (#5583315)
Excel does give hints when formulas are out of pattern. It's called "Inconsistent Formula" and it's displayed with a little triangle on the box.
Re:Damn Excel – How the 'most important application' is ruin... by jodrellblank on Sunday April 21, 2013 @11:01AM (#5585038)
HOWEVER, spreadsheets are rather unique in the fact that they hide the very formulas used to generate everything, unless you have a specific cell selected.

Unless you click the button to "show formulas" in Excel, then you can view them all at once.

http://i237.photobucket.com/albums/ff263/dharma-putra/Excel2...

http://www.lytebyte.com/wp-content/uploads/2008/04/view-form...

Re:Damn Excel – How the 'most important application' is ruin... by james1071 on Sunday April 21, 2013 @12:18PM (#5585292)
No use if the worksheet has thousands of rows.