Adventures in AI: Excel formula edition

This is the first of a potentially ongoing series documenting my experiences with different AI products and use cases. Much of this is for myself so that I can understand how to best use these tools, but maybe it can be useful for others as well. In the long run, I hope to identify some clear ways to improve AI and perhaps build some tools to help it solve particular problems in a better way. 

Last week, I had a daunting and excessively boring Excel problem to solve. Essentially, I had a spreadsheet detailing some quality measure results for patients. The results were expressed as codes, and depending on the code, the patient would either get a designation of “Met” or “Not Met” for the quality measure. These codes were contained in two different fields, but never both. So, I wanted to write a formula that would look at these two fields, evaluate the codes and then return “Met” or “Not Met” depending on the code found.

I suspected that this was not a particularly difficult Excel problem, but I didn’t know how to do it, and I’ve found that googling for Excel help is usually fruitful but quite tedious as you search through various examples looking for your exact use case. So, I decided to try Claude to see if it could help me.

Here’s my initial prompt:

Claude then confidently replied with an answer and a formula. Here’s the formula:

=IF(OR(
    VLOOKUP([@CPT], CriteriaTable_CPT, 2, FALSE)=TRUE, 
    VLOOKUP([@HCPCS], CriteriaTable_HCPCS, 2, FALSE)=TRUE
), "Met", "Not Met")

It also told me that this formula assumes I create two tables, the CriteriaTable_CPT and the CriteriaTable_HCPCS. I asked for examples of these, and this is what it provided:

-- CPT Criteria Table --
| CPT Code | Meets Criteria |
|----------|----------------|
| 99213    | TRUE           |
| 99214    | TRUE           |
| 99215    | TRUE           |
| 90471    | TRUE           |
| 90672    | TRUE           |
| 86408    | TRUE           |
| 80053    | TRUE           |
| 83540    | TRUE           |
| 84153    | TRUE           |
| 84154    | TRUE           |

-- HCPCS Criteria Table --
| HCPCS Code | Meets Criteria |
|------------|----------------|
| G0102      | TRUE           |
| G0103      | TRUE           |
| G0438      | TRUE           |
| G0439      | TRUE           |
| G0444      | TRUE           |
| G0445      | TRUE           |
| G0447      | TRUE           |
| G0459      | TRUE           |
| G0463      | TRUE           |
| G0493      | TRUE           |

And it told me to create these tables and only insert my true values.

I tried this over and over again, and could never get it to work. I’m not entirely sure why it failed, but I eventually decided to simplify things. I concatenated my two fields (since they are never both populated at the same time) and simplified my formula to only use that field.

Still didn’t work.

After a lot of trail and error, what I discovered was that my tables needed to contain both my True and False codes for the formula to work. I don’t know that they technically needed anything in the second column at all, but they had to be in the table for the “Not Met” options to populate in the column.

Lessons learned

Well, the obvious lesson here is that Claude can be very confidently wrong. My attempt to move faster by using AI and not googling ended up taking me a lot longer because I had to troubleshoot Claude’s faulty code to get my answers. I also had to simplify my query with good ol’ fashioned human ingenuity before I could even get to that point.

When AI works, it feels like magic. But when it doesn’t, it’s a special kind of frustration because you may often feel like you’re the problem. You’ll ask yourself things like:

• Is my prompt clear enough?

• Did I mistype this formula?

• Have I missed an obvious step here?

Instead of asking the most useful question which is:

Does Claude know what he’s talking about?

In a lot of ways, these AI tools are much like their creators: they are overconfident in their knowledge. Instead of treating them like gurus, it’s likely more prudent to treat them like that slightly drunk guy at the party that will confidently explain any subject you broach with them. Expect a load of nonsense along with whatever nuggets of wisdom you might gain.