Duration Field not displaying zero values

[martinAt]

Hi all, thank you in advance for any help!

I have 3x columns of interest in my Airtable which contains TransferWise statement information which is copied and pasted by me (from TransferWise to Airtable) on a regular basis. These columns are:

{TransferWiseID}
{ExchangeRate}
{Amount}

I also have a formula column which is:
{ExchangeRate*Amount}

Below is the formula that I am currently using with some success. I am happy that if {TransferWiseID} contains “BALANCE-” then 0.00 is populated into the formula column. This is working fine.

Where I am stuck is, is if {TransferWiseID} contacts “CARD-” or “TRANSFER-” the formula rounds to 2 decimal places as it should, but only if it is not a round number.

What I really want it to do however, is round to 2 decimal places consistently. E.g., right now, if a total is 10, then the formula column displays 10, however, I would like it to display 10.00.

Can anyone suggest any tweaks to my formula below to make this happen?

Thank you!
Martin

IF(
    SEARCH("BALANCE-", {TransferWiseID}),
    "0.00",
    IF(
        SEARCH("CARD-", {TransferWiseID}),
        ROUND({Amount} * {ExchangeRate}, 2),
        IF(
            SEARCH("TRANSFER-", {TransferWiseID}),
            ROUND({Amount} * {ExchangeRate}, 2)
        )
    )
)

[JonathanBowen · Accepted solution]

Hi @martinAt - a few pointers for you:

• You can simplify the formula a bit as the last two conditions result in the same formula, so you really only need to say “If ID contains “Balance”, then zero, otherwise Amount * Exch Rate”. So something like this:

IF(
  SEARCH('BALANCE-', {TransferWiseID}),
  0, 
  Amount * ExchangeRate
)

• I’m assuming that the exchange rate and amount fields are numbers - is that right? If so, you don’t need to do the rounding, just use the formula field format to always show 2 dp:

Now, when the result of the formula is an integer it still displays to 2 dp:

• Final point - the first part of your formula returns "0.00" which is a string not a number. Generally it is better for numbers to return as numbers so that you can use them in other calculations. Your use case might need this value as a string of course.

[martinAt]

Hi Jonathan,

Thank you for your reply!

In reply to your questions:

• Firstly the formula you provided worked perfectly!
• The {Amount} and {ExchangeRate} columns are both a Number ‘Type’, correct.
• The {Amount} column had a decimal ‘Format’ (rather than an integer ‘Format’) with a ‘1.00’ precision. [Whereas the {ExchangeRate} column had a ‘1.00000’ precision].
• The issue I had with my formula was that the ‘Format’ tab disappeared – so I could no longer select a format/precision for the formula column. Do you know why this disappeared when using my formula, out of interest?
• As soon as I entered your formula, the ‘Format’ tab re-appeared for the formula column.
• On your final point for the first part of the formula if {TransferWiseID} column contains ‘BALANCE-’, it returns 0.00 as a string instead of a number, this is fine for my use-case because this data does not need to be sent to Xero.

Thanks once again and I hope my response has been useful to any other users that come across this issue!

Regards,
Martin

[JonathanBowen]

martinAt:

The issue I had with my formula was that the ‘Format’ tab disappeared

Yes, thinking about it, the reason the format disappeared was because of the string "0.00". You have one part of the formula returning a string and another returning a number, so a mixed result cannot be formatted. Making the “0.00” a number rather than a string means the result is not mixed and the format tab returns.

[martinAt]

Thanks Jonathan, that makes sense. I found some advice on here relating to this, but I couldn’t find any clear adjustments to my formula. I am not a ‘tech’ guy by any means, but I am trying to learn to build what I need to – by trial and error! Have a great weekend. Cheers again, Martin.