📦
What You're BuildingA returns analysis dashboard for ShopEase's Q1 data — automatically calculating return rates for 25 orders via ARRAYFORMULA, classifying risk levels with nested IF and conditional formatting, querying patterns by category, and building a summary dashboard using SUMIF/COUNTIF/AVERAGEIF.
Day4
| Order ID | Category | Sale (₹) | Return (₹) | Return Rate% | Risk Flag |
|---|---|---|---|---|---|
| R001 | Electronics | 32,000 | 28,000 | =formula | HIGH RISK |
| R003 | Home | 950 | 200 | =formula | LOW |
| R006 | Clothing | 1,800 | 1,800 | =formula | HIGH RISK |
| … 22 more rows … | |||||
🪜
Step-by-Step Guide1
Build Your 25-Row Returns Dataset
Create tab Day4. Add these columns: Order ID, Product Name, Category (Electronics/Clothing/Home/Books), Sale Amount, Return Amount, Return Reason, Return Date, City, Customer Rating (1–5). Then add 25 rows. Ensure Return Amount is always <= Sale Amount for realistic data.
Distribution TipFor interesting QUERY results, include at least 5-6 rows per category. Mix return reasons — "Damaged" for Electronics, "Not as Described" for Clothing, "Changed Mind" for Books.
2
Return Rate % — ARRAYFORMULA (Column J)
In cell J3, calculate return rate for all 25 rows at once:
Cell J3 — one formula for all rows
=ARRAYFORMULA(
IF(E3:E27="", "",
IFERROR((F3:F27 / E3:E27) * 100, 0)))
IFERROR is EssentialIf any Sale Amount is 0, dividing by it causes #DIV/0! error. IFERROR(formula, 0) replaces the error with 0 instead — keeping your sheet clean.
3
Risk Flag with Conditional Formatting (Column K)
Cell K3 — Risk classification
=ARRAYFORMULA(
IF(J3:J27="", "",
IF(J3:J27>60, "HIGH RISK",
IF(J3:J27>=30, "MEDIUM",
"LOW"))))
Apply 3 Conditional Formatting Rules on K3:K27:
Rule 1: Formula =$K3="HIGH RISK" → Red fill (#FDECEA), bold red text
Rule 2: Formula =$K3="MEDIUM" → Yellow fill (#FFF8DC), dark gold text
Rule 3: Formula =$K3="LOW" → Green fill (#DCF5E8), dark green text
Rule 1: Formula =$K3="HIGH RISK" → Red fill (#FDECEA), bold red text
Rule 2: Formula =$K3="MEDIUM" → Yellow fill (#FFF8DC), dark gold text
Rule 3: Formula =$K3="LOW" → Green fill (#DCF5E8), dark green text
4
QUERY — Category Breakdown (Row 30)
Cell A30
=QUERY(A2:K27,
"SELECT D,
COUNT(A),
AVG(J),
SUM(F)
GROUP BY D
LABEL COUNT(A) 'Total Returns',
AVG(J) 'Avg Return Rate %',
SUM(F) 'Total Return Value'", 1)
5
QUERY — Electronics High-Return Filter (Row 50)
Cell A50
=QUERY(A2:K27,
"SELECT A, B, E, F, J, K
WHERE D = 'Electronics'
AND J > 50
ORDER BY J DESC", 1)
6
Summary Dashboard — SUMIF, COUNTIF, AVERAGEIF (Column M)
Build a dashboard in column M–P that answers key questions per category:
// For "Electronics" — repeat for each category
M4: =COUNTIF($D$3:$D$27, "Electronics")
N4: =SUMIF($D$3:$D$27, "Electronics", $F$3:$F$27)
O4: =AVERAGEIF($D$3:$D$27, "Electronics", $I$3:$I$27)
=SUMIF( range, criteria, sum_range )
$D$3:$D$27 — Column to check for the condition (Category)
"Electronics" — The condition to match
$F$3:$F$27 — Column to SUM when condition is matched (Return Amount)