There’s a way to convert a sentence into an Excel Formula using modern AI. You’d expect this to come from Microsoft but instead it’s the work on one smart man who has made the Excel Formula Bot.
David Bressler has created a way to turn human sentences into an Excel Formula at https://excelformulabot.com/
It’s interesting to see a modern, open-source AI system at work. There’s hours of nerdy fun seeing what sentences can or can’t be handled by the Excel Formula Bot.
Excel Formula Bot examples
Here’s some examples of how the Excel Formula Bot works and how to get the most out of it.
For example type
Add up all the sales in Col B that are over $100
and Excel Formula Bot will suggest
=SUMIF(B:B,">100")
The Bot is clever enough to ignore unnecessary information (“the sales”) and knows that ‘Add up’ means SUM()
Median of all the sales in Col B that are over $100 returns =MEDIAN(IF(B:B>100,B:B))
If the text in Col A includes “Office” average the values in Col B
Excel Formula Bot will suggest:
=AVERAGEIF(A:A, “*Office*”,B:B)
Which is exactly right.
The Bot is smart enough to tell the difference between ‘includes’, ‘starts with’ or ‘exactly’ for text comparisons.
As you can see here, changing the text to ‘starts with “Office” ‘ will alter the formula to “Office*”
Tip: in a real worksheet change the whole column references (e.g A:A) to a narrower range or named range (e.g. A1:A50 or “Sales”)
We tried a sentence that wasn’t in the order of an IF function, the bot correctly understood.
Add up the numbers in Col B if cell A1 says "Yes", otherwise show "999" returns =IF(A1="Yes",SUM(B:B),999)
But it’s not perfect (we’d not expect that), see this simple example:
Work out the percentage price increase between B2 and B3 returns =B3-B2/B2
Which isn’t correct, it needs brackets =(B3-B2)/B2 and either the cell formatted as a Percentage or multiplied by 100.
But if we take out the first two, unnecessary, words … “work out”.
the percentage price increase between B2 and B3 then the bot gets it right =(B3-B2)/B2
Lesson: with this or any AI ‘natural language’ bot, it’s best to keep your text to the essentials.
Table References
The Bot does understand Excel Table references in the form Table[columnName] for example:
All the sales in Year2021[@Sales] that are over $100 returns =SUMIF(Year2021[@Sales],">100")
Named Ranges
The bot gets a little confused with named ranges (e.g. Sales ), which is understandable.
How many Customers are in Oregon? returns =COUNTIF(B:B,"Oregon")
The word ‘Customers’ is replaced with Column B.
Add up the numbers in Sales if cell A1 says "Yes", otherwise show "999" returns =IF(A1="Yes",SUM(B1:B10),999)
The word Sales becomes B1:B10, but that changes. Another test returned A1:A100 instead. The bot seems to put a placeholder range in place of the word Sales.
But putting the range name in quotes, seems to tell the bot that it’s a range and the formula is correct.
Add up the numbers in "Sales" if cell A1 says "Yes", otherwise show "999" returns =IF(A1="Yes",SUM(Sales),999)
The instructions say:
Model results are best when the prompt is as specific as possible (ie: referencing exact cells, rows or columns).
Tweak the Bot
In practice we’d take the Excel Formula Bot suggestion and tweak it a little. It’s not good practice to refer to whole columns or row so instead limit the range, apply a name or a table reference e.g.
Instead of
=SUMIF(B:B,">100")
Use one of …
=SUMIF(B2:B100,">100")
=SUMIF(SalesthisMonth,">100")
=SUMIF(YeartoDate[@Sales],">100")
Your milage may vary
Keep in mind that the Excel Formula Bot is constantly changing and learning so your results will vary.
You can help improve the bot by clicking “Yes” or “No” , sadly there’s no direct way to explain your problem if clicking ‘No’.
Kudos to David Bressler for this site. Hopefully Microsoft will take note. Combine Mr Bressler’s smarts with Microsoft’s considerable AI service would be a great help for Excel users.
Understand or fix an Excel sheet with Evaluate Formula
Excel’s order of calculation is not BODMAS
LET() assigns names to calculations in Excel
Text in Excel formulas
