Excel and Sheets with AI: Formulas, Errors, Structure, and Macros

I'm working in [Excel 365 / Excel 2016 / Google Sheets], the
interface language is [English / other — name it].

My data is on the sheet [Sales], the header is on row 1, data
starts at row 2:
A = branch (text), B = date (date format), C = revenue (number),
D = category (text)

Sample rows:
[paste 4 rows]

I need a formula that calculates: [sum revenue for the branch
named in cell F2, but only for this year].

I want:
1. the finished formula, ready to paste into cell [G2], in
   exactly my language/locale version,
2. a piece-by-piece explanation of what each function does,
3. what happens if a column or row gets added to the data,
4. which row I should use to check the result by hand.

Don't use functions my version doesn't have — if you're not sure
a function is available, offer a fallback for older versions too.

I need to solve this in [Excel 365]: [describe the task and the
columns].

Offer three different solutions:
1. the simplest possible formula, even at the cost of being less
   robust,
2. a robust solution that survives an added column and typos in
   the data (extra spaces, capitalization),
3. a solution using more modern functions (XLOOKUP, FILTER, LET),
   if they make sense here.

For each one, note: when to use it, what breaks it, how it
behaves on an empty cell and a not-found value. At the end,
recommend one and justify why — keeping in mind that I'll update
this table monthly and hand it off to a colleague.

I inherited this formula in [Excel] and don't understand it:

[paste the formula]

Context: it's in cell [G2] on the sheet [Report], the data is in
[Sales!A:D], header in row 1. Sample data:
[paste 4 rows]

Explain it step by step, from the inside out:
1. break it into its nested parts, and for each one say what it
   returns (a concrete value on my sample data),
2. what the formula does as a whole, in one plain sentence,
3. what assumptions it makes about the data — what has to be
   true for it to work,
4. under what circumstances it returns a wrong value without
   showing an error,
5. how I'd write it more simply with the same result.

Don't change anything in my workbook yet, just explain.

Rewrite this formula into a more readable form for [Excel 365]:

[paste the formula]

Requirements:
- the result must be identical in every case, including empty
  cells, zeros, and not-found values,
- if you use LET or helper columns, explain what each part does,
- write out how I can verify the new formula gives the same
  results as the old one (a check column, a count of mismatches),
- flag any cases where the new and old formula could actually
  differ.

Finally, suggest a cell comment that explains to a colleague what
the formula calculates.

My formula is returning an error. Here's everything I know about it:

Formula: [paste the formula]
It's in cell: [G2], sheet [Report]
Error message: [#N/A]
It shows up: [only on some rows / on all rows]
Program: [Excel 365, language/locale]

Sample data where it fails (include a row that works):
[paste 5 rows]

Do three things:
1. name the most likely cause and explain why that one,
2. give me 2 other possible causes to rule out, and how to test
   them quickly,
3. write the corrected formula.

Don't hide the error with IFERROR until we know why it's happening.

I know why my formula returns [#N/A]: [reason, e.g. the item
isn't in the lookup table yet]. I want to handle it so the report
looks presentable, but I still want to know about the problem.

Formula: [paste the formula]

Suggest a solution that:
- shows [blank / the text “not found” / zero] instead of the
  error, whichever makes sense for the totals that follow,
- doesn't mask any error type other than the one I'm expecting,
- lets me also calculate how many rows fell into the handled case
  (a count cell),

Explain how the handled value behaves in sums and averages.

My formula isn't returning an error, but the result is wrong.

Formula: [paste the formula]
What it returns: [$16,480]
What it should be: [$15,580]
How I got the correct value: [manually summed March from these
rows]

Sample data: [paste 8 rows including the disputed ones]

Find the discrepancy: which rows the formula counted extra or
left out, and why. Check in particular:
- ranges that are the wrong size or start one row off,
- numbers stored as text and spaces in text values,
- dates compared as text instead of as dates,
- absolute vs. relative references when the formula is copied
  down,
- hidden or filtered rows.

Write out how I'll verify the fix on two more rows.

I'll describe what I need to track in a spreadsheet, and you
design the workbook structure.

What I'm tracking: [e.g. monthly revenue and costs for five
branches, comparison against plan, year-over-year comparison]
Who will enter data: [me and the branch managers]
How often: [monthly, data pasted in from an export]
What has to come out of it: [a one-page summary for leadership
and a trend chart]

Suggest:
1. what sheets the workbook should have and what's on each one,
2. for the data sheet, exact columns: name, type, example value,
   whether it's filled in manually or by formula,
3. where to use data validation (dropdown lists) and why,
4. which calculations should be formulas and which should be a
   pivot table,
5. three mistakes I'll avoid with this structure.

Also point out what's missing from my request that you need to
ask me about.

I have a table that's grown over the years and is a mess.
Structure and sample:

[paste the header and 10 rows]

Problems I've noticed: [merged cells, subtotals sitting inside
the data, dates stored as text, three variant spellings of the
same branch name]

Write me a cleanup plan:
1. what to fix and in what order, so nothing breaks along the way,
2. for each step, the concrete procedure in [Excel] — which
   function or built-in tool to use,
3. how I'll verify before each step that the totals haven't
   changed (a control total before and after),
4. what to do so the same mess doesn't happen again.

Assume I'm working on a copy of the file.

I have this data in [Excel 365]:
columns [A branch, B date, C category, D revenue, E cost],
[3,200] rows, data starts at row 2.

I want a summary: [revenue and margin by branch and month, plus
each category's share].

Write me:
1. step-by-step pivot table instructions — where to click, what
   to drag into rows, columns, values, and filters,
2. how to set the date to group by month,
3. how to add a calculated field for margin,
4. how to format the table so it's readable (numbers, percentages,
   remove any grand totals that don't make sense),
5. what I do when new data comes in — how it gets refreshed.

Then write how I'd get the same result with formulas, and tell
me which approach is better for a monthly recurring report, and why.

I need to pull [product name and margin] from the sheet [Price
List] into every row of the sheet [Report], keyed on [product code].

Data:
Report: A = product code, B = quantity, data starts at row 2
Price List: A = code, B = name, C = cost price, D = sale price

Write a formula for [Excel 365] that:
- finds the value by code regardless of extra spaces or
  capitalization,
- returns a readable message when the code isn't in the price
  list,
- can be copied down without the reference to the price list
  shifting,
- keeps working after a column gets added to the price list.

Explain why you chose that particular function, and how I'll
verify that every row matched correctly.

Write me a macro (VBA) for Excel that does this:
[combine the data from every sheet in the workbook into one
sheet called “Summary”, add a column with the source sheet's
name, and sort by date]

Sheet structure: [header on row 1, same columns A-E, row count
varies, sheets are named after branches]

Safety requirements:
- the macro must not delete or overwrite the original sheets,
- it writes the result to a new sheet; if it already exists,
  ask before overwriting rather than overwriting silently,
- at the start, verify every sheet has the expected header, and
  stop with a message if not,
- at the end, print how many rows from how many sheets it processed.

Add a comment on every block. Then write instructions for how I
paste the macro in and run it, and how I test it on a copy of
the file.

Write an Apps Script for Google Sheets that, once a month,
[copies the sheet “Current” into an archive sheet named after
the month, and clears the input fields for the new month].

Requirements:
- never overwrite an archive sheet; if it already exists, append
  a sequence number to the name,
- only clear cells [B2:B20], leave formulas and headers alone,
- ask for confirmation in a dialog before deleting anything,
- write a row to a “Log” sheet with the date, who ran the script,
  and how many cells were cleared,
- don't send anything by email, just prepare the text in a cell.

Write instructions for how I install the script, run it manually
the first time, and schedule it afterward. Point out what I
should back up before the first live run.

I'm about to hand this workbook off to a colleague and I want to
make sure they can't do any damage. Description of the workbook:

Sheets: [list and what's on each one]
Key formulas: [paste 3-5 formulas and where they are]
What changes every month: [new data gets pasted into the Data
sheet]

Prepare for me:
1. text for a “How this works” sheet — briefly: where the data
   comes from, what's calculated where, what happens every
   month, what not to touch,
2. a list of places this workbook is most likely to break if
   someone interferes with it (adding a column, sorting, inserting
   a row),
3. a suggestion for which cells to lock, and why,
4. three checks a new user should run after every data update,
   to catch it if something's wrong.

Write for someone who uses Excel regularly but doesn't write
formulas.