Skip to main content

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:

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"

  )

)

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"

  )

)


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!

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!


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.


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.



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!

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!


No worries, glad I could help!

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"

  )

)


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

Reply


Terms & Conditions