Result Value Formatting for Custom Numbers

The Report Format field on the Analyses setup screen enables formatting analysis results with various options including decimal place and significant figure rounding. However, the formatting options available are not limited to the field’s pick list, which only provides commonly used formats.

The Report Format field allows any of the formatting options supported by the Visual Basic for Applications (VBA) Format function. To see a list of the predefined named date and number formats and user-defined format options, open Excel, use Alt+F11 to open the VBA editor, use F1 to open Excel VBA Help, search for “Format” then select the “Format Function” topic.

The image below shows the available named numeric formats, some of which you will already find in the Report Format field’s pick list. To test a named numeric format, open the Analyses setup screen, switch to edit mode and select any analyte, then click the [Test…] button adjacent to the Report Format field. In the Result Report Format popup screen enter “Currency” in the Format field. Since currency is not a numeric format commonly used in a LIMS it is omitted from the pick list. However, with the example values you will see that since it is a valid named numeric format option for VBA’s Format function it will work properly in LIMS. Try the “Percent” named numeric format as well.

User-defined numeric formats use characters for digit and decimal placeholders, thousands separator, and literal characters. For example, both “0” and “#” are digit placeholders. A “0” displays a digit, or a zero if the number does not have a digit in the position where the zero appears in the format. A “#” displays a digit, or nothing if the number does not have a digit in the position where the “#” character appears.

Custom number formats can include literal characters. Use this capability to create formatted results for results you cannot enter directly. For example, if a manufacturer’s lot numbers are listed as two digits followed by a dash and three digits, use a report format of “00-000” and enter only the digits for the result value. That is, enter a result value of 12345 with this user-defined format to report the lot number as 12-345. If lot numbers are always prefaced with the letter “L” use “L00-000” for the report format to produce L12-345.

An often overlooked feature of user-defined numeric formats is the option to include different formats for positive, negative, and zero values. These are created by separating the formats with a semicolon (;) in the report format field. Following our current example, assume lot numbers are identified with either a leading “A” or “B” depending on the manufacturing plant. With only two lot number formats, we can use different positive and negative formats to create the lot number. A positive result value is used for “A” lot numbers and a negative value for “B” lot numbers. We can also use a zero result to indicate no lot number. The screen below shows the format with example result values.

If more than two distinct options are necessary for lot identification characters, result types can be used with result values. The table below shows results for example result types using a “00-000” analysis report format.

Note that the VBA Format function also supports named and user-defined date and time formats. See “Using Date and Time Analyses” KB article for more information. With user-defined analysis report formats, capturing and reporting data with special-purpose analytes is a viable option.