When Users Ask for 'Profit Margin' and You Realize Formulas Are Harder Than You Thought

#ai #buildinpublic #nextjs #startup

Formula support is working in SpeakSheet.

But getting here? That was a journey I didn't expect.

Here's what happened when I tried to teach AI the difference between "calculate total" and "calculate profit margin."

The Request That Started It All

My Brain

"This is cool, but can it add formulas? Like, I want to track revenue and cost, and have it automatically calculate profit margin."

Me: "Sure, how hard can that be?"

Model: It was, in fact, harder than that.

The Naive Approach (That Failed)

My first thought: "Just tell the AI to add formulas."

Prompt to Gemini:
User wants: "Track sales with revenue and cost. Calculate profit margin."

Generate an Excel file with a profit margin formula.

The Problem: AI Doesn't Understand Context

I realized the issue:

Gemini is great at understanding WHAT the user wants:

✅ "Calculate profit margin" → User wants ((Revenue - Cost) / Revenue) * 100

But terrible at the specifics:

❌ Should it be a decimal or percentage?
❌ Should it use cell references (B2) or column names (Revenue)?
❌ Should it calculate the value or generate a formula?

Every time I asked, I got different outputs.

Not good enough for production.

Solution (That Actually Works): Schema-Based Formula Generation

Here's what I changed:

The New Flow:

Step 1: AI analyzes user's file + prompt

When user uploads an existing Excel file and adds a prompt like:
I send to Gemini:

{
  "userPrompt": "Calculate profit margin",
  "fileSchema": {
    "columns": [
      { "name": "Revenue", "type": "number", "position": "B" },
      { "name": "Cost", "type": "number", "position": "C" }
    ],
    "rowCount": 100
  }
}

Step 2: AI returns structured schema (not formulas)
Gemini responds with:

{
  "calculation_type": "profit_margin",
  "columns_needed": ["Revenue", "Cost"],
  "output_column": {
    "name": "Profit Margin",
    "type": "percentage"
  }
}

Step 3: I generate the formula using system prompt template

This is where it gets interesting.

*The Problem I Didn't Expect: *
Excel's "Enable Editing" Button
Formulas were generating perfectly.

But when users opened the files:
"The profit margin shows 0 instead of 40%"

I tested on my machine: Worked fine.
Sent to beta tester: Shows 0.
What??

The Investigation
After an hour of debugging, I asked the beta tester to screen share.

What I saw:
They open Excel file
Yellow banner at top: "PROTECTED VIEW"
Formula cells show: 0
They click "Enable Editing"
Formula cells suddenly show: 40%
Oh no.

The Root Cause:
Excel Protected View
Excel opens files from "untrusted sources" (downloads, programmatically-generated files) in Protected View.

In Protected View:

Formulas calculate in the background ✅
But Excel shows 0 instead of the calculated value ❌
Users must click "Enable Editing" to see results ❌
This made my AI-generated files look broken.

Users thought: "The formula doesn't work."

Reality: "The formula works, Excel is hiding the value."

But Wait, There's More:
Complete UI Overhaul
While fixing formulas, I realized the UI was... not good.

The old UI:
Generic input box
Basic "Generate" button
No feedback during processing
Looked like a 2010 Bootstrap template

The new UI