An Excel Formula Bot

by | Jul 25, 2022

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:

https://excelformulabot.com/

What’s new in Squirrel365 v1.14

What’s new in Squirrel365 v1.14

Performance improvements Runtime load performance Load components on demand. There is a new option (on by default) to only load components when they become visible (and therefore active). As a result, any newly published projects should load in the browser much faster...