Pure Functional Programming in Excel

Felienne Hermans

Recorded at GOTO 2016


Get notified about Felienne Hermans

Sign up to a email when Felienne Hermans publishes a new video

hello everyone my name is Flint animals
I'm assistant professor at Delft
University of Technology I'm on Twitter
as well so if you want to rape me not
through the app but by sending a tweet
then feel free to do so
so maybe you're wondering at a
Developers Conference why we are talking
about spreadsheets so my view on this is
spreadsheets are definitely mislabeled
many people think of spreadsheets as
being data but spreadsheets are not data
spreadsheets are code this is the gospel
of my life spreadsheets are gold I go to
all sorts of conferences to share the
happy word that spreadsheets are code so
if you want to leave now or fall to
sleep or do some email that's fine
because this is already the most
important message of my talk right there
right cheats are good so you might
wonder why well I have three reasons for
this the first reason that spreadsheets
are code is they're used for similar
problems as you see here this is an
investment calculation where you put in
some numbers and an investment advice
comes out you could make this in any
programming language I mean you could do
it in and C or an objective-c if you
want an iPad app in Java or in a
spreadsheet it doesn't really matter
there's nothing special about this
problem that makes it spreadsheet
specific of course you could wonder why
do people do this in a spreadsheet I
have asked people this so during my PhD
dissertation I've worked with several
banks and investment companies in the
Netherlands and I asked them like why
are you building this why aren't you you
know asking your IT provider for some
software you know why because we are bad
at making software seriously they have
tried this they've went to their IT
provider either internally or externally
and they say look I've I've made this
spreadsheet and it's sort of okay it's
like a prototype can you can you build
me some so far for that and the IT
provider goes like yeah sure no problem
that will be at six months and a million
euros and if that were only true it
wouldn't be that bad but it's never six
months in a million euros is nine months
and it's three million euros and in
has half the features they want so
end-users perceive themselves as better
programmers than us and I mean they're
not entirely wrong in that because they
can quickly build prototypes to do what
they want so I go to great lengths to
make my points to such great lengths in
fact that I have built a Turing machine
in Excel formulas to show that
spreadsheet formulas are Turing complete
so no more saying please that
spreadsheet are not a real programming
language because this is a Turing
machine every row by the way is one
iteration of the tape and with
conditional formatting I'm moving the
head over the tape so it's also I think
a nice visualization of a Turing machine
you can easily see what's going on you
see the history stored in the rows as
well instead of just a head moving over
a real tape and many people and enjoy
this lots it sort of went viral on the
internet and it brought my personal blog
down for half a day because it was on
Hacker News and already so the third
reason that spreadsheet should
absolutely be treated as pieces of
source code is this suffer from typical
software engineering problems only one
entry spreadsheets has a manual an
average spreadsheet is used by 12
different people over life span of five
years does that sound familiar that is
exactly the type of problems that
software is suffering from and
especially was suffering from let's say
30 or 20 years ago in those days
everyone was an end user there were no
so friend hearing degrees yet there were
no tools everyone was just building
their own stuff and then so for
engineering software suffers from those
problems as well and spreadsheets still
do today so in summary the activities
the complexity and the problems
surrounding spreadsheets are the same as
software's or spreadsheets are code
there's no denying it anymore but I'm
going to go for it on that it's not just
a programming language Excel actually is
the next programming language you should
learn why the resistance is
why first of all spreadsheets are live
programming who knows who made this is
anyone familiar with this picture yeah
who made it you know that's okay
so it's made by a guy called bread
Viktor and what he proposes here is the
idea of life programming so on the left
side you have the instantiation of the
source code that you see on the right so
if you change something here a number or
color or an integer the tree will change
immediately so there's no compiling
involved it live update and he go into
this from live programming well that's
awesome bread Victor where we had that
since VisiCalc
I mean a spreadsheet is live programming
you change your formula and
automatically everything updates there's
no compilation involved there either so
functional programming is sort of like
back from from the almost dead it's
getting more and more mainstream so you
know what else is pure functional
programming excel is pure functional
divided by definition side-effect free
the only thing a formula can do is take
input from different formulas and
produce a value a formula cannot change
the results of an order formula or
another cell so there are no side
effects isn't that fantastic thirdly
everyone knows spreadsheets people
is managing his soccer team's finances
in a spreadsheet your accountant is
probably doing your taxes in a
spreadsheet
everyone knows Fred sheets except us
developers isn't that sort of sad I I
really like to say that spreadsheets are
the most successful programming language
in the history of mankind it's such a
good programming language that people
don't even know their programming isn't
that fantastic I mean imagine doing Java
by accident that will never happen when
people are programming in a spreadsheet
without even knowing their programming
how successful is that so I'm going to
assume now that you're convinced that
spreadsheets are good and that you're
also good
the spreadsheets are pretty cool so let
me show you some typical programming
ideas implemented in a spreadsheet to
show you how super awesome they are
let's so Uncle Bob says that functional
programming is programming without
assignment statements so let's see how
that looks like in the spreadsheet we
are going to take a typical example
we're going to square some integers and
let's take it easy I know it's late in
the day so I have a number of values
here and what I want is the square so
that is still pretty easy what I can do
is I just multiply the number with
itself now I can drag that down or
probably you know this you can drag a
formula down and then it automatically
updates the location you can also double
click there and then it goes
automatically so what we have is a list
of inputs and we have a function
definition this is just defining a
function over an input it's like a map
we're mapping the function over all the
inputs but now you're going to say as
developers and this isn't pretty because
all those formulas look slightly
different but I can fix that I can give
those that first column a name I'm going
to call that input and what I can do now
is I can refer to that names range in
this way I'm dragging this input down
and what happens now is every value in
this range gets the value that's the
intersection of this row and the named
range so I can delete that and what I
can do now there instead of referring
directly to the formula I can say inputs
times inputs isn't that nice and now it
looks like more proper programming at
school huh it's the same everywhere so
it's really like a function and you can
do that without having the ugly
spreadsheet like a 2 and a 3 so let's do
something a little bit more exciting
because this is still quite easy so what
we want what we would like to have is
the sum of all squares that are over 30
so I can make a simple boolean I'm going
to say which of those values is bigger
than 30 that's still you know super easy
and see we have square here so again
here everywhere we have a nice
consistent formula no a 3s and a 7s just
a nice formula we can use this sum F now
to sum all of the values in the squares
over 30 it's sort of nice in that zoom
if over 30 if this is true then I'm
swimming the square value but it takes
it takes a lot of real estate there's a
lot of room I need for all those extra
squares and extra boolean's so what do
we want to do is something a little bit
more efficient what I would like to do
is I would like to multiply those two
ranges directly so multiply the squares
with the boolean that internally is
re-written to 0 or 1 so you can multiply
with it what I could do is multiply
input by input but that's not it because
what I showed you earlier is if you have
a range if you refer to the range you
get the intersection of the row and that
range so that's not what we want
but it is close what we can do is use
control shift and then Excel makes this
into an array function and that means we
can calculate with ranges or arrays as
first-class citizens so if I just type
the same formula and hit control shift
enter I get this and I got a different
value C without curlies it's this and
with Curly's it's this what's happening
here is this entire input range is
multiplied by an entire second input
range and the result of those two ranges
because it's pairwise multiplication is
another range so what we're seeing here
is the first item of the results of this
range multiplied by itself and if I
would give it a little bit of space I
going to show you I think the first five
elements so I'm copying this formula now
here and then we get the first five
elements of the array so now you can see
that that array formula action
the results in a real second array of
which I'm showing a few values what I
can do then is I can reduce that array
so I have all the values now I could sum
it so I could do this
the sum of this range multiplied by
itself so in just one formula we don't
need all the extra cells just in one
formula I'm multiplying the range pair
wise with itself and I'm swimming
everything and then I can also put in
the filter I can multiply input by input
and then multiply it by whether or not
that is bigger than 30 and then I get
the same value here but I could delete
all those extra cells I don't need them
because everything is reduced so it's
pretty efficient if you know about all
these special functions like array
formulas so something else I want to
talk about is how error values work in
Excel probably you've never really
thought about it because they are so
intuitive and nice but there's something
interesting going on there so suppose
again you have a number of inputs and
I'm going to see this with some random
data between 0 and 3 so sometimes we get
zeros there suppose I would want to
divide by that random data column and
I'm going to call that the danger zone
because it's really very dangerous
danger dividing by 0 Excel warns us
Excel gives an error and say watch out
this is an error so think about this for
a minute what is the data type if we're
saying this is an integer what is the
data type of that range who's a
functional programmer here yes
look at this guy's it's a monads in
Excel it's a maybe int this is a
700-million Excel users they just use
monads without any problems they don't
even think about it
because if we have another column that
uses this may
baby ends as an input it just works look
we have Veera calculate some more column
that just takes the maybe int and works
with it
without any problem so this plus here is
lifted and just apply it to the ends so
we go from a plus that works on integers
seamlessly to a plus that works on maybe
an integer or an error value isn't that
awesome I think it's pretty cool so
let's do some more real programming
because now you're saying yeah but a
little bit of filtering a little bit of
swimming that's that's not something
that I'm really going to use let's
tackle a bit more exciting problem so
what we have here is a maze and it's
filled with money so I want to have a
walk through this maze that gives me
most money I'm a poor academic so I need
to get my money from programming
examples that are set in a maze so what
I could do of course would just work is
going to start here so this is the
beginning of my maze but it's easier to
start at the end and work our way back
so what we can do is we can say the
value then what we can achieve most but
the most money is that we can get from
our maze there are two options either I
came from that way or I came from that
way that's that's the only two options
that could have happened if I have
reached the end of the maze so I'm
simply going to take the maximum of
those two so the maximum that I could
achieve here or the maximum that I could
paths through the maze so this this is a
solution that works however there is a
problem with this solution does anyone
know what our problem is so it's not
very efficient yes it's not very
efficient it's not really solve it so
but it's not really solved because for
example this path through the maze every
recursive goal you would try all the
paths next
course of goal so if you're here you try
all the pads and then you're again
you're here and you try all the pads so
it works but it's very expensive because
you keep exploring the entire maze so
what we could use here is a technique
called dynamic programming and what
dynamic programming does is it saves
half states of the problem in order to
be more efficient you don't have to
calculate everything you calculate
subproblems you calculate the whole
problem from subproblem so here is our
maze and we're going to have an extra
worksheet called max and what we're
saving there is the maximum value at
that square in the maze so those are
easy this one and that one there's only
one way you can reach that square the
only way to get there is like that
because we're only moving that way so
those are easy but the rest of the maze
what I want to do there is I want to
look at what is the best way I could
have achieved this sub-problem so far so
I'm taking the maximum of those two
steps before me plus whatever is in this
field of the maze so either I came from
that way or it came from that way plus
whatever was already there now I have
the same formula for all those squares
in the maze and I have my answer that's
it that's the whole solution and if you
compare that to this is a very famous
problem in computer science with the
solution in C or even in high school it
really looks very easy in a spreadsheet
this is the only formula you need for
the entire maze and you have your answer
however it's a bit cumbersome because
now I have the value but what I also of
course would want to know is what is the
path through the maze how am I supposed
to walk in order to obtain all that
money and of course I could find it out
because every time I could look what is
the best of those two and what is the
best of those two and I could retrace my
steps but that's too much work I'd
rather have Excel do the thinking for me
then
I do it myself so what I'm going to do
is instead of printing the values I'm
going to print the pads with arrow like
symbols and those error like symbols are
going to indicate how I'm walking
through the maze so here is relatively
easy because again if we're on the
border of the maze there chose one way
we could go up and in this case left
those are the only options we have
because we're only walking now we're
walking back so we're only walking up
then in this case if I want to detect
where I was coming from in my optimal
solution I can just say well I'm going
to point at a maximum if the maximum is
above me I'm going to print this and if
the maximum is next to me I'm going to
print that and now we have sort of a
vector field of our maze Lutz looks
interesting and I could say it's solved
because I can just follow the arrows now
I could go like this to two that way and
then that way that's that's how we're
going but still it's too much thinking
so let's just tweak the formula a little
bit such that the solution looks
somewhat nicer I'm only going to print
something if the cell is being pointed
at only if I'm really in the fat I'm
printing and otherwise I just brands and
at the antis tree because that will look
very pretty so there we go if the value
below me is this if I mean points it at
from the bottom then I'm printing
something and otherwise I'm printing the
empty string still sort of an easy for
malonic it looks nice but we're only
going one way now you see we're only
here is now stalled so we need one more
thing because now we're only pointing to
that from below we also want to print if
we're point to that from the sides
almost the same we just add this
we're just saying if the value next to
us is that arrow we're printing as well
and otherwise it's the empty string and
then we get this at all
that's our bad our money-making fads
true the whole maze and it's really a
very easy formula it's just if I'm being
pointed at I'm going to print the
maximum and otherwise I print the empty
string and the nice added value that you
see now the power of live programming
and having the data and the formulas
together is that every step of the way
it was very clear what was happening now
I'm printing values now I'm printing
strings you're very in touch with your
data whereas if you're doing this by
reading from a text file probably you're
using pen pen and paper to track okay
what am i printing what am i doing
there's print statements everywhere or
you're using the debugger you have to
spend lots of efforts to remain in touch
with your data hours here the day eyes
is right there it's screaming at you
like use me
give me some formulas and I will tell
you what I'm up to and that makes
spreadsheet programming really very very
easy and intuitive
so in summary spreadsheets are good if
everyone said anyone close to you said
it's just a spreadsheet hit them on the
head with something really tell them
it's untrue spreadsheets are real
programming and it's super cool so of
course this is sort of the the main idea
behind all the research I'm doing at
Delft University of Technology is if
spreadsheets are code then maybe they
need some software engineering maybe we
as software engineers know how to
support people that are building
spreadsheets because in software as I
said in the beginning of my talk thirty
years ago 25 years ago everyone was an
end-user programming everyone was
building their own compiler and then 10
years later people were like oh yeah no
the program is so burning probably we've
lost a compiler and also the person that
built it isn't working here anymore
died and no either so we saw those
problems and we realize as community we
need to improve we need to build some
tools to help people maintain
in legacy code so if you look at a
modern ie while this screenshot isn't
all that modern anymore but if you look
at a IDE you see it at all as all sorts
of features like debugging and testing
and analyzing those features are not
helping me to build codes to write codes
they're helping me to write responsible
code to be a good developer I'm going to
test my code I'm going to analyze code
metrics from now now and then it's
helping me to be a good developer then
if I look at Excel yeah it has support
for making things to different phones or
a different color it has no support for
anything no IDE lying features helping
spreadsheet developers to build good
spreadsheets so whether they have the
first version of my of the first example
I showed you with a two times a three or
they use nice names for everything no
one at least not the interface is
telling them maybe you shouldn't you
shouldn't go everything a five maybe
should give some names here and there so
we go spreadsheets are so similar to
source code is what I observe maybe we
can help
spreadsheet developers with some ideas
from source from so for engineering so
one of the things I build mi PC
dissertation is the idea of code smells
for spreadsheet formulas for example the
long method smell if you have a method
that's very long that does lots of
things it easily translate to long
formula if you have a formula and then
we're not really saying long in terms of
real length but we're looking at the
number of different operations so in
this formula we're summing and
multiplying and dividing if a lot is
going on in a formula it's just a smelly
and for the same reason as long
Methodist smelly another smell we know
from source code is the many parameter
smells you have a method and it has all
these knobs and buttons you can press
that's not very maintainable well if you
look at the spreadsheet format it's
referring to that and that and that and
that very very hard to read
conditional complexity is a smell it's
it exactly translates to spreadsheets we
have an if so you can mess them and I
used to be a limit I think of seven
layers of nesting but for reasons
unknown to me they have lifted that band
so you can now nest up to I'd and I was
sixty-four depths or something like that
oh this really really happens that
people nest six seven eight ifs well if
you have to be bug that it's just
horrible something we also inspected is
the use or the occurrence of clones like
we know clones in source code can be bad
because if you have the same
functionality in multiple places if you
have to make a change it has to be
changed everywhere this happens in Excel
as well for example in this formula you
have this soup formula that occurs twice
and this is not just bad for
maintainability but actually also for
execution speed of Excel because the way
the Excel caching model works is Excel
caches sell results but it doesn't cache
all sub formula so the execution engine
in this case would be like oh you want
to have the exponent of zero minus G 36
times C 36 no problem calculate pool and
then it would be there and then again we
think oh the exponent of 0 minus 336 it
would not remember that has already been
calculated so if you would extract that
sub part into a cell you would also get
the added benefit of more speed because
it's only calculate at once so we found
that those smells not only occur at the
formula level we have a tool that
visualizes dependencies between
worksheets as you can see here so the
blocks are worksheets and the arrows
connect worksheet so if there's a
formula here that would be a formula in
calculation 2 that would say input a 1
times 12 or something like that so
unfortunately not all visualizations
looked as structured as this one some
visualizations look like this still ok
some visualizations look like this which
is particularly interesting you can't
read it but these are the three quarters
2 3 and 4 with their corresponding month
but this is q1 with January February and
March so it somehow had a different
structure than all the other moms I mean
we asked the person that build this he
said yeah yeah
you know I started like a prototype in
q1 and I didn't really know what I
wanted yet
and then in q2 of course I I started all
afresh and then I really knew how to
build my model but then of course time
went by and as it goes it became January
again I totally forgot that that first
quarter was like back of a napkin I
didn't know what I'm building yet in the
model state of life with those two
different calculations so how would you
know without a visualisation tool like
this other visualizations look like this
also interesting so that's Dutch for
sheet 1 and G 2 you can't really see but
this is an arrow going both ways so all
the data went from the one worksheet to
that all the sort of the worksheets had
the same structure and all these
formulas were connecting and all the
connecting of the same worksheets so
there was definitely an opportunity
visualization like this where the
pinkish blocks are external worksheets
so this spreadsheet is referring to
different worksheets in different files
so they got this thing the model sheet
where we got it from and the whole
process around it was people emailing
someone spreadsheets they were put in a
special folder and then you open the
spreadsheet and it were freed all the
other spreadsheets yeah exactly you know
it was very wobbly to say the least so
we concluded that those smells don't
really occur only at individual formula
level but you can also have inter
worksheet smells like you can also have
inter class smells so who knows what
smell these classes are exhibiting when
I did this talk at another conference
someone yelled bad naming which is true
when I was what I was going for and I
was too lazy to change my slides so it's
not bad
yes a t-shirt for you this is the
feature and we smell because that method
here XY is it's it's envious its jealous
of those wonderful fields X&Y in Class A
it would rather live in Class A where
the fields are that it's so interested
in so this is a smell that also easily
transforms to spreadsheets if you have a
formula that refers to cells on another
worksheet then probably it's better if
it would go there this is an example
from an actual open data spreadsheet set
that we've analyzed so you see formulas
like this so we're on the income
statement worksheet and we have this
formula so if you if the quo if my boss
asks me what does that formula actually
really do ah if I have to debug it it
would go like this
okay required funds b9 that is buildings
amount of amount of buildings divided by
a required funds c9 that is depreciation
of the buildings etc etc you have to go
back and forth between all those cells
all the time to really read what's going
on then we know from cognitive science
that context switches are very expensive
if you have to switch views all the time
that's super annoying so what you could
do is take that formula and put it on
the required funds worksheet just take
it and copy paste it there and the added
benefit is that if you click it now
Excel will Cooler the dependence for you
which is nice I mean it's still sort of
a rainbow because these this is actually
three different color screen but at
least it's better because you can sort
of see what's going on and you don't
need to prefixes anymore either because
you're already on the required funds
worksheet or you can drop them which
makes the form a load shorter and then
you start to see some structure all your
these three things divided by each other
plus these three things divided by each
other so it's really a lot better like
it is in source code to place the
formula where it belongs
oh you could factor out 2/12 that's true
that's definitely true too so we started
a spin-off company called import wrong
we have a tool that does this smell
detection for you quick commercial break
where you can just upload your
spreadsheet and we tell you all the
smells that are in there so this would
be how it will look like for the
spreadsheet I just showed you well look
there then there more than 10 references
to that worksheet you should move it to
a different sheet then of course if you
say smells you say I have my hopes when
you're really reassuring definitely if
you have smells then you should refactor
your source code which also goes for
spreadsheets so in addition to giving
the suggestions as we did in the
beginning in the slide I showed you we
also have a refactoring tool that helps
people refactor their spreadsheet so
this is how it looks like it's called
bumble bee and the spreadsheet user can
ask for example for this formula it's a
simple example but it's sum divided by
account so you could also use a average
function so maybe this person isn't
aware that that average function exists
probably in this case it would be aware
over there for example also refactoring
tree supports going from assumed to
assume if and not everyone knows what is
some it is or how it works so you can
ask bumblebee do you have some
interesting rewrites for me can I
improve this formula and says yeah sure
we have some accounts to average
refactoring for you and like in modern
IDs you get a preview as well this is
how the formula will look like if you
have refactor it in and then you can
apply it and we'll movie just applies
the formula for you and those
transformations are programmable so boom
will be if you downloaded it it comes
with a set of refactorings but you can
also write your own refractories in a
little language so here for example is
the sum and counts to average
refactoring swimming arrange divided by
the count of the same range results in
tried this yet with users if you have
spreadsheet users in
your company let me know at the end of
this talk but we envision that
spreadsheet users that are power users
that they can write array formulas and
maybe can write some VB we think they
could also be capable of writing those
transformations themselves and in
addition we also want to explore the
idea of programming by example here a
little bit where if we observe a user is
changing formulas a few of them we could
deduce a refactoring pattern from it and
say hey you're doing the same thing
maybe we can apply that transformation
for you in the entire spreadsheet if
you're interested to try it or want to
encourage business users in your company
to try it you can download this from
fleadome slash bumblebee all the links
will be also in the final slide so you
don't necessarily to make a picture or
not I'll wait for you then if you say
refactoring of course you say that's a
good woman that's not it yes testing
you're not going to refactor your
spreadsheet without the security of some
tests and this is actually do haha oh
you don't believe in testing that's
happen fair enough
ok so this is actually the chronological
order in which I did the work but it
would have made sense also to start with
testing to do that at least before
refactoring however and this was typical
ivory tower thinking I thought yes but
how am I going to get spreadsheets users
to test and convince their management to
test it's already quite hard to get
professional developers to test and to
give their bosses you know to have them
get permission for testing but this was
really thinking the wrong way because
spreadsheet users are actually quite
good at testing we observed in the same
open data set I talked about earlier
that these type of formulas are actually
quite common so what this expresses is
if the sum of those guys if there are
100% together it outputs 100 but if it's
not it out
or this is like a test it's like an
assertion it expresses the knowledge
that those five things together should
be a hundred percent and in the lack of
real testing framework what do
spreadsheet users use for formulas they
have no other language and formulas we
found that eight percent of spreadsheets
that we analyzed had this type of test
formula so they're not all that in
common and then of course we thought huh
we could exploit those test formulas if
people are already testing a little bit
we could use that to extract a test
suite so we build an order to code
expector and you can ask expector to
search for those type of test formulas
in your spreadsheet and then I would say
hey look at that
I found a formula that looks a little
bit like a test we use some heuristics
there so we have a number of words that
we know that look like test outputs so
okay and or yes/no in real-life
spreadsheets we even found test formulas
outputting in smiley or a sad e face
depending on the results of the test
definitely so we know what a test
formula is we say hey what we found here
is that the sell on that the formula net
cell expresses that those guys should be
100% and as you see we've done some
parsing here to represent the test
formula in natural language to make it
easier for people to see it and then you
can add those formulas to the test suite
you can have the little tick mark save
the tests and then we say well it passed
that test pass because this is actually
a hundred percent and none of the tests
failed and what we also do is we show
users coverage of their tests they can
say show me what's tested and everything
that's covered by a test formalised
marked yellow and this is because this
is a test formula that depends on those
cells we say they are tested as well so
we're not really talking branch coverage
or statement covers here advanced ways
of coverage we just want to give users a
feel for how well everything is tested
so if you have a test all the
and also tested nearly as of course if
you would change one of the 20s then
that would show up this is not foolproof
of course because one could be too and
there are ways you could you could have
other cells here that depend on it it's
not foolproof however it is helping a
little bit giving people an idea of
what's testing what we also do is we
help people then add more tests and
instead of just letting them free we
thought it might be easier to just give
them one formula to test I have five
minutes before I'm going to a meeting
let me add one test to my formula so
that you can pick in three categories a
complex formula a formula with a big
value that represents a lot of money or
a lot of orders or whatever or formula
that has many precedents that that many
cells are referring to so you could say
for example if you ask for a complex
formula hey this formula here looks
looks pretty nifty that's probably
something you want to test if you just
want to add one test and then you can
express some tests building test so you
could say it's a number this is the
upper limit the lower limit and in a
slightly newer version of the two we
also have an extra value there where
users can write a formula so whatever
formula they won because we found this
is slightly too restrictive just having
types and upper and lower bounds and we
give people compliments we say wonderful
you have increased your test coverage
from 6% to 8% you want to encourage
people and show them that higher
coverage is is indeed better so we have
added two tests so now you see our test
suite is bare because now three tests
have passed and still no more failing
this tool too is available from my
website fill in a little comb slash
expect overages at this point giving the
plugins away for free because it's
active research and so no guarantees if
it doesn't work you can send me an email
but probably there's nothing I can do
about that
this is everything I wanted to share
with you but before we go over to
questions I do think we have some time
for questions we have some time for
questions
let me summarize my entire talk for you
in one minute so if you came in late or
it's the end of the day and you're
slightly sleepy this is your second
chance to get the gist of the talk in
like 30 seconds and otherwise of course
this is optimal preparation for
question-asking spreadsheets our codes
it's the most important message to
remember and tell all your friends and
tweet about it in and put it on a tee
shirts why because they're used for
similar problems there are just as
powerful namely turing-complete and they
suffer from typical software engineering
problems even cooler they are what every
programming language in 2016 aims to be
live and functional and used by everyone
what more is there to wish for I showed
you a few examples of how to implement
real-life programming problems in an
easy way in a spreadsheet including a
nice may solved with dynamic programming
so if spreadsheets are indeed code they
can use a little bit so for engineering
magic and I've showed you that in the
form of a smell detection tool every
fracturing to and attest to if you want
to know more this is the website perfect
excel of the smell detection tool that
is now commercially available you can go
to my website to download the
refactoring too and a testing tool
because they're still active ongoing
research and we really want people to
try that and I have a research group in
Delft as well where I force grad
students to also like this and you can
find out more about all the sorts of
research we do at our websites Prezi Lab
the lauric and if you want to connect
with me later I will be at the party
tonight you can send me a tweet if you
want to meet or an email or you can ask
a question now of course
yeah so one question is what is the
reach of people what is the audience not
everyone would use Excel like I do well
some people use it in way way more
advanced ways than I do with my little
programming example so of course there
are people that just use it for simple
lists but there are companies and
organizations that really depend on
Excel for for that core business so
insurance companies banks that really
make models that the company depends on
so we do think there are lots of people
that could benefit from this type of
software yeah that's just Excel user so
it's very hard for us to know how many
people you know where's the threshold of
serious use at what point this is also I
mean a general question for developers
at what point is it worth it to go from
notepad plus plus to a full fledged IDE
because you would have refactoring
support that's it's not a very easy
question to to answer and it could also
depend on your go well I'm actually in
two weeks going to go for a research
visit I will work there for three months
to to improve Excel and to maybe also
I'm not sure if they will give me a
given you look at the data but yeah they
actually were one of the funders of my