Rounding Results for Comparison with Specification - Pharmaceutical Technology

Latest Issue
PharmTech

Latest Issue
PharmTech Europe

 Pharmaceutical Technology All results
Rounding Results for Comparison with Specification
The mysteries of rounding are exposed.
 Apr 2, 2013 Pharmaceutical Technology Volume 37, Issue 4, pp. 122-124

Rounding using Microsoft Excel

 Table I: results from Excel 2010 with four examples.
Many laboratories use Microsoft Excel for calculations, so it is interesting to see how it performs with the four examples. Excel has three rounding functions: round, round down, and round up. In each function, it is necessary to specify the number to be rounded and the number of decimal places required. The results from Excel 2010 for the examples to 2 decimal places are shown in Table I. The incorrect rounded values are highlighted in yellow.

The round function works well with the exception of example 4. However, Excel can be easily programmed to perform a unbiased rounding decision tree automatically. Suppose the values to be rounded are put starting in cell C9 and the number of decimal places to be rounded to in cell E9.

Excel's trunc function can be used to extract the values of X, Y and Z; using the formulae:

For X = TRUNC(\$C9,\$E9)*10^\$E9-TRUNC(\$C9,\$E9-1)*10^\$E9

For Y = TRUNC(\$C9,\$E9+1)*10^(\$E9+1)-TRUNC(\$C9,\$E9)*10^(\$E9+1)

For Z = \$C9-TRUNC(\$C9,\$E9+1).

These values of X, Y and Z are stored in the relevant rows (9 to 12) of columns G, H, and I. The rounding calculation formula (a nested “if” formula) is now placed in cell J9 for the first example and copied down for rows 10, 11, and 12 for the others:

=IF(\$H9<=4,ROUNDDOWN(\$C9,\$E9),IF(\$H9>=5,

IF(AND(\$H9>=5,\$I9>=0),ROUNDUP(\$C9,\$E9),ROUNDDOWN(\$C9,\$E9))))

 Table II: Results of the unbiased rounding formula using Excel.
The Excel results of the unbiased rounding formula are shown in Table II and agree with the manual evaluation for the four examples.

The mysteries of rounding are exposed here and strict unbiased rounding can be applied.

References

1. USP 35, General Notices 7.20, “Rounding Rules” (US Pharmacopeial Convention, Rockville, MD, 2012).

2. C. Eisenhart, M.W. Hastay, W.A. Wallis, Selected Techniques of Statistical Analysis, (McGraw-Hill, New York, 1947).

| Weekly
| Monthly
|Monthly
| Weekly
 Survey
What do you think the role of continuous (rather than batch) processes in pharmaceutical manufacturing will be over the next five years?
Many companies in the industry will be using continuous processes for some products.
Companies in both pharmaceutical and biopharmaceutical production will be evaluating continuous processes but few will implement.
Only a few companies will be evaluating or implementing; most will stay with batch processing.
Many companies in the industry will be using continuous processes for some products. 34%
Companies in both pharmaceutical and biopharmaceutical production will be evaluating continuous processes but few will implement. 29%
Only a few companies will be evaluating or implementing; most will stay with batch processing. 38%
Most Viewed Articles
 Columnists Outsourcing Outlook Eric LangerNovel Expression Systems Opening CMO Opportunities sponsored by Ingredients Insider Cynthia Challener, PhDSecuring the Global API Supply Chain Regulatory Watch Jill Wechsler FDA Focuses on Drug Appearance and Attributes European Regulatory WatcchSean MilmoEMA Collaborates with HTA Assessment Networks
 UPCOMING CONFERENCES Serialization Summit San Diego, CA Feb. 27-28, 2014 Advances in Aseptic Processing San Diego, CA Mar. 10-12, 2014 ClinTech 2014 Cambridge, MA Mar. 11-13 2014 Investigator-Initiated and Sponsored Research (IISR) Philadelphia, PA Mar. 19-20 2014 See All Conferences >>
 VALIDATION RESOURCES FROM IVT NETWORK Process Validation Special Editions 19th Annual Validation Week Compendium Computer and Software Validation Volume II Special Edition Analytical Method Validation Toolkit More from IVT