The Problem
A price list becomes harder to maintain the moment two currencies are involved. You may start with clean USD values, but once a team also needs EUR prices, every line has to stay in sync with the current rate.
If the rate is typed separately in every row, the sheet becomes fragile. One updated cell and one forgotten cell can leave the totals wrong. The safer approach is to make the conversion rule live in one place and let every row formula point back to it.
The flow below shows why that single-rate setup matters. The product prices can change row by row, but the exchange rate should stay centralized so the converted column and totals stay connected.
The Problem: One Exchange Rate for the Whole Price List A centralized rate keeps every converted amount tied to the same rule.
In this workbook, the USD prices are already listed and the exchange rate is stored in a small rate cell. The challenge is to convert every row to EUR using that shared rate, then total both currencies so the original and converted values can be reviewed together.
USD prices -> shared exchange rate -> EUR prices -> currency totals
That keeps the worksheet maintainable. If the rate changes later, the user should be able to update one cell and trust the converted prices and totals to follow.
How We Solve It
Each EUR price is just the USD price multiplied by the exchange rate. The key detail is how we reference that rate. We lock the rate cell with absolute references so the formula can be copied down without drifting away from the official value.
Method 1: Hardcode the rate
Method 1: Multiply by a typed rate when you only need a quick one-off conversion.
This is fine for a quick estimate. You multiply the amount by a number like 0.92 and get the converted result immediately. The downside is maintenance. If the rate changes, every formula that contains that number has to be edited by hand.
This solves the math part of the problem, but not the maintenance part. It can convert a single row correctly, yet it is not the best fit for a sheet where one shared rate should control the whole list.
=B2 * 0.92
Method 2: Use an anchored rate cell
Method 2: Lock the rate cell so every copied formula points to the same exchange rate.
This is the pattern the challenge expects. The dollar signs in $G$1 lock both the column and the row. That means every copied formula still points to the same rate cell, which is exactly what we want when one exchange rate controls the whole list.
This solves the full challenge because each EUR price is calculated from its USD value while still using the one official rate in G1. Once the formula is copied down, every row stays tied to the same conversion rule and the totals update together.
=B2 * $G$1
Method 3: Convert in the other direction
Method 3: Reverse the math when you need to convert back to the original currency.
If you already have the EUR amount and need to recover the USD value, you can divide by the rate or multiply by its inverse. That is useful in audit work because sometimes you inherit the converted column first and need to trace it back.
This solves a related audit problem rather than the main one in this worksheet. It helps when the converted figure is already on the sheet and you need to work backward to confirm the original amount or check whether the rate was applied correctly.
=C2 * (1 / $G$1)
Function Explanation
1. Multiplication
The actual conversion is simple multiplication. Price times rate gives the converted amount. The trick in this challenge is not the math itself but making sure the formula keeps using the correct rate as you copy it down.
That is why the row formulas need to be built for maintenance, not just for one correct result. A converted column is only useful if every row follows the same rate rule.
2. Absolute References
The dollar signs in $G$1 create an absolute reference. That keeps the exchange rate anchored to one place instead of letting it slide to G2, G3, and so on.
This matters because the rate is a workbook setting, not row data. Locking it prevents copied formulas from drifting away from the source of truth.
3. SUM
Once the row calculations are done, SUM gives clean totals for both currencies. That makes it easy to compare the original list with the converted one.
The totals also act like an audit. If one row uses the wrong rate, the converted total is one of the first places that can reveal the problem.
In real sheets, the rate cell is the part you update. The product formulas should usually stay untouched.
Convert each USD price in B2:B6 to EUR using the rate in G1, make sure your formulas stay locked to that one rate cell, and then total both currency columns in the summary.