Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Nested IF statement returning a blank cell

Topic Labels: Formulas
Solved
Jump to Solution
4016 6
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!