# Easy way to convert sentences into Excel formula

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.

`=SUMIF(B:B,">100")`

Use one of …

`=SUMIF(B2:B100,">100")`
`=SUMIF(SalesthisMonth,">100")`
`=SUMIF(YeartoDate[@Sales],">100")`