1

I use a Google Sheet to track ebook sales, some of which are international. Column H below has the royalty amount that was paid out for the sale, but that amount is still shown in the sale currency, as seen in Column F. I'd like to be able to convert the royalty amount to USD using a formula. I found one formula that comes close to what I want...it takes the money amount shown in a cell (e.g. A2) and converts it from one currency to another (GBP to USD in this case).

=A2*GOOGLEFINANCE("CURRENCY:GBPUSD")

However, this formula obviously requires you to enter both the base currency and the conversion currency, and I'd have to change it every time I needed to convert something besides GBP. Since I'll always be converting the sale currency to USD, is there a way to write a similar formula that takes the amount from Column H, looks at the currency listed in Column F, and converts that to USD?

enter image description here

1
1

Please use the following.

=IF(LEN(A2),IFERROR(D2*GOOGLEFINANCE("CURRENCY:"&B2&"USD"&""),D2),)

Copy down till it meets your needs.

enter image description here

Functions used:

5
  • 1
    Must be the fastest answer in webapps. LOL – marikamitsos Dec 19 '20 at 1:58
  • 1
    Haha! Thanks so much for your help! – EJF Dec 19 '20 at 2:00
  • Glad I could help. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Dec 19 '20 at 2:01
  • Will do. Just waiting for the 5 minutes to be up so it actually lets me accept an answer :) – EJF Dec 19 '20 at 2:02
  • 1
    Thanx. This was fun. Stay safe. Happy seasons. – marikamitsos Dec 19 '20 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.