Number formatting using custom fields

Custom field functions and field formatting options can be used to format numeric values in a variety of ways.  For example, here the data contains ‘value’ and ‘appearance’ is at it should be displayed:

14000001.4 Million
62000006.2 Million
473000000473 Million
4000000040 Million
4500000045 Million
71000000007.1 Billion
22000000002.2 Billion
1580000015.8 Million


The field ‘value’ would need to use a ‘custom’ format, to force the use of comma for thousands, like this:

Then a custom field called “appearance” is created using the following command:

IF(FIELDVAL(value), >, 999999999.999,CONCAT(ROUNDVAL(DIV(FIELDVAL(value),1000000000),2,TRUE), ' Billion'),IF(FIELDVAL(value), >, 999999.999,CONCAT(ROUNDVAL(DIV(FIELDVAL(value),1000000),2,TRUE), ' Million'),FIELDSTR(value)))