HELP! Formatting Decimals in Formulas

[Cassy_Amelia]

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:

[TheTimeSavingCo · Accepted solution]

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

[Cassy_Amelia]

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!

[TheTimeSavingCo]

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.

[Cassy_Amelia]

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!

[TheTimeSavingCo]

No worries, glad I could help!

[peteb]

It is absolutely insane that this is the solution to getting formula results to display decimal places