Rounding Results for Comparison with Specification - Pharmaceutical Technology

Latest Issue
PharmTech

Latest Issue
PharmTech Europe

Rounding Results for Comparison with Specification
The mysteries of rounding are exposed.


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).


ADVERTISEMENT

blog comments powered by Disqus
LCGC E-mail Newsletters

Subscribe: Click to learn more about the newsletter
| Weekly
| Monthly
|Monthly
| Weekly

Survey
What role should the US government play in the current Ebola outbreak?
Finance development of drugs to treat/prevent disease.
Oversee medical treatment of patients in the US.
Provide treatment for patients globally.
All of the above.
No government involvement in patient treatment or drug development.
Finance development of drugs to treat/prevent disease.
29%
Oversee medical treatment of patients in the US.
10%
Provide treatment for patients globally.
6%
All of the above.
42%
No government involvement in patient treatment or drug development.
13%
Jim Miller Outsourcing Outlook Jim MillerCMO Industry Thins Out
Cynthia Challener, PhD Ingredients Insider Cynthia ChallenerFluorination Remains Key Challenge in API Synthesis
Marilyn E. Morris Guest EditorialMarilyn E. MorrisBolstering Graduate Education and Research Programs
Jill Wechsler Regulatory Watch Jill Wechsler Biopharma Manufacturers Respond to Ebola Crisis
Sean Milmo European Regulatory WatchSean MilmoHarmonizing Marketing Approval of Generic Drugs in Europe
FDA Reorganization to Promote Drug Quality
FDA Readies Quality Metrics Measures
New FDA Team to Spur Modern Drug Manufacturing
From Generics to Supergenerics
CMOs and the Track-and-Trace Race: Are You Engaged Yet?
Source: Pharmaceutical Technology,
Click here