Challenge Scenario
In global ecommerce and retail merchandising, products are maintained in a base currency (such as US Dollars) and dynamically converted into international currencies like Euros (EUR) and Japanese Yen (JPY) for overseas storefronts.
Rather than hardcoding exchange rates directly into dozens of row formulas, professional financial analysts keep exchange rates in a dedicated parameter table. This ensures that whenever exchange rates fluctuate, updating a single rate cell instantly recalculates the entire catalog.
As an Ecommerce Pricing Assistant, your objective is to calculate local prices for 4 catalog items using the exchange rate table in Column G:
- EUR Price (Column C): Multiply USD Price by the EUR rate in cell
$G$2 (0.92).
- JPY Price (Column D): Multiply USD Price by the JPY rate in cell
$G$3 (155).
- Total USD (Cell B7): Sum all baseline USD prices using
SUM.
- Average EUR (Cell C8): Compute the average EUR catalog price using
AVERAGE.
Your Sheet Layout
Understanding how your worksheet is structured prevents referencing mistakes when copying formulas down columns:
- Product Catalog (Columns A & B): Shows original Item Codes (
ITEM-101 to ITEM-104) and their base prices in US Dollars ($100 to $400).
- Converted Price Columns (Columns C & D): Where you calculate prices in Euros and Yen using multiplication formulas.
- Exchange Rate Table (Columns F & G): The master conversion rate lookup table where EUR (
0.92) is stored in cell G2 and JPY (155) is stored in cell G3.
- Portfolio Summary (Rows 7 & 8): Aggregates total USD inventory value and average EUR item price.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Item Code, USD Price |
Product Catalog |
Base USD prices ($100, $250, $400, $150) |
Read-only baseline product catalog pricing. |
F2:G3 |
Currency, Rate to USD |
Exchange Rate Table |
EUR = 0.92, JPY = 155 |
Master exchange rate parameters. Must be referenced with dollar signs ($G$2 and $G$3). |
C2:C5 |
EUR Price |
Calculation |
=B2 * $G$2 |
Converts base USD price to Euros at 0.92 rate. |
D2:D5 |
JPY Price |
Calculation |
=B2 * $G$3 |
Converts base USD price to Japanese Yen at 155 rate. |
B7 |
TOTAL USD |
Summary |
=SUM(B2:B5) |
Calculates the total catalog value in USD ($900). |
C8 |
AVERAGE EUR |
Summary |
=AVERAGE(C2:C5) |
Calculates the average price across all products in EUR (€207). |
Note: Column E is deliberately left blank as a visual separator between the catalog and the exchange rate table.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C5), convert USD Price to EUR using =B2*$G$2.
2
In Column D (D2:D5), convert USD Price to JPY using =B2*$G$3.
3
In cell B7, calculate total USD Price using SUM(B2:B5).
4
In cell C8, calculate average EUR Price using AVERAGE(C2:C5).
Solution & Formula Breakdown
Let's examine the mathematical concepts and cell referencing rules needed to solve this challenge:
1. Converting USD to EUR with Absolute Referencing (C2:C5)
=B2 * $G$2
To calculate the price in Euros, multiply the USD price in Column B by the EUR exchange rate in cell G2:
- Why use
$G$2? The dollar signs ($) create an absolute reference. When you copy or drag the formula down from row 2 to row 5, the relative reference B2 shifts to B3, B4, and B5, but $G$2 stays firmly locked onto the EUR rate cell.
- Row 2 (ITEM-101):
100 * 0.92 = 92
- Row 3 (ITEM-102):
250 * 0.92 = 230
- Row 4 (ITEM-103):
400 * 0.92 = 368
- Row 5 (ITEM-104):
150 * 0.92 = 138
2. Converting USD to JPY with Absolute Referencing (D2:D5)
=B2 * $G$3
Similarly, multiply the USD price by the JPY exchange rate in cell G3, locking the cell with $G$3:
- Row 2 (ITEM-101):
100 * 155 = 15,500
- Row 3 (ITEM-102):
250 * 155 = 38,750
- Row 4 (ITEM-103):
400 * 155 = 62,000
- Row 5 (ITEM-104):
150 * 155 = 23,250
3. Aggregating Total USD Portfolio Value (Cell B7)
=SUM(B2:B5)
The SUM function adds all values in the specified cell range:
100 + 250 + 400 + 150 = 900. The grand total USD inventory value is $900.
4. Calculating Average EUR Price (Cell C8)
=AVERAGE(C2:C5)
The AVERAGE function sums all EUR prices and divides by the count of items (4):
(92 + 230 + 368 + 138) / 4 = 828 / 4 = 207. The average EUR item price is €207.
If you forget to lock the rate cell with dollar signs and write =B2*G2, copying down to row 3 will become =B3*G3 (which accidentally multiplies by the JPY rate 155 instead of EUR), and row 4 will become =B4*G4 (multiplying by an empty cell and returning 0). Always lock external lookup cells!