Challenge Scenario
In retail sales analytics and inventory performance reporting, store managers calculate total dollar revenue generated by each product line and segment high-performing items as "BEST SELLER" products.
Multiplying Units Sold by Unit Price provides item revenue, while conditional threshold testing automatically flags products generating $2,000 or more for priority marketing and restock scheduling.
As an Ecommerce Sales Analyst, your tasks are:
- Revenue (Column D): Multiply Units Sold by Unit Price (
Units Sold * Unit Price).
- Performance Tier (Column E): If Revenue is $2,000 or greater (
D2 >= 2000), label as "BEST SELLER"; otherwise label as "STANDARD".
- Total Revenue (Cell D7): Calculate grand total revenue across all product lines using
SUM(D2:D5).
- Best Seller Count (Cell E8): Count how many products achieved
"BEST SELLER" status using COUNTIF(E2:E5, "BEST SELLER").
Your Sheet Layout
Understanding how the product sales performance sheet is structured:
- Product Sales Volume (Columns A to C): Shows Product Name, Units Sold, and Unit Price ($20 to $120).
- Revenue & Performance Tiers (Columns D & E): Calculates dollar turnover and assigns
BEST SELLER vs STANDARD classifications.
- Storewide Sales Summary (Rows 7 & 8): Aggregates total store turnover ($12,650) and counts top performing SKU count (3 items).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Product Name, Units Sold, Unit Price |
Sales Volume |
Product quantities and prices |
Read-only product catalog sales records. |
D2:D5 |
Revenue |
Calculation |
=B2 * C2 |
Calculates dollar revenue generated per product line. |
E2:E5 |
Performance |
Classification |
=IF(D2 >= 2000, "BEST SELLER", "STANDARD") |
Categorizes high-revenue items generating $2,000+. |
D7 |
TOTAL REVENUE |
Store Summary |
=SUM(D2:D5) |
Calculates grand total revenue across all products ($12,650). |
E8 |
BEST SELLER COUNT |
Store Summary |
=COUNTIF(E2:E5, "BEST SELLER") |
Counts best seller products (3). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate Revenue by multiplying Units Sold by Unit Price (=B2*C2).
2
In Column E (E2:E5), assign Performance using =IF(D2>=2000, "BEST SELLER", "STANDARD").
3
In cell D7, calculate total revenue using SUM(D2:D5).
4
In cell E8, count best seller products using COUNTIF(E2:E5, "BEST SELLER").
Solution & Formula Breakdown
Let's examine how each calculation and performance tier is evaluated:
1. Calculating Product Revenue (D2:D5)
=B2 * C2
Multiply Units Sold by Unit Price:
- Wireless Mouse (Row 2):
80 * 25 = 2,000 → $2,000
- Mechanical Keyboard (Row 3):
45 * 90 = 4,050 → $4,050
- USB-C Hub (Row 4):
30 * 20 = 600 → $600
- Noise-Canceling Headset (Row 5):
50 * 120 = 6,000 → $6,000
2. Performance Classification with IF (E2:E5)
=IF(D2 >= 2000, "BEST SELLER", "STANDARD")
If revenue in D2 is 2000 or greater, output "BEST SELLER"; otherwise output "STANDARD".
3. Storewide Summary Rollups (D7 & E8)
- Total Revenue (Cell D7):
=SUM(D2:D5) sums 2000 + 4050 + 600 + 6000 = 12,650.
- Best Seller Count (Cell E8):
=COUNTIF(E2:E5, "BEST SELLER") returns 3.
Using >= 2000 correctly includes products that generated exactly $2,000 (such as Wireless Mouse). If you used strictly greater than (> 2000), $2,000 would be excluded!