 # Is it possible to format the decimal points on a Price column based on a parameter?

Want to be able to show 0 decimals IF the Category for the fields row is X, if its not X then show 2 decimals in the number.

The number of decimals seems to apply to the entire column and doesn’t provide for this IF statement.

Possible?

Thanks

Try this in a virtual column app formula to produce a Text value:

``````(
FLOOR([Decimal])
& IFS(
([Decimal] <> DECIMAL(FLOOR([Decimal]))),
(
"."
& RIGHT(
(
"00"
& FLOOR(
(
[Decimal]
- FLOOR([Decimal])
)
* 100
)
),
2
)
)
)
)
``````
1. `FLOOR([Decimal])` gives the whole number component of the Decimal column value.

2. `([Decimal] <> DECIMAL(FLOOR([Decimal])))` asks whether the original Decimal column value (`[Decimal]`) is not equal to the whole number component of that same column value (`FLOOR([Decimal])`). If the two differ, the original value contains a non-zero fractional component.

3. `([Decimal] - FLOOR([Decimal]))` removes the whole component from the Decimal column value, leaving only the fractional component.

4. `(... * 100)` moves the first two digits of the fractional component from (3) to the left of the decimal point.

5. `FLOOR(...)` extracts the whole component of the value from (4).

6. `("00" & ...)` creates a text value from the value from (5) and adds leading zeros to ensure at least two digits in the resulting fractional component.

7. `RIGHT(..., 2)` extracts only the last two digits from the result of (6), trimming excess leading zeros.

8. `("." & ...)` completes the fractional component text by prefixing the value from (7) with a decimal point.

9. `..1 & IFS(..2)` attaches the fractional component text (`..2`)–if generated by (2) through (8)–to the whole component (`..1`).

2 Likes

Got it. Thanks Steve!

1 Like

This is just what I need. Thanks. Too bad it’s not easier to do this in AppSheet.

1 Like

Actually, what I needed was slightly different. I needed to divide [Value A] by [Value B] and have the result show to one decimal point (e.g. “13.3”). I came up with this:

concatenate([Value A]/[Value B],".",right(([Value A]*10)/[Value B]),1))

It works but it’s a little complicated to do this each time. Is this what one needs to do in AppSheet?

@Kirk_Masden Are you able to set the decimal digits to “1”? Then you would not need to do any calculations.

Thanks! I need to make text like “1.5 (3/2)”, so does that mean that need that I need to do this?

If both of your columns “ValueA” and “ValueB” are decimals with one decimal digit, you can write the value with the a TEXT expression like TEXT([ValueA]/[ValueB]) and the result will be with one decimal digit as well.

3 Likes

Thanks! I didn’t know that.

You’re welcome