ChatGPT and Excel: build formulas, check answers and find mistakes

Define the total before asking for a formula

ChatGPT can suggest formulas when you explain the data structure and question precisely. But a formula that calculates successfully does not necessarily answer the reporting question. “Total registrations” could mean the number of rows, unique IDs or participants. Define the meaning before copying a formula.

This exercise uses eight fictional registration rows for AI and Excel programmes. There is one repeated ID, one missing status and one cancelled registration. The aim is to calculate the subset of Confirmed registrations with unique IDs while identifying records that need review.

Download the workbook to see the formulas and instructions, or use the CSV to rebuild the exercise. The reference answers are calculated from the data and stated rules. They do not depend on whether an AI produces a similar answer.

Understand the data and set rules for problem records

The Data sheet stores ID, Tarikh, Program, Status, Peserta and Kadar_RM in columns A to F. Data occupies rows 2 to 9. One row represents a registration record; Peserta is the number of people in that record. Do not count eight rows as eight participants.

ID R004 appears twice with identical values. In this exercise, both rows are temporarily excluded from the Confirmed subset until they are reviewed. This is a deliberately conservative exercise rule, not an instruction to delete all repeated records in every dataset. The real data owner must determine whether a repeated record is an error or a valid transaction.

A blank status is not treated as Confirmed. Cancelled records are also excluded from this subset. R006 has zero participants and remains a valid record with a zero value. Explain the difference between a blank and zero so the formula does not hide a source-data issue.

Give ChatGPT the exact columns and conditions

Include the sheet name, data range and exclusion rules. Ask for an explanation of each part of the formula so you can assess the suggestion. Avoid sharing a complete real work file when a small sample is enough to explain the problem.

The instruction below uses helper columns so you can see the checks on every row. This also makes the method easier to discuss with colleagues: someone can point to why a record is included or held back without interpreting one long formula.

Fictional data is on the Data sheet, rows 2 to 9.
A=ID, B=Tarikh, C=Program, D=Status, E=Peserta, F=Kadar_RM.
Suggest formulas for G (Bil_ID) and H (Nilai_Baris_RM).
Then calculate total participants and value where Status="Confirmed" AND Bil_ID=1.
Both rows of a repeated ID must be temporarily excluded; do not automatically retain one.
Blank statuses and Cancelled are excluded. Zero participants is a valid value.
Explain the formulas and show the records contributing to the total.

Build formulas you can inspect row by row

In G2, COUNTIFS counts how often the ID in A2 appears in the source range. Dollar signs keep the range fixed as you copy the formula down. In H2, the participant count is multiplied by the rate to calculate the row value. Copy both formulas down to row 9.

SUMIFS then adds only rows with a Confirmed status and Bil_ID equal to one. Microsoft describes SUMIFS as summing with multiple conditions. Make sure the criteria ranges and sum range have the same length; this example uses rows 2 to 9 throughout.

If the columns in your file are arranged differently, the formula references must change too. Do not copy these formulas into another workbook without matching the sheet and field names. Kadar_RM means the rate per participant in this example, so the units also matter.

Data!G2: =COUNTIFS($A$2:$A$9,A2)
Data!H2: =E2*F2

Subset value:
=SUMIFS(Data!$H$2:$H$9,Data!$D$2:$D$9,"Confirmed",Data!$G$2:$G$9,1)

Subset participant count:
=SUMIFS(Data!$E$2:$E$9,Data!$D$2:$D$9,"Confirmed",Data!$G$2:$G$9,1)

Compare totals with clearly defined reference answers

The four qualifying records give 25 participants and a value of RM3,240. The manual check is RM1,200 for R001, RM1,200 for R002, RM0 for R006 and RM840 for R007. This is the subset that can be reported under the exercise rules while problem records await review.

Multiplying and summing every source row gives RM7,620. Filtering only for Confirmed without checking IDs gives RM6,120. Both calculations can run without an Excel error while answering different questions. Do not label any of these totals as actual monthly revenue.

Test changes before applying the method to real work

Make a copy of the exercise and change one value at a time. If the participant count in R001 increases from 10 to 11, the subset should increase by one participant and RM120. If R007 changes to Cancelled, seven participants and RM840 should leave the total. Restore the original data after each test so the effect remains clear.

Next, try adding a new record beyond row 9. These fixed-range formulas do not include it automatically. Extend every range consistently or build a suitable Excel Table before turning the workbook into a monthly process. Source changes are part of testing, not something to ignore because the first formula worked.

Keep definitions, reasons for exclusions and refresh instructions with the workbook. ChatGPT can help write those notes, but the person responsible for the data must confirm the rules. A report that can be explained and repeated is more useful than a formula understood only by the person who copied it.