Ask HN: What would Excel's formula language look like if you invented it today?

Re:Ask HN: What would Excel's formula language look like if ... by mkl on Monday December 02, 2013 @10:16AM (#6834107)

I would do away with the opaque, error-prone cell references altogether and work exclusively with named columns, cells, etc. Yes, named cells and ranges are supported in most spreadsheet programs, but people don't seem to use them much since it's easy to get started with the cell references. Operating on named column ranges seems safer than "fill-down" etc. which it's easy to go wrong with.

I think this change would be more beneficial than changes to the formula language, though I think the one-liner style the formula languages encourage is quite problematic too.

[ Parent | on HN ]

Re:Ask HN: What would Excel's formula language look like if ... by alok-g on Monday December 02, 2013 @12:19PM (#6834944)

As an extensive user of named cells, one problem I have encountered with named cells is that copying-and-pasting to run a parallel scenario often results in a mess up -- cells in the copy inadvertently referring to the old cells, etc.

What would help is some sort of object-oriented programming with the named cells as opposed to the named cells behaving like global variables.

[ Parent | on HN ]

Re:Ask HN: What would Excel's formula language look like if ... by bashinator on Monday December 02, 2013 @11:17AM (#6834536)

I was helping my bartender friend with a spreadsheet for his per-bottle profits, and it would have been so much simpler if we could have named the columns after each liquor, and the rows for their wholesale-cost, serving-size, bottle-size, serving-charge, etc.

You'd probably need a way to override it to allow certain individual cells to have a unique name, though - constants if you will.

[ Parent | on HN ]

Re:Ask HN: What would Excel's formula language look like if ... by Someone on Monday December 02, 2013 @12:10PM (#6834868)

Constants should be in a separate sheet named 'config', 'constants', or the like. Alternatively, in Excel, one can define names with values that aren't stored in a cell (one can enter 42 or sin(d3) in the text box where one can edit name values in the 'Name Manager'h

Disadvantage is that such constants won't be visible when looking at formulas in other sheets. That's why Apple's Numbers spreadsheet allows for multiple tables in a sheet.

Excel's solution for that problem is he table (http://office.microsoft.com/en-001/excel-help/overview-of-ex...) if you are willing to transpose rows and columns, theta might come close to what you want.

[ Parent | on HN ]

Re:Ask HN: What would Excel's formula language look like if ... by dragonwriter on Monday December 02, 2013 @12:23PM (#6834966)

> I was helping my bartender friend with a spreadsheet for his per-bottle profits, and it would have been so much simpler if we could have named the columns after each liquor, and the rows for their wholesale-cost, serving-size, bottle-size, serving-charge, etc.

You can do that with Excel 2007+ with tables, which are probably one of the best improvements in Excel in a long time, and underused because so many Excel users have spent so much time learning how to work without them they failed to notice that it was no longer necessary, and that the most common Excel use cases had just gotten much easier.

[ Parent | on HN ]