AI Creates Your Spreadsheets, Sometimes

We’ve been interested in looking at how AI can process things other than silly images. That’s why the “Free AI Bot that Generates the Excel Formula for Any Problem” caught our eye. Based on GPT-3, it supposedly transforms your problem description into a formula suitable for Excel or Google Sheets.

Our first prompt didn’t work out very well. But that was sort of our fault. When they say “Excel formula” they mean that quite literally. So trying to describe the actual result you want in terms of columns or rows seems to be beyond it. Not realizing that, we asked:

If the sum of column H is greater than 50, multiply column A by 0.33

And got:

=IF(SUM(H:H)>50,A*0.33,0)

A Better Try

Which is close, but not really how anyone even mildly proficient with Excel would interpret that request. But that’s not fair. It really needs to be a y=f(x) sort of problem, we suppose.

Here’s a different prompt:

Negative 1 if the sum of column H is greater than 50, 
otherwise 1 unless cell A4 is negative and then the result is 42

And the result:

=IF(SUM(H:H)>50, -1, IF(A4<0, 42, 1))

So where’s the intelligence? The result is correct, of course, but it isn’t that hard to figure out yourself.

Part of the problem with these sorts of things is that they are inexact. The website has disclaimers about “there may be imperfections in the model” and “verify this output before putting to use.” Verifying this is correct implies you could have written it yourself and it probably wouldn’t take much more time.

Looking Up Formulas

We were impressed that it seems to know how to find certain relationships. For example:

Find the power if the voltage is in cell B1 and the current in cell C1
=B1*C1

Or…

The current for a resistor in R1 when the voltage is in E1

=E1/R1

Here’s what’s funny, though. It must be keying on the actual names of the cells because try this:

The current for a resistor in C5 when the voltage is in C6

That flips the equation over for some reason:

=C5/C6

It just goes to show that the neural net doesn’t think about the problem the way we do. We think this one was right:

The amount of interest on a V1 balance at a compound interest rate in P1 compounded annually for V2 years

=V1*(1+P1)^V2

But we were pretty sure this one was wrong:

The time it takes a ball to fall to the ground from Q1 feet

=Q1/32

Our Opinion

So, in our opinion, this is little more than a parlor trick. You could have had some keyword-matching templates and gotten as good a result that would also be more repeatable. You could even ask for clarification or note impossible-seeming situations.

Is there a market for this? Sure, when it becomes Star Trek-level computer interaction. (“Computer: Run an analysis of all known Romulan transmission format…”). But at the level of GPT-3 guessing our formulas from natural language, it is just a toy. Even Wolfram Alpha which does a better job is still not up to the task, although we wonder how hard it would be for them to push out Excel formulae? At least it is better than the “prompt engineering” we’ve been hearing about.

That’s not to say that GPT-3 isn’t useful. It clearly is for some problems. This implementation of using it for spreadsheets just isn’t one of them. Still neat, though.

 

9 thoughts on “AI Creates Your Spreadsheets, Sometimes

  1. I love this tool. I haven’t used Excel enough to memorize even the basic formulas yet – I would have to look up how an IF statement should be formatted. I know what I want the computer to do, just not what words Excel wants to hear to make it happen.
    This tool does that last step of translating my intention into a formula. Often the result is perfect, other times it’s close enough that I can tweak it to be right.
    I’ve been doing some data analysis in Excel lately; the tool is bookmarked so I can keep it open on the other monitor while I work.

    1. This sounds like me and LabVIEW which is used in the company I work at. But coming from a C background, I know what I want to do but not which one of the 1000000 icons accomplished that. I’m sure there’s someone working on a translator…

  2. Reminds me a bit of Open AI DALL-E 2 and Midjourney. Definitely novel but still not quite universally there either.

    That all said, using Excel or Libreoffice Calc can be a huge undertaking to do even basic things. It can, if done 100% correctly, do some amazing things at scale but sheesh do they not make it easy at all to do basic testing or even elegantly put in code for that matter. This is all on top of VB and what it does or doesn’t do right as well.

    1. Microsoft has been teasing native Python support (as an alternative to VB) for awhile now. Not sure it’s ever been officially implemented within Excel outside of using add-ons. A proper built in Python API and better formula scripting views (e.g. ever try to do a large if/else? It gets annoying, real quick) would go a decent way in making it more user friendly.

      Heck, I find Google Sheets JavaScript way easier to make custom formulas for.

  3. The problem here is that this implementation of AI is trying to help you use real language to create the abstract excel “battleship” ($A$1* F17..AC2001) formulas when you could have used a modeling tool that supports Real Language Formulas natively out of the box. http://www.quantrix.com is a good example. Not to mention you can avoid the dangerous overloading of formulas and cells, not require a formula for every cell and not build all of the structure of the model from scratch every time. Too many advantages to list compared to traditional spreadsheets.

  4. I’d imagine this will get better over time. Any decent AI model will incorporate feedback, the more it is used by random users, the more of a dataset they have to align it with correct answers.

    The reason ML models are chosen for stuff like this versus handcoding a bunch of if/else or lookup tables is that overtime given a large enough dataset, the the trained model will encompass a massive amount of scenarios that were not accounted for in a hand done program.

Leave a Reply

Please be kind and respectful to help make the comments section excellent. (Comment Policy)

This site uses Akismet to reduce spam. Learn how your comment data is processed.