Help

HELP! Formatting Decimals in Formulas

Topic Labels: Formulas
Solved
Jump to Solution
2137 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Cassy_Amelia
6 - Interface Innovator
6 - Interface Innovator

I am trying to format decimals in a formula to no avail:

image

  • 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
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

Screenshot 2022-06-19 at 2.00.46 PM

See Solution in Thread

6 Replies 6
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

Screenshot 2022-06-19 at 2.00.46 PM

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?

image

  • 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.

Screenshot 2022-06-20 at 2.43.02 PM

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:

image

  • 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!

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