Help

Re: Nested IF statement returning a blank cell

Solved
Jump to Solution
3301 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniela_Fernand
6 - Interface Innovator
6 - Interface Innovator

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.

1 Solution

Accepted Solutions

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.

See Solution in Thread

6 Replies 6
augmented
10 - Mercury
10 - Mercury

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
6 - Interface Innovator
6 - Interface Innovator

Thank you so much for your help… to your question, Currency is not lookup-based.

Here’s the screenshot:
Screen Shot 2021-11-23 at 17.12.22

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!

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
6 - Interface Innovator
6 - Interface Innovator

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

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.

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!