21. Real-World Project Briefs

21.1 Sales vs Target Tracker

By Ravindra Bagale · Last updated: 6 October 2026

The ask. Ravindra Bagale, the regional head, wants a one-page tracker every month: each city lead's sales against target, the gap in rupees, a Hit / Close / Miss status and a rank. It goes to the Monday review.

Sheet Sales, A1:D6 (November, fictional):

What to deliver (one under the other):

1. Convert A1:D6 to a Table named tblSales (Ctrl + T).
2. Add Achievement % = Actual ÷ Target.
3. Add Gap (₹) = Actual − Target (negative means short).
4. Add Status: Hit at 100% or more, Close at 90% or more, else Miss.
5. Add Rank on achievement, best = 1.
6. Add a total row: total achievement is total actual ÷ total target (not the average of the percentages).
7. Colour the Status column with conditional formatting.

Key formulas (row 2; copy down or let the Table fill them):

E2: =[@Actual]/[@Target]
F2: =[@Actual]-[@Target]
G2: =IFS([@[Achievement %]]>=1,"Hit",[@[Achievement %]]>=0.9,"Close",TRUE,"Miss")
H2: =RANK.EQ([@[Achievement %]],[Achievement %],0)
Total: =SUM(tblSales[Actual])/SUM(tblSales[Target])

Expected result (check your numbers):

Total actual ₹8,26,100 ÷ total target ₹8,50,000 = 97.2%. The simple average of the five percentages would be 96.0%, which is the wrong number to report.

Checks before you send it:

1. Achievement % column is formatted as % with one decimal.
2. The total uses SUM ÷ SUM.
3. Gap is negative only for leads below target.
4. Status for exactly 90% says Close (test by typing a target of 100 and actual of 90 on a copy).