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

Improve this question
1
  • 1
    Finally. Good job.
    – marikamitsos
    Commented Dec 19, 2020 at 1:53

1 Answer 1

Reset to default
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:

Improve this answer
5
  • 1
    Must be the fastest answer in webapps. LOL
    – marikamitsos
    Commented Dec 19, 2020 at 1:58
  • 1
    Haha! Thanks so much for your help!
    – EJF
    Commented Dec 19, 2020 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
    Commented Dec 19, 2020 at 2:01
  • Will do. Just waiting for the 5 minutes to be up so it actually lets me accept an answer :)
    – EJF
    Commented Dec 19, 2020 at 2:02
  • 1
    Thanx. This was fun. Stay safe. Happy seasons.
    – marikamitsos
    Commented Dec 19, 2020 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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