Excel Challenge 50: Calculate Tiered Quantity Volume Pricing Discounts

Challenge Scenario

Wholesale distributors and B2B suppliers reward high-volume buyers with tiered quantity discounts:

50 or more units (C2 >= 50): 20% discount (0.20).
10 to 49 units (C2 >= 10): 10% discount (0.10).
Fewer than 10 units: 0% discount (0).

As a Sales Operations Lead, you are processing four wholesale order batches:

Discount Rate % (Column D): Use a nested IF formula: =IF(C2>=50, 0.20, IF(C2>=10, 0.10, 0)).
Invoice Total (Column E): Multiply unit price by quantity, then apply the discount rate: =B2*C2*(1-D2).
Total Invoiced Revenue (Cell E7): Sum all invoice totals using SUM(E2:E5).
Discounted Orders Count (Cell D7): Count how many orders received a discount (> 0) using COUNTIF(D2:D5, ">0").

Your Sheet Layout

Understanding the volume discount pricing sheet:

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:C5`	Order, Price, Quantity	Order Batch	Client order number, unit price, and units purchased	Read-only purchase orders.
`D2:D5`	Discount %	Pricing Rule	`=IF(C2>=50, 0.20, IF(C2>=10, 0.10, 0))`	Determines tiered discount tier (0%, 10%, 20%).
`E2:E5`	Invoice Total ($)	Billed Amount	`=B2C2(1-D2)`	Calculates final payable amount after discount.
`D7`	Discounted Orders	Summary	`=COUNTIF(D2:D5, ">0")`	Counts orders that received discounts (3).
`E7`	Total Revenue	Summary	`=SUM(E2:E5)`	Total billed invoice revenue ($4,465.00).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: D2

In cells D2:D5, assign discount rate (20% for >=50, 10% for >=10, 0% otherwise) using IF.

Objective #2

Cell: E2

In cells E2:E5, calculate the final invoice total (= Price * Qty * (1 - Discount)).

Objective #3

Cell: D7

In cell D7, count how many orders received a discount (> 0).

Objective #4

Cell: E7

In cell E7, sum the total invoiced revenue across all orders.

Solution & Formula Breakdown

Let's examine how nested conditional pricing evaluates order quantities:

1. Tiered Discount Rate with Nested IF (D2:D5)

=IF(C2>=50, 0.20, IF(C2>=10, 0.10, 0))

Order #1001 (8 units): Less than 10 → 0%.
Order #1002 (25 units): >= 10 and < 50 → 10%.
Order #1003 (60 units): >= 50 → 20%.
Order #1004 (12 units): >= 10 and < 50 → 10%.

2. Net Invoice Total Calculation (E2:E5)

=B2*C2*(1-D2)

Order #1001: 8 × $50 × (1 - 0) = $400.00.
Order #1002: 25 × $50 × (1 - 0.10) = $1,125.00.
Order #1003: 60 × $50 × (1 - 0.20) = $2,400.00.
Order #1004: 12 × $50 × (1 - 0.10) = $540.00.

3. Summary Revenue & Count (D7 & E7)

Cell D7 (Discounted Orders): =COUNTIF(D2:D5, ">0") → 3 orders.
Cell E7 (Total Billed): =SUM(E2:E5) → $4,465.00.

Always test the highest threshold first in nested IF formulas (C2>=50 before C2>=10). If you checked >=10 first, a quantity of 60 would match >=10 and mistakenly get only 10%!