Decimal places being removed in formula + Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date

[Cassy_Amelia]

Hi,

I am using this formula and it isn’t implementing the decimal places from my {Refill / lowest price} column, which in itself is set to 2 decimal places:

"Lowest cost per " & {Unit} & " based on: " & “£” & {Refill / lowest price} & " for "& {Lowest cost option}

It is bringing back things like the following;

“Lowest cost per wash based on: £5.3 for 24 capsules (fragranced)”

If I go to formatting it gives me “Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”

But is there any way to ensure it ‘pulls’ the number with both decimal places?

Thank you!

[Rupert_Hoffsch1]

Hi Cassy, what field type is the {Refill / lowest price} ?

[Cassy_Amelia]

Hey Rupert!

It’s a # Number field with precision set to Decimal 1.00 precision.

Thanks for your help!

[Rupert_Hoffsch1]

I’m not able to replicate this, for me it’s showing correctly. Can you send a screenshot of the formulas and the fields?

[Cassy_Amelia]

Sure :slightly_smiling_face:

Thanks again!

[Cassy_Amelia]

It seems to just be for the prices ending in 0 i.e:

£5.30 > £5.3
£5.80 > £5.8

[Rupert_Hoffsch1]

This earlier post should help you: Missing zero in currency data

[Cassy_Amelia]

Legend! Thank you so much!

I formatted the number as suggested in that post above, and then used that in the formula and it’s worked perfectly!

[Cassy_Amelia]

Just realised something…

The formula provided before in that post actually removes the .00 for rounded numbers under the value of £10. i.e £5.00 becomes £50

IF(Number, "£" & Number & IF(FIND(".", Number & "") = LEN(Number & "") - 1, "0"))

Is there something I can do to tweak the formula?

Really appreciate your help with this!

[Cassy_Amelia]

Side note: here is a clearer view of the formula result: