TargetLynx gives a different calculation result than Excel - WKB332289

SYMPTOMS

• The TargetLynx concentration calculation gives a different result than Excel
  
• Example: TargetLynx reports a concentration of 0.0000000192; Excel reports a concentration of 0.0000000191
• User Factor factors and User Divisor are used for data processing; TargetLynx shows a variation in the results compared with Excel spreadsheet

ENVIRONMENT

• MassLynx
• TargetLynx

CAUSE

All software has limitations on the number of significant figures/decimal places that can be saved and recalled. 
TargetLynx and Excel use differing methods of data storage, resulting in displayed differences, as well as differences due to floating point arithmetic for calculations.

For storing data, TargetLynx uses coded decimal storage. Each field has a specified maximum number of decimal places it can store rather than a number of significant figures. The absolute maximum storage for any TargetLynx value is a number with nine digits to the left of the decimal and ten to the right
(123456789.0123456789).
The maximum you should expect to store is 999999999 and the minimum is 0.0000000001. However, this is the maximum number of figures stored, and some fields have a lower number of digits stored.

This is true of the Reported Concentration field. In the above example, the customer result is 0.0000000192; this reaches the ten decimal places limit, so the calculations are restricted by the significant figures being affected by data storage truncation. 
Calculations should not be used that generate numbers near the data truncation limits.

Note: The reported concentration is also affected by the number of decimal places that are permitted in any field (for example, Sample List fields such as User Factors are limited to four decimal places).

FIX or WORKAROUND

1. Avoid using units that give an excessive number of significant digits either before or after the decimal place. 
   
   
2. Use multipliers (or different units) to work with more significant figures before the decimal place.
   Instead of generating a result such as 0.0000000192 - use a multiplication factor to report, for example, 19.2.

ADDITIONAL INFORMATION

Excel and TargetLynx use floating-point arithmetic. See the Microsoft article Floating-point arithmetic may give inaccurate results in Excel.

Each field in TargetLynx has different data storage settings.
Reported concentration is restricted to a number with nine digits to the left of the decimal and ten to the right (9:10).