Spreadsheet Formula Generator

Generate Excel and Google Sheets formulas from plain English descriptions with step-by-step explanations.

Use this prompt — free See example output

The Prompt

(2 messages)

System

You are a spreadsheet power user. Convert natural language descriptions into correct formulas for the specified platform.

Rules:
- Generate formulas for the specified platform (Excel or Google Sheets) — note syntax differences
- Explain each function and parameter in the formula
- Provide the formula ready to paste (starting with =)
- If the task requires multiple formulas or helper columns, show the complete setup
- Include example data to demonstrate the formula works
- Suggest alternatives when a simpler approach exists (e.g., pivot table vs. complex formula)

User

Generate a spreadsheet formula:

Task: {{task}}
Platform: {{platform}}
Data layout: {{data_layout}}

Variables

Fill in these inputs to customize your output:

{{task}}

Example: Calculate the running average of the last 7 days of sales, handling blank cells and starting the average only when we have at least 3 days of data

{{platform}}

Example: Google Sheets

{{data_layout}}

Example: Column A has dates, Column B has daily sales amounts. Data starts in row 2 (row 1 is headers). Some days may have empty sales cells.

Example Output

Here's what this prompt generates with the sample inputs:

Sample outputclaude-haiku-4-5

{
  "formula": "=IF(COUNTA(OFFSET(B2,MAX(0,ROW()-ROW($B$2)-6),0,MIN(7,ROW()-ROW($B$2)+1),1))>=3, AVERAGEIF(OFFSET(B2,MAX(0,ROW()-ROW($B$2)-6),0,MIN(7,ROW()-ROW($B$2)+1),1),\"<>\"), \"\")",
  "simplified_alternative": "=IF(ROW()-ROW($B$2)>=2, AVERAGEIF(INDIRECT(\"B\"&MAX(2,ROW()-6)&\":B\"&ROW()),\"<>\"), \"\")",
  "explanation": [
    "INDIRECT builds a dynamic range covering the last 7 rows (or fewer at the start)",
    "AVERAGEIF with '<>' ignores empty cells in the average",
    "The outer IF checks we have at least 3 days of data before showing a result",
    "ROW()-ROW($B$2) calculates how many data rows we have so far"
  ],
  "example": {
    "input": "Row 2: Jan 1, 100 | Row 3: Jan 2, (blank) | Row 4: Jan 3, 150 | Row 5: Jan 4, 200",
    "output": "Row 2-3: (blank, <3 values) | Row 4: 125 (avg of 100, 150) | Row 5: 150 (avg of 100, 150, 200)"
  },
  "tips": [
    "Place this formula in column C starting at C2 and drag down",
    "For very large datasets (10K+ rows), consider using a pivot table with a calculated field instead — formulas with OFFSET/INDIRECT recalculate on every edit"
  ]
}