Functions to customize number format
I have a calculation which returns either a numeric value (e.g. "$50.3M") or a textual value (e.g. "No Holding"). However, I cannot seem to format the numbers as freely as I'd like. Currently, I'm using the string:
"$" & Round ( Number ; 1 ) & "M"
which works most of the time, but if the value is 50, the returned result is "$50M" and not "$50.0M" as I would like it to be. Similarly, if the value is 0.5, the returned result is "$.5M" and not "$0.5M."
Is there a way to tell force FileMaker to include the zeroes? Also, is there a way to tell FileMaker to insert the commas? There's a (small) chance that the value returned would be over 1 billion and therefore be shown as $1000M.