Eight tasks, one worksheet each

Core weights for twenty tablets from batch PT-2601, target 550.0 mg. The extract was pasted out of a report, and two of the weights came across as text. Excel will not count them, and it will not tell you.

Do this

1. Select B7 and read the Name Box and Formula Bar.
2. Freeze the top row so the headings stay visible (View > Freeze Panes).
3. AutoFit columns A and B (Home > Format > AutoFit Column Width).
4. Find the two weights stored as text and repair them. Hint: look at which cells are left-aligned.
5. Apply one decimal place to the whole weight column.
6. Select B7:B26 and read the count and average off the Status Bar.
7. Answer the three questions in the EDIT cells.

Two blocks. First, deviation from a single target weight — the classic case for locking a reference. Then a dilution grid, where one formula has to fill in two directions at once.

Block A — deviation from target

1. In C9, calculate the deviation of the tablet weight from the target in C5.
2. In D9, calculate the weight as a percentage of target. Format it as a percentage.
3. Fill both down to row 18. If the answers drift as you fill, the target reference is not locked.

Block B — the dilution grid

1. Stock concentrations run down column G; dilution factors run across row 9.
2. Write one formula in H11 that divides the stock by the dilution factor, then fill it across to K and down to row 14.
3. You will need one reference with its column locked and another with its row locked.

Uniformity of content for two batches, ten units each, as a percentage of label claim. PT-2601 was released and PT-2602 was not. Make the spreadsheet say why. The in-house criterion for this exercise: every unit between 85.0% and 115.0%, and %RSD no greater than 6.0%.

Do this

1. Label each unit Within or Outside using IF with AND. Fill down both columns.
2. In the summary block, calculate the mean, standard deviation and %RSD for each batch.
3. Count the units outside the 85–115% window with COUNTIFS.
4. Write a verdict formula that returns Pass or Fail from those two results.

A batch formulation lists raw material codes; you need the material name, function and supplier pulled across from the approved-materials table. Then six blends need banding by flow character from their Carr's index — a different kind of lookup entirely.

Block A — exact match

1. Use XLOOKUP to return the material, function and supplier for each code. Lock the lookup and return ranges so the formula fills down cleanly.
2. One code is not on the approved list. Use the fourth argument so that cell reads Not approved rather than #N/A.

Block B — banded match

1. Calculate Carr's index for each blend: (tapped − bulk) ÷ tapped × 100.
2. Use XLOOKUP with match_mode = −1 to return the flow character from the table of lower limits.

Dissolution at 45 minutes, twelve vessels per batch. Both batches have respectable means. Only one of them would you release. Your job is to produce the numbers that show the difference.

Do this

1. Fill the summary block for both batches: n, mean, median, standard deviation, minimum, maximum, range, %RSD, standard error of the mean, and the 95% confidence interval half-width.
2. Count the vessels falling below 85% released.
3. Build the reported result as a text formula: mean ± CI.
4. Write one sentence saying which batch you would release and on what evidence.

Six paracetamol standards by HPLC, from 2 to 100 µg/mL, and one sample. One tablet was made up to 500 mL, then 5 mL of that was diluted to 100 mL. Label claim is 500 mg. Work out the assay, and make every step auditable.

Do this

1. Calculate the slope, intercept, R² and standard error of the fit. Remember: y range first, then x.
2. Fill the fitted-area column — you will plot it as the fitted line in task 7.
3. Back-calculate the sample in four visible stages: concentration injected, concentration in the stock solution, milligrams per tablet, and percentage of label claim.
4. Estimate LOD and LOQ from the standard error of the fit.

Two charts, both of which you will build again in your dissertation. Excel's defaults will get you about 60% of the way; the rest is the part that makes a figure publishable.

Chart A — the calibration plot

1. XY scatter of concentration against peak area, from the T6 sheet, markers only.
2. Add your fitted-area column as a second series, formatted as a line with no markers.
3. Title, both axis titles with units, and keep the legend — you have two series.

Chart B — the dissolution profile

1. XY scatter with straight lines and markers, plotting both batch means against time.
2. Add custom error bars pointing at each batch's SD column. Do not use the "Standard Deviation" preset — it calculates a single value from the plotted series instead of using your per-point SDs.
3. Set the y axis from 0 to 100 and say in the caption that the bars are ± 1 SD, n = 12.

Then

1. Answer the four chart-choice questions at the bottom of the sheet, one word each.
2. Score both of your charts against the visual checklist in the right-hand panel.