🎯

Learning Objectives

Building on Worksheet 1, you will now master:

  • Calculate and interpret measures of dispersion (variability)
  • Apply coefficient of variation for pharmaceutical quality assessment
  • Understand USP compliance requirements for weight variation
  • Create basic visualizations for data interpretation
  • Perform complete pharmaceutical batch analysis
🏭 Real-World Scenario

You've completed the central tendency analysis in Worksheet 1. Now your QC manager wants to understand the variability in the manufacturing process. "Are our tablets consistently close to target, or do we have concerning variations that might indicate process issues?" This worksheet focuses on measuring and interpreting data spread.

📏

Part 1: Measures of Dispersion

Why Dispersion Matters in Pharmaceuticals

🧠 Step-by-Step Reasoning: The Importance of Variability

Step 1: Two batches can have the same mean but very different variability
Step 2: High variability suggests process instability or control issues
Step 3: Regulatory authorities (FDA, EMA) require evidence of consistent manufacturing
Step 4: Patients depend on consistent dosing - variability affects therapeutic outcomes
📊 Example: Two Manufacturing Lines

Line A weights: 549.8, 549.9, 550.0, 550.1, 550.2 mg (Mean = 550.0 mg)

Line B weights: 545.0, 548.0, 550.0, 552.0, 555.0 mg (Mean = 550.0 mg)

Question: Which line would you prefer for production? Why?

Range - The Simplest Measure

🧠 Step-by-Step: Calculating Range

Step 1: Understand the Concept
Range shows the total spread from lowest to highest value
Step 2: Find Maximum and Minimum
From our tablet weight data (Worksheet 1):
Maximum = 552.8 mg, Minimum = 548.2 mg
Step 3: Calculate Range
Range = Maximum - Minimum = 552.8 - 548.2 = 4.6 mg
Step 4: Interpret the Result
The tablets vary by 4.6 mg across the entire batch

📐 Range Formula

Range = Maximum Value - Minimum Value
💻 Excel Implementation

=MAX(range) - MIN(range)

Example: =MAX(A1:F5) - MIN(A1:F5)

Limitation: Range only considers extreme values and ignores the distribution of middle values. One outlier can make range misleading.

Standard Deviation - The Gold Standard

🧠 Step-by-Step: Understanding Standard Deviation

Step 1: The Concept
Standard deviation measures average distance of values from the mean
Think of it as "typical deviation from target"
Step 2: Calculate Deviations
For each value, find: (Value - Mean)
Example: If value = 548.2 and mean = 550.29
Deviation = 548.2 - 550.29 = -2.09 mg
Step 3: Square the Deviations
Squaring removes negative signs and emphasizes larger deviations
(-2.09)² = 4.37 mg²
Step 4: Average the Squared Deviations
Sum all squared deviations ÷ (n-1) = Variance
Using n-1 gives us the sample standard deviation
Step 5: Take Square Root
√Variance = Standard Deviation
This returns us to original units (mg)

📐 Standard Deviation Formula

s = √[Σ(x - x̄)² / (n-1)]

Where: s = sample standard deviation, x̄ = mean, n = sample size

📋 Worked Example: Standard Deviation Calculation

Let's calculate standard deviation for a subset of our tablet weights:

Weight (x) Mean (x̄) Deviation (x - x̄) (x - x̄)²
548.2 550.29 -2.09 4.37
552.1 550.29 1.81 3.28
549.8 550.29 -0.49 0.24
551.3 550.29 1.01 1.02
550.7 550.29 0.41 0.17
Sum of (x - x̄)² 9.08
Variance Calculation:
s² = Σ(x - x̄)² / (n-1)
s² = 9.08 / (5-1)
s² = 9.08 / 4 = 2.27 mg²
Standard Deviation:
s = √(variance)
s = √2.27
s = 1.51 mg
💻 Excel Functions

=STDEV.S(range) - Sample standard deviation

=STDEV.P(range) - Population standard deviation

=VAR.S(range) - Sample variance

Note: Use STDEV.S for batch data (sample from larger population)

🔥 Exercise 1.1: Calculate Standard Deviation

Calculate the standard deviation for this dissolution data (% dissolved at 15 minutes):

Dissolution Data (%)
72.875.273.974.1

Step 1: Calculate the mean: _______________

Step 2: Complete the deviation table:

x x - x̄ (x - x̄)²
72.8 _____ _____
75.2 _____ _____
73.9 _____ _____
74.1 _____ _____
Sum 0 _____

Step 3: Variance = Σ(x - x̄)² / (n-1) = _____ / 3 = _____

Step 4: Standard Deviation = √(variance) = _____

Excel Formula: =STDEV.S(______)

📈

Part 2: Coefficient of Variation (CV%)

The Relative Measure of Variability

🧠 Step-by-Step: Why CV% is Crucial

Step 1: The Problem with Standard Deviation
Standard deviation is in original units - hard to compare different measurements
Is 1.5 mg variation in weight the same as 1.5% variation in assay?
Step 2: The Solution - Relative Variability
CV% expresses standard deviation as a percentage of the mean
This allows comparison across different types of measurements
Step 3: Pharmaceutical Applications
USP and ICH guidelines often specify CV% limits
Common acceptance criteria: CV% < 2% for weight, < 5% for assay
Step 4: Interpretation
Lower CV% = More consistent process
Higher CV% = More variable process (needs investigation)

📐 Coefficient of Variation Formula

CV% = (Standard Deviation / Mean) × 100
CV% = (s / x̄) × 100

📋 Worked Example: CV% Calculation

Using our tablet weight data from earlier:

Given Values:
Mean (x̄) = 550.29 mg
Standard Deviation (s) = 1.51 mg
(from our previous calculations)
CV% Calculation:
CV% = (s / x̄) × 100
CV% = (1.51 / 550.29) × 100
CV% = 0.274%
💻 Excel Implementation

=(STDEV.S(range)/AVERAGE(range))*100

Example: =(STDEV.S(A1:F5)/AVERAGE(A1:F5))*100

📋 USP Acceptance Criteria

Weight Variation (USP <905>):

  • For tablets ≥ 324 mg: No individual weight deviates by more than ±5% from average
  • CV% typically should be < 2% for good manufacturing control
  • If CV% > 3%, investigate process parameters

🔥 Exercise 2.1: CV% Analysis and Compliance

You receive assay results for content uniformity testing. Calculate CV% and assess compliance:

Assay Results (% of labeled claim)
98.9101.299.7100.899.1
100.398.6101.199.8100.5

Calculate step-by-step:

1. Mean = _______________

2. Standard Deviation = _______________

3. CV% = (SD / Mean) × 100 = _______________


Excel formulas:

Mean: _______________

STDEV: _______________

CV%: _______________


Compliance Assessment:

Is CV% < 5%? (Circle: YES / NO)

Are all individual values within 85-115%? (Circle: YES / NO)

Overall conclusion: _______________

📊

Part 3: Quartiles and Outlier Detection

Understanding Data Distribution with Quartiles

🧠 Step-by-Step: Quartile Concepts

Step 1: What Are Quartiles?
Quartiles divide sorted data into four equal parts
Q1 = 25th percentile, Q2 = 50th percentile (median), Q3 = 75th percentile
Step 2: Interquartile Range (IQR)
IQR = Q3 - Q1
Shows spread of middle 50% of data (less affected by outliers)
Step 3: Outlier Detection
Values beyond Q1 - 1.5×IQR or Q3 + 1.5×IQR are potential outliers
This is the "1.5 × IQR rule"
Step 4: Pharmaceutical Application
Outliers might indicate equipment malfunction, operator error, or raw material issues

📐 Quartile and Outlier Formulas

IQR = Q3 - Q1
Lower Outlier Boundary = Q1 - 1.5 × IQR
Upper Outlier Boundary = Q3 + 1.5 × IQR

📋 Worked Example: Outlier Analysis

Let's analyze a tablet hardness dataset for outliers:

Data (sorted): 8.2, 8.5, 8.7, 8.8, 9.0, 9.1, 9.2, 9.3, 9.4, 9.6, 12.1 kP

Find Quartiles:
Q1 position = (11+1)/4 = 3rd value
Q1 = 8.7 kP
Q3 position = 3×(11+1)/4 = 9th value
Q3 = 9.4 kP
Calculate IQR:
IQR = Q3 - Q1
IQR = 9.4 - 8.7 = 0.7 kP
1.5 × IQR = 1.5 × 0.7 = 1.05 kP
Lower Boundary:
Q1 - 1.5×IQR
8.7 - 1.05 = 7.65 kP
(No values below this)
Upper Boundary:
Q3 + 1.5×IQR
9.4 + 1.05 = 10.45 kP
(12.1 kP exceeds this limit)
Outlier Detected: The value 12.1 kP exceeds the upper boundary (10.45 kP) and should be investigated. Possible causes: damaged tablet, incorrect measurement, equipment calibration issue.
💻 Excel Functions for Quartiles

=QUARTILE.INC(range, 1) - First quartile (Q1)

=QUARTILE.INC(range, 2) - Second quartile (Q2, median)

=QUARTILE.INC(range, 3) - Third quartile (Q3)

=QUARTILE.INC(range, 3) - QUARTILE.INC(range, 1) - IQR

🔥 Exercise 3.1: Complete Outlier Analysis

Analyze this particle size data (micrometers) for outliers:

Raw Data: 45.2, 47.8, 48.1, 48.9, 49.2, 49.7, 50.1, 50.8, 51.2, 65.3

Step 1: Sort the data (if needed): _______________

Step 2: Find quartiles:

Q1 (position ___): _______________

Q3 (position ___): _______________

Step 3: Calculate IQR = Q3 - Q1 = _______________

Step 4: Calculate boundaries:

Lower boundary = Q1 - 1.5×IQR = _______________

Upper boundary = Q3 + 1.5×IQR = _______________

Step 5: Identify outliers: _______________

Excel formulas:

Q1: _______________

Q3: _______________

IQR: _______________

🏭

Part 4: Complete Batch Analysis Project

Comprehensive Quality Assessment

🎯 Project Scenario

You're conducting a complete quality assessment of three tablet batches (A, B, C) before release. Your manager needs a comprehensive report including central tendency, variability, and compliance assessment.

📊 Batch Data: Weight Variation (mg)

Tablet # Batch A Batch B Batch C
1248.7251.2249.8
2250.1248.9250.3
3249.8252.1248.9
4250.3247.8251.1
5249.6253.0249.7
6250.0249.7250.2
7249.9251.8250.8
8250.2248.3249.5
9249.7252.5250.6
10250.4250.1249.4
📋 USP Weight Variation Requirements

For tablets weighing 250 mg (average):

  • No tablet should deviate by more than ±7.5% from average weight
  • Acceptance range: 231.25 mg to 268.75 mg
  • Target CV%: < 2% for excellent control, < 3% acceptable

🔥 Final Exercise: Complete Batch Analysis Report

Complete the comprehensive analysis for all three batches:

BATCH A ANALYSIS

Central Tendency:

Mean = _____ mg | Median = _____ mg | Mode = _____

Dispersion:

Range = _____ mg | Standard Deviation = _____ mg | CV% = _____%

Quartiles & Outliers:

Q1 = _____ mg | Q3 = _____ mg | IQR = _____ mg

Outlier boundaries: _____ to _____ mg | Outliers detected: _____

USP Compliance:

Individual weights within ±7.5%? (Circle: YES / NO)

CV% acceptable? (Circle: YES / NO)

BATCH B ANALYSIS

Central Tendency:

Mean = _____ mg | Median = _____ mg | Mode = _____

Dispersion:

Range = _____ mg | Standard Deviation = _____ mg | CV% = _____%

USP Compliance:

Individual weights within ±7.5%? (Circle: YES / NO)

CV% acceptable? (Circle: YES / NO)

BATCH C ANALYSIS

Central Tendency:

Mean = _____ mg | Median = _____ mg | Mode = _____

Dispersion:

Range = _____ mg | Standard Deviation = _____ mg | CV% = _____%

USP Compliance:

Individual weights within ±7.5%? (Circle: YES / NO)

CV% acceptable? (Circle: YES / NO)

FINAL RECOMMENDATION

Best performing batch: _____ (justify with statistics)

Batches approved for release: _____

Batches requiring investigation: _____

Key findings and recommendations:

_________________________________________________

_________________________________________________

Professional Tip: In real pharmaceutical QC, always document your statistical analysis with Excel files, include control charts for trending, and maintain detailed investigation records for any out-of-specification results.
← Previous: Worksheet 1 📋 View Solutions