Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Adding 2x zeros (e.g. 10.00) to a formula, IF the ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1579
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 11:12 AM

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

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 01:02 PM

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.

4 Replies 4

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 01:02 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 01:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 01:38 PM

`"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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 27, 2020 03:53 PM