What You Will Learn
You will learn how to paste a broken Google Sheets formula into Gemini and ask for diagnosis, correction, explanation, and a validation check.
Required Dataset
Use the workbook from Lesson 28.
You will use:
Dynamic_Array_Practice
Query_Analysis
Create one new tab:
Formula_Debugging
Before You Start
In Formula_Debugging, create a small area for broken formulas, corrected formulas, explanation, and validation notes.
Use formulas from earlier lessons, especially SUMIFS, XLOOKUP, FILTER, and QUERY.
Step-by-Step Instructions
Step 1. Create a broken formula
Paste this intentionally broken formula into Formula_Debugging:
=SUMIFS(Dynamic_Array_Practice!D:D,Dynamic_Array_Practice!A:A,”West”,Dynamic_Array_Practice!B:B)
This formula is missing a criterion for column B.
Step 2. Ask Gemini to debug it
Open Gemini and enter:
Act as a Google Sheets formula debugger.
Formula:
=SUMIFS(Dynamic_Array_Practice!D:D,Dynamic_Array_Practice!A:A,”West”,Dynamic_Array_Practice!B:B)
Error or issue:
The formula does not work.
Objective:
Diagnose the issue, provide the corrected formula, explain the correction, and show how to validate the result manually.
Gemini should explain that every criteria range in SUMIFS needs a matching criterion.
A corrected formula might be:
=SUMIFS(Dynamic_Array_Practice!D:D,Dynamic_Array_Practice!A:A,”West”,Dynamic_Array_Practice!B:B,”Apparel”)
Step 3. Debug a QUERY formula
Try this broken formula:
=QUERY(Dynamic_Array_Practice!A1:F,”select A, sum(D) where F = Active group by A”,1)
Ask Gemini to debug it. The likely issue is that text criteria such as Active must be inside quotes within the query string.
Corrected formula:
=QUERY(Dynamic_Array_Practice!A1:F,”select A, sum(D) where F = ‘Active’ group by A”,1)
Expected Result
You should have a debugging workflow that helps you identify formula syntax errors, missing arguments, wrong ranges, text criteria problems, and incorrect assumptions.
Check Your Work
Confirm that Gemini explains the cause of the error, not just the corrected formula. Manually validate the result by filtering the source data and comparing the total.
Optional Extension
Ask Gemini:
Review the formulas in Formula_Debugging. Identify common error patterns, explain how to prevent them, and create a short formula QA checklist.
