Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
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.
Nov 27, 2020 01:02 PM
Hi @martinAt - a few pointers for you:
IF(
SEARCH('BALANCE-', {TransferWiseID}),
0,
Amount * ExchangeRate
)
Now, when the result of the formula is an integer it still displays to 2 dp:
"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.Nov 27, 2020 01:02 PM
Hi @martinAt - a few pointers for you:
IF(
SEARCH('BALANCE-', {TransferWiseID}),
0,
Amount * ExchangeRate
)
Now, when the result of the formula is an integer it still displays to 2 dp:
"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.Nov 27, 2020 01:29 PM
Hi Jonathan,
Thank you for your reply!
In reply to your questions:
Thanks once again and I hope my response has been useful to any other users that come across this issue!
Regards,
Martin
Nov 27, 2020 01:38 PM
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.
Nov 27, 2020 03:53 PM
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.