Is it possible to format the decimal points on a P...

Daisy_Ramirez · 07-02-2019 10:34 AM

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

Steve

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

FLOOR([Decimal]) gives the whole number component of the Decimal column value.
([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.
([Decimal] - FLOOR([Decimal])) removes the whole component from the Decimal column value, leaving only the fractional component.
(... * 100) moves the first two digits of the fractional component from (3) to the left of the decimal point.
FLOOR(...) extracts the whole component of the value from (4).
("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.
RIGHT(..., 2) extracts only the last two digits from the result of (6), trimming excess leading zeros.
("." & ...) completes the fractional component text by prefixing the value from (7) with a decimal point.
..1 & IFS(..2) attaches the fractional component text (..2)–if generated by (2) through (8)–to the whole component (..1).

Daisy_Ramirez

Got it. Thanks Steve!

Kirk_Masden

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

Kirk_Masden

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?

Aleksi

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

Kirk_Masden

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

Aleksi

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.

Kirk_Masden

Thanks! I didn’t know that.

Aleksi

You’re welcome

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