Ask HN: What would Excel's formula language look like if you invented it today?
26 comments
Honestly, I think the bones of Excel's language are fine. It's very natural for anybody who knows highschool math. There are obviously a few features I'd want redesigned - like the inline-if stuff, and a good lambda syntax would be nice. Better Boolean support would be a big thing.
Basically I'd want something like C# or Python.
The big thing is that Excel is for non-coders, so you can't use programmer-centric syntax. That means that C's ternary operators, double-equal-signs equality test, and the ampersand/pipe boolean logic is right out.
On the other hand, I like curly braces, and the "BEGIN" "END" keywords of wordy languages seem more confusing than simple braces. Or indent-based since the environment can be trusted to enforce them and make them neat. But curly-braces are good for one-liners, which are important to Excel.
.NET support would also be a huge plus.
Oh, I just designed Boo.
Yeah, Boo (or something very close to it) would be my language for Excel. Except for the silly double-equality thing, screw that, since most variable declarations will be a cell anyways so the assignment operator isn't too useful for Excel and you can make assignment verbose with a "let" keyword.
My biggest problem with Excel isn't the formula language but the muddy-thinking nature of spreadsheets themselves. I'd rather see a middle-ground between a database and a spreadsheet, something like Lotus Improv.
Make the user specifically define the names of their variables they're using and whether they're a scalar, an array, or a 2d array. Make the formulas visible above these things.
Basically I'd want something like C# or Python.
The big thing is that Excel is for non-coders, so you can't use programmer-centric syntax. That means that C's ternary operators, double-equal-signs equality test, and the ampersand/pipe boolean logic is right out.
On the other hand, I like curly braces, and the "BEGIN" "END" keywords of wordy languages seem more confusing than simple braces. Or indent-based since the environment can be trusted to enforce them and make them neat. But curly-braces are good for one-liners, which are important to Excel.
.NET support would also be a huge plus.
Oh, I just designed Boo.
Yeah, Boo (or something very close to it) would be my language for Excel. Except for the silly double-equality thing, screw that, since most variable declarations will be a cell anyways so the assignment operator isn't too useful for Excel and you can make assignment verbose with a "let" keyword.
My biggest problem with Excel isn't the formula language but the muddy-thinking nature of spreadsheets themselves. I'd rather see a middle-ground between a database and a spreadsheet, something like Lotus Improv.
Make the user specifically define the names of their variables they're using and whether they're a scalar, an array, or a 2d array. Make the formulas visible above these things.
I think localizing it was a big mistake.
I don't even want to know if this is fixed now, but I am fairly certain that stuff written in German Excel didn't work in English installations.
=SUM() vs
=SUMME()
I think the current Excel formula language is perfect for what it does. 99.999% excel users don't really need anything more than sum or sumif.
I work extensively in Excel and we have an in-house programming language that can be run with an eval() type excel function. The code is written directly in the cells and you just have to eval() it to run it. It's brilliant, let's you do all you want without breaking excel in any way. The syntax is a pretty standard C variant with a lot of support for matrices (finance work).
I work extensively in Excel and we have an in-house programming language that can be run with an eval() type excel function. The code is written directly in the cells and you just have to eval() it to run it. It's brilliant, let's you do all you want without breaking excel in any way. The syntax is a pretty standard C variant with a lot of support for matrices (finance work).
A 2013 spreadsheet formula language would look pretty similar to what we see now. The format is simple and relatively easy for people to understand.
If I were able to change it, I may consider adding a left hand side to the formulas (A3=A1+A2 rather than just =A1+A2), which seems verbose but completely obviates the need for the special array formula type (e.g. `{=A1:A2 * B1:B2}`, which would be naturally expressed as C1:C2=A1:A2 * B1:B2). There are other constructions like range unions and intersections that I probably would change, but no obvious replacement comes to mind.
If you want to explore JS in excel formulae further, there are people trying to implement the formula space in JS: https://github.com/sutoiku/formula.js
BTW: if your goal is to attack the spreadsheet space by innovating on formulas, you are probably barking up the wrong tree.
If I were able to change it, I may consider adding a left hand side to the formulas (A3=A1+A2 rather than just =A1+A2), which seems verbose but completely obviates the need for the special array formula type (e.g. `{=A1:A2 * B1:B2}`, which would be naturally expressed as C1:C2=A1:A2 * B1:B2). There are other constructions like range unions and intersections that I probably would change, but no obvious replacement comes to mind.
If you want to explore JS in excel formulae further, there are people trying to implement the formula space in JS: https://github.com/sutoiku/formula.js
BTW: if your goal is to attack the spreadsheet space by innovating on formulas, you are probably barking up the wrong tree.
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.
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.
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.
What would help is some sort of object-oriented programming with the named cells as opposed to the named cells behaving like global variables.
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.
You'd probably need a way to override it to allow certain individual cells to have a unique name, though - constants if you will.
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.
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.
> 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.
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.
Scheme is easy to embed and maps well to common spreadsheet operations because it accepts lists of arguments to functions like sum and stdev. It also has a full numeric stack. Modify it to accept ranges of cells and it would be an excellent language for Excel.
that's awesome, looks like the Emacs of spreadsheets
Emacs has Org-Mode, which has auto-updating tables [1], which would be the spreadsheet of Emacs.
Here's a demo mileage tracker I did in Org-Mode [2].
- http://orgmode.org/guide/Tables.html#Tables - https://vimeo.com/71364504
Here's a demo mileage tracker I did in Org-Mode [2].
- http://orgmode.org/guide/Tables.html#Tables - https://vimeo.com/71364504
Python.
I actually want to use Python in a spreadsheet program but it's more difficult than it should be, DataNitro is not free and Gnumeric's Python support is not enabled in Windows builds. Pyspread doesn't work quite like a regular spreadsheet program.
I actually want to use Python in a spreadsheet program but it's more difficult than it should be, DataNitro is not free and Gnumeric's Python support is not enabled in Windows builds. Pyspread doesn't work quite like a regular spreadsheet program.
I remember resolver one doing exactly this but the main problem was that it "wasn't Excel" http://www.resolversystems.com/products/resolver-one/power.p...
Have you taken a look at http://www.pyxll.com/?
What difference to regular spreadsheets makes Pyspread unusable for you?
Not sure the language design can really be considered (totally) independently of the UI design. Spreadsheets are not text editors, and what is good for the goose may poison the gander and render it dead.
I wouldn't change a lot, perhaps nothing.
VBA is a fuzzy line; a lot of functionality is added through plugins from Bloomberg / Datastream / Reauters (I live in the financial world) through C++ libraries for all kinds of functions.
Integrity of data and calculation... Best not to use Excel for critical, non audited systems. Changing the language will not change the potential to use a dodgy clockwork clock (spreadsheet) to do the job of a digital watch.
I do deeply dislike the visualisation of data. Formatting a chart is simple but tedious. If venturing from bar / line / area / scatter charts i.e. more than an extremely linear presentation of data, then Excel does not excel.
But a vlookup, or a pivot, at the stroke of a few characters, is amazing. Given the >65535 line restriction of earlier versions of excel, it would be very nice these functions could function on larger data groups, the hundreds of thousands of lines Excel now represents in rows.
VBA is a fuzzy line; a lot of functionality is added through plugins from Bloomberg / Datastream / Reauters (I live in the financial world) through C++ libraries for all kinds of functions.
Integrity of data and calculation... Best not to use Excel for critical, non audited systems. Changing the language will not change the potential to use a dodgy clockwork clock (spreadsheet) to do the job of a digital watch.
I do deeply dislike the visualisation of data. Formatting a chart is simple but tedious. If venturing from bar / line / area / scatter charts i.e. more than an extremely linear presentation of data, then Excel does not excel.
But a vlookup, or a pivot, at the stroke of a few characters, is amazing. Given the >65535 line restriction of earlier versions of excel, it would be very nice these functions could function on larger data groups, the hundreds of thousands of lines Excel now represents in rows.
pow(A2,2)
Please:
pow = Math.pow.bind(Math)
Not sure if JS is the best choice for a spreadsheet, though, JS has surprising (compared to Python or Ruby) IEEE 754 arithmetic. I don't know that much about the topic so perhaps someone else could elucidate.
Most of the trouble I've had with spreadsheets boils down to not knowing the (albeit small) language. Writing =B3/B$1*$A$1 is generally useful, don't get rid of that. Emphasize the formatting as a separate concept from the data (formatting needs its own language). Add type hinting/checking to cells & functions, but don't 'enforce'. Make plots first-class, embeddable in single cells (and mergable into larger areas). Make functions first-class, so a cell can contain a formula. Allow simple access to not just other sheets' cells/fns, but other files' cells. If you're crazy, allow access to remote files' cells/fn over HTTP. If you can define a sane JS FFI, you could import js libraries remotely. It blurs into web development, but what doesn't?
I think it'd be neat to get some flowback from the FRP/Cells community which seems to be reinventing spreadsheet computation from first principles.
$(1,1) = $(1,2) + $(1,3); // cell value ref: $(row, col)
$('sheet1').onready(function() {
}); // you get the picture
replace fill handle based formula extension with parameterized formulae:
for (int i = 0; i < $('name_range').length(); i++)
$(1,i) = $(2,i) + $(3,i); // and so on and so forth
As a simple experiment or learning experience, why not start with the same languages as Light Table, then add R and Julia. (or start with Light Table and add a spreadsheet view)
But if you wanted to raise the bar on spreadsheets in general, you might back away from the scripting end and think about sets, series, logic, and ontologies. Look at CLIPS and Prolog, and try out Protégé.
Also, "Populous is a generic tool for building ontologies from simple spreadsheet like templates." (http://www.e-lico.eu/populous.html)
I would personally enjoy being able to define named streams with Redis-like pub/sub semantics driven by arbitrary generators, composeable as sets (when finite, or as constrained by rules), and having a parametric type system available when it makes sense. Then scripting.
But if you wanted to raise the bar on spreadsheets in general, you might back away from the scripting end and think about sets, series, logic, and ontologies. Look at CLIPS and Prolog, and try out Protégé.
Also, "Populous is a generic tool for building ontologies from simple spreadsheet like templates." (http://www.e-lico.eu/populous.html)
I would personally enjoy being able to define named streams with Redis-like pub/sub semantics driven by arbitrary generators, composeable as sets (when finite, or as constrained by rules), and having a parametric type system available when it makes sense. Then scripting.
> Google Docs clones the Excel formula language for use in cells but allows you to write extensions/macros (where you would use VBA) in Google Apps Script but what is it really? It looks a lot like JavaScript.
It doesn't just "look a lot like JavaScript", it -- to quote Google -- "is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications." [1]
[1] https://developers.google.com/apps-script/
> Why not have a single language for the cells and for extensions?
Because programming in the extremely small is different than programming in the large.
With a suitable functional programming language, you could probably find something that would work tolerably well for both.
It doesn't just "look a lot like JavaScript", it -- to quote Google -- "is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications." [1]
[1] https://developers.google.com/apps-script/
> Why not have a single language for the cells and for extensions?
Because programming in the extremely small is different than programming in the large.
With a suitable functional programming language, you could probably find something that would work tolerably well for both.
I would base it on Javascript just to give certain people something else to whine about.
for what excel is intended to do - the current formula language is actually perfect - I wouldnt trade it for anything else (being someone who does a lot of work in excel - but also spends plenty of time hacking python & js) - if you really want to do more powerful stuff - you have macros and the like. Theres also Datanitro and other plugins you can add in. Also - if you really just care about using the data in the sheet - just read it into a simple python script and manipulate away. Dont fix something that isn't broken, I say.
Wow, never heard of datanitro, thanks for the tip. Something like this but in ruby?
but what if excel didn't exist, and you were writing it today... would you invent something more like formula language or something like Forth?
As a few others have said, the current language is suitable for the original intended purpose, although a better way of managing if..then..else situations would be appreciated.
If anything, I'd take away some of the non-core mathematical procedures and functions (ie: cell prettifiers) so that people stop using spreadsheets for such diverse purposes as project management (GANTT in spereadsheet columns - ugh!), tabular text reports that should be done in a word processor and anything else where another app would be more suitable.
If anything, I'd take away some of the non-core mathematical procedures and functions (ie: cell prettifiers) so that people stop using spreadsheets for such diverse purposes as project management (GANTT in spereadsheet columns - ugh!), tabular text reports that should be done in a word processor and anything else where another app would be more suitable.
I think Excel language is great: It's fantastic that so many people in the world are unwittingly gaining experience in using a pure functional language, and its conciseness really lends itself to writing in relatively small cells. There are no side effects, it's hard to shoot yourself in the foot.
I do find myself writing fold-like functions by creating a column to hold the accumulator; whereas it might be nice to have that syntax built-in.
It's an intuitive functional language. Well played.
I do find myself writing fold-like functions by creating a column to hold the accumulator; whereas it might be nice to have that syntax built-in.
It's an intuitive functional language. Well played.
I think that I wouldn't build spreadsheets today. I'd build a tool which allows me to build simple forms which store, edit, and view data, and a separate tool which lets me work on and transform that dataset through a visual programming pipeline.
This would allow me to decouple the storage format, the views, and the transformations from each other, and make for a significantly more maintainable system for most things that spreadsheets are commonly used for.
This would allow me to decouple the storage format, the views, and the transformations from each other, and make for a significantly more maintainable system for most things that spreadsheets are commonly used for.
"Make everything as simple as possible, but not simpler." - Albert Einstein
http://en.wikipedia.org/wiki/Reverse_Polish_notation
http://old.easyco.com/easydocs/d3/pickref/page1517.htm
(50 year old methods that worked well on the technology of the day would fly today.)
http://en.wikipedia.org/wiki/Reverse_Polish_notation
http://old.easyco.com/easydocs/d3/pickref/page1517.htm
(50 year old methods that worked well on the technology of the day would fly today.)
Today with the politics at MS? I imagine it would be similar to powershell's syntax or just be powershell with new excel specific functions. I imagine there would be more wizards to help non-techies generate code. Maybe even natural language stuff. "I want to add up everything in column B." or "I want to append http:// to everything in column C."
Something like Haskell seems like a good fit for the brevity of working with sequences using higher order functions, but maybe the type system is too verbose... I'm sure there's some better candidates, but it'd be a nice start and maybe an excuse to look at the work of that one person who turned a Haskell dialect that compiles to javascript into a fulltime job.
Firstly : cells + formulas are isomorphic to rule-based (expert) systems in that both are directed graphs (which may contain cycles, which is an error!).
Thus, just a thought experiment, it may be useful to turn the situation on it's head and say / ask : "So, this GUI, with formulas and cell references, is actually a way to visualize a rule-based system..."
Thus, just a thought experiment, it may be useful to turn the situation on it's head and say / ask : "So, this GUI, with formulas and cell references, is actually a way to visualize a rule-based system..."
Build web applications, with formulas. Formulas that build real software, the kind a programmer is able to create. This way: www.cellmaster.com.au/Build.html?hn (2 min video)
JavaScript or a language that improves upon it like CoffeeScript, TypeScript, or Dart.
> If you were to invent a language to be used in spreadsheets today, what would it look like?
Lisp. I'd have the cells contain data, and only data. You can type data into the cells like normal. However, when you want to input a formula I'd split the screen horizontally and have it start off with
(cell-name ( ____ ))
Have the user start typing in the middle of the second parenthesis where the underscore is by default - have it default selected to make it clear, slightly grey out the surrounding syntax so they don't immediately have to worry about it but bring it back to the normal colour if they click outside of that.
Clicking a cell back on the cells windows would insert a reference to it.
(cell-name (+ a1))
Dragging across cells would define a group with some syntactic sugar.
(cell-name (+ group a1 b2))
Inserting a graph would show you the code of the graph right there in the code window.
What might I do differently to Lisp? I'd replace the + sign with 'sum-of' and * with 'product-of' to help people get used to the prefix style. So the above would become.
(cell-name (sum-of group a1 b2))
I'd have sum-of inserted when you clicked the + key on your keyboard to make it clearer how it worked.
I think this would be quite intuitive and add a lot of easily seen power. We're used to using parenthesis to group math things, we're used to using variables, we're used to using functions - we just don't tend to think of those things as such.
All your logic would be visible in the code window, finally escaping the horrible black-box style of clicking off the cell and everything vanishing.
Potential problems?
'Ah, but Excel's for non-coders,' you might say. I don't think it is, not really. If you just want to add a range together you have to learn a formula that's quite similar to Lisp to begin with:
=SUM(a1:b2)
And in many ways it's less intuitive than Lisp is. Why is =SUM required to be like that, why do you need to group the references after SUM like that? What is the : meant to signify? And as you go develop more complex things like SUM-IF you're rapidly looking at something that's just a bad programming language.
You can sort of muddle your way into it by messing around - but I don't see that it's not a thing that people have to learn or that it's any easier than Lisp's presentation would be. Have you tried interviewing for admin jobs lately? I have, I try interviewing for jobs I'm not interested in from time to time, and even working some of them, just to keep in contact with the state of offices. Pivot tables and 'macros' are considered advanced excel knowledge - I've been in offices where most of the people working there don't know how to make a drop-down list for a cell, or how to use something as simple as SUM-IF.
Excel's language isn't simple for non-coders already. Where it wins above programming languages that we all are perhaps more familiar with, IMO, is that it gives people an immediate visual hook into a data structure that they can use as a starting point.
Like the REPL - but with a slightly different emphasis.
Lisp. I'd have the cells contain data, and only data. You can type data into the cells like normal. However, when you want to input a formula I'd split the screen horizontally and have it start off with
(cell-name ( ____ ))
Have the user start typing in the middle of the second parenthesis where the underscore is by default - have it default selected to make it clear, slightly grey out the surrounding syntax so they don't immediately have to worry about it but bring it back to the normal colour if they click outside of that.
Clicking a cell back on the cells windows would insert a reference to it.
(cell-name (+ a1))
Dragging across cells would define a group with some syntactic sugar.
(cell-name (+ group a1 b2))
Inserting a graph would show you the code of the graph right there in the code window.
What might I do differently to Lisp? I'd replace the + sign with 'sum-of' and * with 'product-of' to help people get used to the prefix style. So the above would become.
(cell-name (sum-of group a1 b2))
I'd have sum-of inserted when you clicked the + key on your keyboard to make it clearer how it worked.
I think this would be quite intuitive and add a lot of easily seen power. We're used to using parenthesis to group math things, we're used to using variables, we're used to using functions - we just don't tend to think of those things as such.
All your logic would be visible in the code window, finally escaping the horrible black-box style of clicking off the cell and everything vanishing.
Potential problems?
'Ah, but Excel's for non-coders,' you might say. I don't think it is, not really. If you just want to add a range together you have to learn a formula that's quite similar to Lisp to begin with:
=SUM(a1:b2)
And in many ways it's less intuitive than Lisp is. Why is =SUM required to be like that, why do you need to group the references after SUM like that? What is the : meant to signify? And as you go develop more complex things like SUM-IF you're rapidly looking at something that's just a bad programming language.
You can sort of muddle your way into it by messing around - but I don't see that it's not a thing that people have to learn or that it's any easier than Lisp's presentation would be. Have you tried interviewing for admin jobs lately? I have, I try interviewing for jobs I'm not interested in from time to time, and even working some of them, just to keep in contact with the state of offices. Pivot tables and 'macros' are considered advanced excel knowledge - I've been in offices where most of the people working there don't know how to make a drop-down list for a cell, or how to use something as simple as SUM-IF.
Excel's language isn't simple for non-coders already. Where it wins above programming languages that we all are perhaps more familiar with, IMO, is that it gives people an immediate visual hook into a data structure that they can use as a starting point.
Like the REPL - but with a slightly different emphasis.
Would you just jump on the JavaScript bandwagon?
Google Docs clones the Excel formula language for use in cells but allows you to write extensions/macros (where you would use VBA) in Google Apps Script but what is it really? It looks a lot like JavaScript. The documentation totally sucks, it lives in the Google Apps Script IDE, the debugging is terrible. It can't easily be shared or put on github and it's hard to test.
Why not have a single language for the cells and for extensions? I'm thinking real JavaScript would be interesting, as in is being able to import external scripts and exporting their globals into the spreadsheet's namespace then being able to set a cell to apply the function and display the output of that function.
So you could set the value of a cell to `=function(i){Math.pow(i,2)}(A2)` this would apply JavaScript's `Math.pow(_,2)` to the value of the cell `A2` or you could let imported functions work directly on the spreadsheet cells and do something like `=Math.pow(A2,2)`
This doesn't solve the problem of JavaScript being a terrible programming language and after re-reading the above, is asking users to write a closure around multiple-argument functions crazy, is polluting the spreadsheet namespace with external functions too crazy? What if a third party JavaScript lib has a function called A2, or even SUM which conflicted with the builtins Perhaps a stack-based programming language would be more appropriate, especially when you start trying to do operations on ranges (lists).
The first thing that comes to mind is Forth but a more modern alternative that plays well with JavaScript is Daimio http://daimio.org/.