Help me write an Excel formula for this workbook task: [CALCULATION GOAL]. I use [EXCEL VERSION AND LOCALE]. My sheet layout and sample values are [SHEET LAYOUT AND SAMPLE DATA]. The formula will go in [FORMULA CELL OR RANGE], and these are the rules and edge cases: [BUSINESS RULES]. First restate the calculation in one sentence and identify any ambiguity in references, data types, or rule order. Then provide: 1. The exact formula, ready to paste, using the cell references and separator style for my Excel version. 2. A line-by-line plain-English explanation of the functions and reference types used.
3. A compact test table with at least five cases drawn from my rules, expected result, and actual formula result. 4. A version for a fill-down column or Excel Table structured references if appropriate. 5. A short troubleshooting section for likely errors, such as text-formatted numbers, dates stored as text, blank cells, duplicate keys, spilled arrays, or circular references. Prefer the simplest maintainable formula that works in my stated Excel version. Do not silently substitute a different business rule, invent columns, or assume blank means zero unless I say so. If a formula cannot reliably satisfy the rule, say so and propose a helper column, Power Query step, PivotTable, or data-cleanup step instead. Before answering, manually trace the formula against every sample row and verify its behavior for blanks, errors, and boundary conditions named in my rules. Also identify volatile functions or full-column references that could slow a large workbook. Ask up to 3 clarifying questions only if a required input is missing.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [CALCULATION GOAL] | Describe the exact result the formula must return and the row or record it applies to. | Return the commission due for each sales order based on rep, order date, and net revenue |
| [EXCEL VERSION AND LOCALE] | Enter your Excel version, operating system if relevant, and locale or list separator. | Microsoft 365 for Mac, US English, comma separator |
| [SHEET LAYOUT AND SAMPLE DATA] | Paste column headers, cell locations, and several representative rows including problem cases. | Orders!A:A Order ID, B:B Rep, C:C Order Date, D:D Net Revenue; Rates table H2:J5 has Rep, Start Date, Rate; sample row: A2=1042, B2=Jordan Lee, C2=7/15/2026, D2=12500 |
| [FORMULA CELL OR RANGE] | Enter where the formula will be placed and whether it will be copied down or spilled. | Orders!E2, copied down to E5000 |
| [BUSINESS RULES] | List the decision order, blank handling, date rules, exceptions, and expected outputs. | Use the latest rate start date on or before the order date for that rep; blank revenue returns blank; negative revenue returns 0; missing rate returns “Rate missing” |
How to use
- Paste headers, cell addresses, and sample rows that include blanks, duplicate values, and boundary dates.
- Test the returned formula in a copy of the workbook before filling a live column.
- Compare the test cases to the business rule, especially at date boundaries and missing-match cases.
- Follow up with: “Rewrite this as a structured-reference formula for an Excel Table named Orders and keep the same error behavior.”
Variations
Lookup formula
Use when matching a value across sheets or a reference table.
Write an Excel lookup formula for [LOOKUP GOAL] in [EXCEL VERSION]. My source table is [SOURCE COLUMNS], my lookup table is [REFERENCE COLUMNS], and the formula belongs in [TARGET CELL]. It must handle [MATCH RULES], including missing or duplicate matches. Return the paste-ready formula, an explanation, and five test cases. Prefer XLOOKUP only if my version supports it; otherwise provide a compatible alternative. Do not assume an exact match if the rule requires approximate or date-effective matching. Ask up to 3 questions only if required inputs are missing.
Date formula
Use when calculating deadlines, aging, workdays, or reporting periods.
Create an Excel formula for [DATE CALCULATION] in [EXCEL VERSION]. The date fields are [CELL REFERENCES AND EXAMPLES], the result goes in [TARGET CELL], and the calendar rules are [WEEKEND, HOLIDAY, TIME-ZONE, AND BLANK RULES]. Give the exact formula, explain inclusive versus exclusive day counting, and show test cases for weekends, holidays, blanks, and dates on the boundary. Flag if a holiday table or helper column is required. Ask up to 3 questions only if required inputs are missing.
Data cleanup
Use when inconsistent text or dates are breaking formulas.
Diagnose and fix this Excel data-cleanup problem: [PROBLEM]. I use [EXCEL VERSION], and sample raw values are [SAMPLE DATA]. Create a safe cleanup formula or Power Query sequence for [TARGET OUTPUT], preserving original data in [ORIGINAL RANGE]. Explain how it handles extra spaces, nonbreaking spaces, text numbers, date formats, blanks, and errors relevant to my sample. Include before-and-after test cases. Do not overwrite source values or guess a date convention. Ask up to 3 questions only if required inputs are missing.
Tips
- Write the rule in priority order before choosing functions; an elegant formula cannot resolve an unstated rule conflict.
- Use Excel Tables and structured references for recurring data so formulas expand with new rows.
- Avoid wrapping every formula in IFERROR; it can hide a broken lookup or malformed source data that should be fixed.
- Keep a small test area with known inputs and outputs whenever a formula controls money, reporting, or eligibility decisions.
FAQ
Can ChatGPT create formulas for older versions of Excel?
Yes, if you state the version. Functions such as XLOOKUP, LET, FILTER, and dynamic arrays are not available in every version.
Why does a formula work in the example but fail in my workbook?
Common causes are text stored as numbers, mismatched date formats, invisible spaces, incorrect relative references, and different locale separators. Include actual sample cells when asking for help.
Should I use one complex formula or helper columns?
Use helper columns when they make rules auditable or easier to test, especially for financial, operational, or compliance calculations. A shorter formula is not automatically safer.