
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2021 11:15 AM
I’m working on a table where I have different currencies (in separate columns) and need to convert them all to one currency. I have a column marking the currency used and so the conditional if statement uses this to have a different calculation for each statement. However, for some reason, the formula doesn’t work but it does not return an error, it simply returns a blank Cell, so I have no idea what I’m doing wrong.
Please HELP.
IF({Currency} = “MXN”, {Amount in MXN}/{FX}, IF({Currency} = “EUR”, {Amount in EUR}*{FX}, IF({Currency} = “USD”,{Amount in USD})))
I know about the Currency converter app, but due to the Terms and conditions/privacy settings, I can’t use it.
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 24, 2021 10:04 AM
Check your {Currency}
field options to see if you have a space either before or after any of the currency abbreviations. Leading/trailing spaces may or may not appear when selecting the items, but they definitely impact comparisons in formulas because " MXN" or "MXN " are not the same as “MXN”.
The reason that’s not working is because there’s a space between “if” and its opening parenthesis in the second function. In short, IF(
is valid, but IF (
is not.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2021 11:34 AM
Hi Daniela. If you’re getting an empty cell, none of your IF conditions are returning true - meaning {Currency} is not a string of “MXN”, “EUR”, or “USD”. Would you mind taking a screenshot of your table header and a few rows?
I also notice that you’re using {FX} for two different calculations. Is it a lookup based on {Currency}? Given that I don’t know what kind of field {Currency} is, you could try adding &"" to it in your formula to see if you just needed to convert it to a string.
IF({Currency}&'' = 'MXN'
I like to use single quotes to keep me out of copy/paste troubles.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2021 03:21 PM
Thank you so much for your help… to your question, Currency is not lookup-based.
Here’s the screenshot:
This is what I currently have as a formula for the COnvertes amount in USD Column, but now it does mark it as an INVALID formula:
if({Currency} = ‘USD’, {Amount in USD}, if ({Currency} = ‘EUR’, {Amount in EUR}/{USD FX - original}, if({Currency} = ‘MXN’, {Amount in MXN}/{USD FX - original}, 0)))
This is my other option which it is a valid formula but it again return every cell blank:
SWITCH({Currency}, ‘USD’, {Amount in USD}, ‘MXN’, {Amount in MXN}/{USD FX - original}, ‘EUR’, {Amount in EUR}/{USD FX - original})
Thank you so much!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 23, 2021 04:33 PM
So I think I see the issue, Daniela. Your {Currency} field is a multi-select. When you use it in a formula, it is by default an array. Therefore, you need to make it a string before you compare it to ‘USD’, ‘MXN’, or ‘EUR’. (Or, make your multi-select a single select).
That was my suggestion to concatenate it with an empty string, first. See my first reply. You can use it in the SWITCH in the same way.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 24, 2021 08:35 AM
Thank you Augmented… You’re right! I’ve changed {Currency} to single select…
IF({Currency} = “USD”, {Amount in USD}, IF({Currency} = “EUR”, {Amount in EUR}/{USD FX - original}, IF({Currency} = “MXN”, {Amount in MXN}/{USD FX - original}, 0)))
Now the whole column returns FALSE, showing $0.00

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 24, 2021 10:04 AM
Check your {Currency}
field options to see if you have a space either before or after any of the currency abbreviations. Leading/trailing spaces may or may not appear when selecting the items, but they definitely impact comparisons in formulas because " MXN" or "MXN " are not the same as “MXN”.
The reason that’s not working is because there’s a space between “if” and its opening parenthesis in the second function. In short, IF(
is valid, but IF (
is not.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 24, 2021 10:48 AM
this solved it!!! thank you so much! I was going crazy!..
Of course, I had a space in the currency abbreviations!
Thank you both for your help!
