
Ask HN: What would Excel's formula language look like if you invented it today? - r4vik
If you were to invent a language to be used in spreadsheets today, what would it look like?<p>Would you just jump on the JavaScript bandwagon?<p>Google Docs clones the Excel formula language for use in cells but allows you to write 
extensions&#x2F;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&#x27;t easily be shared or put on github and it&#x27;s hard to test.<p>Why not have a single language for the cells and for extensions? I&#x27;m thinking real JavaScript would be interesting, as in is being able to import 
external scripts and exporting their globals into the spreadsheet&#x27;s namespace then being able to
set a cell to apply the function and display the output of that
function.<p>So you could set the value of a cell to `=function(i){Math.pow(i,2)}(A2)` this would
apply JavaScript&#x27;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)`<p>This doesn&#x27;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).<p>The first thing that comes to mind is Forth but a more modern
alternative that plays well with JavaScript is Daimio http:&#x2F;&#x2F;daimio.org&#x2F;.
======
Pxtl
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.

~~~
wink
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()

------
xmonkee
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).

~~~
lukeholder
open source?

~~~
xmonkee
The in-house language isn't open source, no.

------
sheetjs
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](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.

------
mkl
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.

~~~
bashinator
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.

~~~
Someone
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...](http://office.microsoft.com/en-001/excel-help/overview-of-excel-tables-
HA010048546.aspx)) if you are willing to transpose rows and columns, theta
might come close to what you want.

------
patrickmay
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.

~~~
oftenwrong
[http://siag.nu/siag/](http://siag.nu/siag/)

~~~
r4vik
that's awesome, looks like the Emacs of spreadsheets

~~~
traviscj
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](http://orgmode.org/guide/Tables.html#Tables)
\- [https://vimeo.com/71364504](https://vimeo.com/71364504)

------
noinsight
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.

~~~
r4vik
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...](http://www.resolversystems.com/products/resolver-
one/power.php)

------
w_t_payne
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.

------
zhte415
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.

------
nailer

        pow(A2,2)
    

seems reasonable.

Please:

    
    
        pow = Math.pow.bind(Math) 
    

at least, as typing Math in a spreadsheet is going to get boring.

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.

------
arh68
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?

------
tel
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.

------
merkitt

      $(1,1) = $(1,2) + $(1,3); // cell value ref: $(row, col)
    
      $('sheet1').onready(function() {
      }); // you get the picture
    

the spreadsheet programming and the data grid/presentation could be made
separate.

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
    

write as many functions as necessary.

------
rch
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](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.

------
dragonwriter
> 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/](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.

------
talmand
I would base it on Javascript just to give certain people something else to
whine about.

------
kamakazizuru
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.

~~~
lukeholder
Wow, never heard of datanitro, thanks for the tip. Something like this but in
ruby?

------
linker3000
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.

------
mrtimuk
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.

------
vertex-four
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.

------
edw519
"Make everything as simple as possible, but not simpler." \- Albert Einstein

[http://en.wikipedia.org/wiki/Reverse_Polish_notation](http://en.wikipedia.org/wiki/Reverse_Polish_notation)

[http://old.easyco.com/easydocs/d3/pickref/page1517.htm](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.)

------
drzaiusapelord
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://](http://) to everything in column C."

------
ben0x539
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.

------
vijucat
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..."

------
eddyparkinson
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)

------
applecore
JavaScript or a language that improves upon it like CoffeeScript, TypeScript,
or Dart.

------
6d0debc071
> 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.

