Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 18, 2022 10:59 AM
I am trying to format decimals in a formula to no avail:
so this was a solution found on another thread:
IF({Refill / lowest price}, “£” & {Refill / lowest price} & IF(FIND(".", {Refill / lowest price} & “”) = LEN({Refill / lowest price} & “”) - 1, “0”))
However, if you now look at the middle column it works for the £12.50, but for any round numbers under 10 it misses the decimal place. (i.e. 5.00 > £50.
Any help with tweaking this formula would be greatly appreciated!
THANKS! :grinning_face_with_big_eyes:
Solved! Go to Solution.
Jun 18, 2022 11:01 PM
Hi Cassy, could you try the following formula and see if it works for you?
IF(
Amount,
IF(
FIND(".", Amount & ""),
IF(
LEN(Amount & "") - FIND(".", Amount & "") = 1,
Amount & "0",
Amount
),
Amount & ".00"
)
)
Jun 18, 2022 11:01 PM
Hi Cassy, could you try the following formula and see if it works for you?
IF(
Amount,
IF(
FIND(".", Amount & ""),
IF(
LEN(Amount & "") - FIND(".", Amount & "") = 1,
Amount & "0",
Amount
),
Amount & ".00"
)
)
Jun 19, 2022 05:06 AM
Thank you so much! That worked! :grinning_face_with_big_eyes:
I have used the same formula in another area, and wondered if you could help with this too please?
ROUND({PRICE PER}, 2)
But once in a another formula it cuts off the 0
Thanks again!
Jun 19, 2022 11:44 PM
Hi Cassy, hmm, could you share a link to your base with me so I can try to troubleshoot it?
I’ve tried to replicate your setup here but it seems to be working fine for me.
Jun 20, 2022 03:43 AM
Adam, you’re a star, it works! I was choosing to either use the ROUND function OR your formula, but I realised it requires both:
ROUND({PRICE PER}, 2)
IF(
Amount,
IF(
FIND(".", Amount & “”),
IF(
LEN(Amount & “”) - FIND(".", Amount & “”) = 1,
Amount & “0”,
Amount
),
Amount & “.00”
)
)
Thanks so much for all your help!
Jun 20, 2022 04:45 AM
No worries, glad I could help!
Dec 13, 2023 12:16 PM
It is absolutely insane that this is the solution to getting formula results to display decimal places