# HELP! Formatting Decimals in Formulas

Topic Labels: Formulas
Solved
632 5
cancel
Showing results for
Did you mean:  6 - Interface Innovator

I am trying to format decimals in a formula to no avail: • To the left is the number
• the 3rd column is what happens when this number is used in a formula (the end is cut off for numbers ending in 0) (e.g. 12.50 > £12.5
• middle column is where I have formatted the number with this formula:

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:

1 Solution

Accepted Solutions  15 - Saturn

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"
)
)
`````` 5 Replies 5  15 - Saturn

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"
)
)
``````   6 - Interface Innovator

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? • middle is formatted number using:

ROUND({PRICE PER}, 2)

But once in a another formula it cuts off the 0

1. The far right column is using your formula above

Thanks again!  15 - Saturn

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.   6 - Interface Innovator

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: • Left column is amount formatted with:

ROUND({PRICE PER}, 2)

• Right column is your formula applied using the above already rounded number:

IF(
Amount,
IF(
FIND(".", Amount & “”),
IF(
LEN(Amount & “”) - FIND(".", Amount & “”) = 1,
Amount & “0”,
Amount
),
Amount & “.00”
)
)

• Middle column is now pulling the finalised “double formatted” number.

Thanks so much for all your help!  15 - Saturn

No worries, glad I could help! 