Shopping Offer Comparison in Excel: Buy 2 Get 1 Free vs % Off vs Flat ₹ Off

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: shopping-offer-comparison-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Five offers on a ₹250 pack (made-up values):

Buy 2 get 1 free is really 33.3% off. "Second pack at half price" sounds like 50% but is only 25%. But Zoya needs only 2 packs, so the best offer makes her buy 1 extra. That is fine for shampoo, not for something that expires.

2. Build it step by step

Step 1. Describe each offer

1. In B3 type the normal price of one pack (₹250).
2. In A5:I5 type Offer, Packs you pay for, Packs you get, % off, Flat ₹ off, You pay, Price per pack, Real discount, Rank.
3. Buy 2 get 1: pay 2, get 3. Second at half price: pay 1.5, get 2. Flat ₹150 off on 3: pay 3, get 3, flat 150.

Step 2. What you pay

1. In F6 type =$B$3*B6*(1-D6)-E6. One formula covers all offer types; unused parts are 0.

Step 3. Price per pack and real discount

1. In G6 type =F6/C6: what each pack really costs.
2. In H6 type =1-G6/$B$3 as %: the real discount. Copy F6:H6 down.

Step 4. Rank

1. In I6 type =RANK(G6,$G$6:$G$10,1). The last argument 1 ranks the smallest price as 1 (RANK).
2. Equal prices get the same rank, so two offers can both be 4.

Step 5. Best offer

1. In L6 type =INDEX(A6:A10,MATCH(MIN(G6:G10),G6:G10,0)): Buy 2 get 1 free (INDEX and MATCH).
2. In L8 type how many packs you need, and in L12 =MAX(0,L10-L8) for the extra packs the best offer makes you buy.

Step 6. Colours

1. Select A6:I10 > New Rule > Use a formula =$I6=1, green and bold (formula rules). Add data bars on H6:H10 (data bars).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Comparing the total paid. ₹200 for one pack vs ₹500 for three is not a fair comparison. Compare the price per pack.
2. Reading "half price" as 50% off. Half price on the second pack is 25% off across two packs.
3. Ignoring what you actually need. Three packs at the best price is a waste if one expires before you use it.
4. Forgetting the sort order in RANK. Without the 1 at the end, RANK gives rank 1 to the most expensive offer.
5. Different pack sizes. If sizes differ, compare price per ml or gram instead (see the unit price comparison).

5. More real examples

Example 1: Clothes sale. "Buy 3 for ₹999" vs "40% off": type each as packs paid, packs received and flat or % off.

Example 2: Mobile recharge. Compare plans by price per day: amount ÷ validity days.

Example 3: Bulk rice from a wholesaler. Add a delivery charge column to You pay before dividing by kg.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

Is buy 2 get 1 free a 33% discount?

Yes: you pay for 2 and get 3, so each pack costs 2/3 of the price, which is 33.3% off.

How do I compare offers in Excel?

Work out what you pay and divide by packs received to get a price per pack, then rank the prices.

How does RANK work for cheapest first?

=RANK(G6,$G$6:$G$10,1); the 1 means ascending, so the lowest price is rank 1.

Is "second at half price" 50% off?

No. You pay 1.5 for 2, so it is 25% off each pack.

How do I find the best offer name?

=INDEX(A6:A10,MATCH(MIN(G6:G10),G6:G10,0)).

7. Quick quiz

Ten questions on this page. Sign in to save your score.