An AI Bot to write Excel formulas ???
Over the weekend I received the weekly newsletter from the chaps at Full Stack Modeller (it’s a quick and quirky read for anyone in the spreadsheet/modelling world, written alternately by Giles Male and Kenny Whitelaw-Jones, I thoroughly recommend it).
In this issue there was a short section about “Excel formula bot”, a free, AI-powered bot written by David Bressler that turns a plain English description into an appropriate Excel formula.
Kenny’s example was: “remove the last four characters from any text label” which returned him the formula =LEFT(A1,LEN(A1)-4)
Must be a lucky hit I thought. How wrong I was!
This bot is a truly remarkable (and slightly scary) piece of technology as you can see from my experiments below.
Experiments with the Excel Formula Bot
First I just checked a variant of Kenny’s example by asking “get the text between the dot and the comma in another piece of text” to which the reply was:
=LEFT(RIGHT(A1,FIND(“,”,A1)-FIND(“.”,A1)),FIND(“,”,A1)-FIND(“.”,A1))
which is correct but perhaps a little long winded, however a slight change in the request gives a better answer:
Input: get what is between the dot and the comma in a piece of text
Output: =MID(A1,FIND(“.”,A1)+1,FIND(“,”,A1)-FIND(“.”,A1)-1)
Then I remembered a post I had commented on last week about getting the smallest non-zero value in a range, so I typed “return the smallest non-zero value in a range” and got
=MIN(IF(A1:A10>0,A1:A10))
Which was at least as good as all the suggestions which had been made in the post.
Now I was hooked. I launched one of my favourites; “return the annual growth rate from two 5 year values”, to my surprise, it got this right too replying:
=((B2/A2)^(1/5))-1
extraordinary! It gets even more useful if you ask the general question “return the annual growth rate from two values” which gives:
= ((Ending Value/Beginning Value)^(1/Number of years))-1
So then I thought, “what about Squirrel365 specific ones?”, for example in Squirrel it is often useful to be able to toggle a value in a cell, and sure enough:
Input: toggle a value
Output: =IF(A1=1,0,1)
What about VLOOKUP?
Finally I went to the old stalwart which everyone knows but can never remember; VLOOKUP:
Input: the value in the third column of a B1:F17 matching A1 in the first column
Output: =VLOOKUP(A1,B1:F17,3,FALSE)
But some people prefer INDEX and MATCH so I added “using index and match” to my request and sure enough I got:
Input: the value in the third column of a B1:F17 matching A1 in the first column using index and match
Output: =INDEX(B1:F17,MATCH(A1,B1:B17,0),3)
But what about XLOOKUP? The same is true, changing to “using XLOOKUP” gave:
Input: the value in the third column of a B1:F17 where the first column of B1:F17 matches A1 xlookup
Output: =XLOOKUP(A1,B1:F17,3)
Failing on Primes
In the short time I played with it, the only thing I could find to break it was to ask it about prime numbers:
Input: return the tenth prime number
Output: =PRIMENUMBER(10)
Input: is 2343 prime
Output: =ISPRIME(2343)
Which looked good until I realised that neither of these functions exist in Excel (or Google Sheets). Perhaps this is just the bot predicting a future release!
An AI Bot for a Complete Excel Profitability Model?
Primes notwithstanding, I was truly staggered (and as I said slightly scared) at what the bot could do.
How long before I could ask for “a model to predict 15 year profitability of our network in the face of the acquisition of one of our large customers by a competitor” and get a model with all appropriate drivers in it returned in a few seconds. Before seeing the Excel Formula Bot I would have been very sceptical, now I am not so sure. Perhaps AI is coming after our financial modelling jobs after all.
If you want to try it out and amaze/scare yourself, then you can find the bot at:
