Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: Mixed INR/USD without FX

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Amount_INR just copies Amount. USD 1000 is treated as ₹1000. Use IF Currency="USD" then ×83. Broken total 150900 → fixed 273900 (LibreOffice verified).

About this data: Made-up SaaS revenue for Shreya Kapoor (Mumbai) and Daniel Ortiz (Austin). FX practice rate 83.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Multi-currency sheets without an FX column silently distort dashboards.

What: Five deals. Broken SUM 150900; fixed 273900 with USD×83.

How:

  1. Open Revenue.
  2. Spot USD rows.
  3. Amount_INR = IF USD then ×83 else Amount.
  4. Confirm total 273900.

Ravindra Bagale’s Tip

Never SUM a mixed Currency column. Always normalize to one reporting currency first.

Bugs explained (with mini examples)

Bug 1 — No FX conversion

USD added as INR.

Bug 2 — Single Amount column reused

Need Amount_INR (or Amount_USD) explicitly.

Practice tasks (12)

  1. Task 1. Broken total?
    Hint: 150900.
  2. Task 2. Fixed?
    Hint: 273900.
  3. Task 3. FX used?
    Hint: 83.
  4. Task 4. Write Amount_INR formula.
    Hint: IF USD ×83
  5. Task 5. USD rows contribution in INR.
    Hint: 83000+41500
  6. Task 6. Interview sentence.
    Hint: “USD added without FX.”
  7. Task 7. Change FX to 84 — what updates?
    Hint: USD rows.
  8. Task 8. Why not convert INR to USD in same column?
    Hint: Pick one reporting currency.
  9. Task 9. Bonus: FX table by date.
    Hint: Advanced.
  10. Task 10. Data validation on Currency.
    Hint: INR/USD.
  11. Task 11. Pivot by Currency before fix.
    Hint: See mix.
  12. Task 12. Document FX assumption.
    Hint: A11.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Convert USD with ×83.
  2. Total 273900. LO: 150900 → 273900.

Quick quiz

Q1. Copying USD into INR without FX:
Q2. Fixed LO total:
Q3. Broken LO total:
Q4. FX in this workbook:
Q5. Before summing mixed currencies you should: