Numeric format examples

The following table contains some sample format expressions for numbers that apply to SPC Data Entry, DMS Data Entry, SPC Charts and Reports, DMS Charts and Reports, and Enterprise Dashboard modules.

The first column contains the format strings that can be used with the FORMAT command; the other columns contain the resulting output for various data values and regional options.

Data to be formatted  
 

Positive 5

Negative 5

Decimal .5

Positive 5

Negative 5

Decimal .5

Format string in template formula  

Output when regional options are set to English (United States):

Output when regional options are set to Italian (Italy):

Zero-length string ("")

5

-5

0.5

5

-5

0,5

0

5

-5

1

5

-5

1

0.00

5.00

-5.00

0.50

5,00

-5,00

0,50

#,##0

5

-5

1

5

-5

1

#,##0.00

5.00

-5.00

0.50

5,00

-5,00

0,50

$#,##0;($#,##0)

$5

($5)

$1

$5

($5)

$1

$#,##0.00;($#,##0.00)

$5.00

($5.00)

$0.50

$5,00

($5,00)

$0,50

\€ #,##0;(\€ #,##0)

€ 5

(€ 5)

€ 1

€ 5

(€ 5)

€ 1

\€ #,##0.00;(\€ #,##0.00)

€ 5.00

(€ 5.00)

€ 0.50

€ 5,00

(€ 5,00)

€ 0,50

Currency

$5.00

($5.00)

$0.50

€ 5,00

-€ 5,00

€ 0,50

0%

500%

-500%

50%

500%

-500%

50%

0.00%

500.00%

-500.00%

50.00%

500,00%

-500,00%

50,00%

0.00E+00

5.00E+00

-5.00E+00

5.00E-01

5,00E+00

-5,00E+00

5,00E-01

0.00E-00

5.00E00

-5.00E00

5.00E-01

5,00E00

-5,00E00

5,00E-01

The following table contains template examples of formatting numeric values:

 

Template formula

Output when regional options are set to
English (United States)

Output when regional options are set to Italian (Italy)

Round a value to 2 decimal places

FORMAT 1.234, 'fixed'

1.23

1,23

FORMAT 1.237, 'fixed'

1.24

1,24

FORMAT 5861.237, '##.##'

5861.24

5861,24

Format a value to a specific number of
digits with the digit grouping symbol

FORMAT 5861.237, '000,000.00000'

005,861.23700

005.861,23700

Format a value to a specific number of
digits without the digit grouping symbol

FORMAT 5861.237, '000000.00000'

005861.23700

005861,23700

You should use caution when formatting a numeric string (a string value containing a number), particularly when regional options are configured with the digit grouping symbol as a comma (,) and/or the decimal symbol as a period (.).  The following table displays where problems occur and how you can use the LOCAL_S command to avoid them:

Template formula

Output when regional options are set to
English (United States)

Output when regional options are set to Italian (Italy)

FORMAT '5861.237', 'fixed'

5861.24

5861237,00

FORMAT '5,861.237', 'fixed'

5861.24

5,86

FORMAT '5.861,237', 'fixed'

5.86

5861,24

FORMAT (LOCAL_S '5,861.237', '.', ','), 'fixed'

5861.24

5861,24

FORMAT (LOCAL_S '5.861,237', ',', '.'), 'fixed'

5861.24

5861,24

 

More:

Numeric formats