## 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:** =V*LOOKUP(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: