---
name: spreadsheet-formula-helper
description: "A working spreadsheet formula matched to your actual layout, with a plain breakdown so you can adapt it, and notes on where it breaks. Use when someone is stuck on an Excel or Google Sheets formula and needs one that fits their real sheet layout. Do not use for building a full model or writing macros and scripts. Related topics: Data, Spreadsheets, Productivity."
---

# Spreadsheet Formula Helper

A working spreadsheet formula matched to your actual layout, with a plain breakdown so you can adapt it, and notes on where it breaks.

## When to use

Use this skill when someone is stuck on an Excel or Google Sheets formula and needs one that fits their real sheet layout. Do not use it for building a full model or writing macros and scripts.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Restate the goal and the layout in one sentence, and pick functions available in the stated tool.
3. Diagnose the failed attempts, naming the cause of each error message.
4. Write the formula with references matching the described layout, handling blanks, duplicates, and mixed text and numbers.
5. Break the formula into parts and explain each so it can be adapted.
6. State what happens when rows or columns are inserted and give the sturdier form if needed.

## Inputs

- **What I am trying to do** (longer free text)
- **How my sheet is laid out** (longer free text)
- **Tool** (choose one: Excel, Google Sheets, LibreOffice, Numbers)
- **What I have tried** (longer free text)

## Output

The formula in a code block, a part-by-part explanation, any helper column setup, and a short note on failure cases.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Spreadsheet Formula

Write the formula for the task below.

What I am trying to do: {{What I am trying to do}}

How my sheet is laid out: {{How my sheet is laid out}}

Tool: {{Tool}}

What I have tried: {{What I have tried}}

Give the formula with cell references that match my layout, then break it down piece by piece so I can adapt it. Handle blanks, duplicates, and text mixed with numbers instead of assuming clean data. If a helper column makes it simpler, use one and say where it goes. Use functions that exist in my chosen tool. Say where it breaks if rows or columns are inserted, and how to make it hold up. If I tried something that failed, explain the cause of that error.

## Pitfalls

- Using a function the chosen tool does not have, such as a newer Excel-only function in LibreOffice.
- Assuming clean data, so one blank or text-formatted number returns an error.
- Hardcoding ranges that silently exclude rows added later.
