Nested IF statement returning a blank cell

[Daniela_Fernand]

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.

[augmented]

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.

[Daniela_Fernand]

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!

[augmented]

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.

[Daniela_Fernand]

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

[Justin_Barrett · Accepted solution]

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”.

Daniela_Fernandez:

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)))

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.

[Daniela_Fernand]

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!