How to fit a 4PL curve in Excel with Solver

1. Enter the worked concentration-response data

Create headers for concentration, observed response, predicted response, residual, and squared residual. Enter the eight positive concentrations and responses below without rounding or replacing the source values. Keep zero-dose controls in the workbook, but outside this positive-concentration model range because zero cannot appear on a logarithmic axis.

This is an exact synthetic increasing 4PL series. It is deliberately clean so you can verify the worksheet and Solver setup. Real data require replicate, residual, uncertainty, and suitability review.

2. Build the 4PL prediction and error columns

Place starting values in named cells: Lower = 100, Upper = 1,800, EC50 = 15, and Slope = 1.0. In C2 calculate the predicted response using the increasing-curve convention below and fill it through C9. In D2 enter =B2-C2 for the residual; in E2 enter =D2^2; fill both down and calculate total SSE with =SUM(E2:E9).

Named cells make the formula readable, but ordinary absolute cell references work as well. If your package uses the opposite curve direction, a signed Hill slope, or log concentration inside the equation, translate the equation explicitly rather than expecting the same parameter signs.

3. Configure and run Excel Solver

Enable the Solver add-in in desktop Excel, then open Data → Solver. Set the total-SSE cell as the objective, choose Min, and select the four parameter cells as the changing variable cells. Choose GRG Nonlinear because this worksheet is a smooth nonlinear problem.

Add EC50 > 0 and Slope > 0 for this stated increasing convention. Add bounds on either asymptote only when the analytical method justifies them; arbitrary tight bounds can create a plausible-looking but constrained result. Save the Solver model or record every option needed to reproduce it.

1. Set Objective to the total-SSE formula cell.
2. Choose Min.
3. Set By Changing Variable Cells to Lower, Upper, EC50, and Slope.
4. Add EC50 > 0 and Slope > 0 for this increasing equation.
5. Choose GRG Nonlinear, run Solve, and retain the Solver status and settings.
6. Repeat from materially different starting values; materially different solutions indicate an unstable fit.

4. Check the fitted result and plot

With the stated data, formula, and adequate convergence, Solver should return approximately Lower = 75, Upper = 1,900, EC50 = 20.0 ng/mL, and Slope = 1.20. The predicted response at 20.0 ng/mL is 987.5, halfway between the fitted asymptotes.

Create an XY scatter plot of observed response against concentration and add the predicted series. Format the x-axis logarithmically, but do not infer fit quality from the smooth line alone. For this exact synthetic fixture SSE should be near zero apart from displayed-input rounding; ordinary experimental data will retain nonzero residuals.

5. Diagnose a fit before reporting it

Inspect the observed-versus-fitted plot, residuals against concentration and predicted response, parameter values and bounds, midpoint location within the tested range, asymptote support, replicate behavior, and convergence message. R² alone cannot establish that all four parameters are supported.

Common failures include a missed row in the SSE range, relative references that move while filling, a retained Solver model from another sheet, poor starting values, a zero or text concentration, an unconverged solution, or parameters pinned to arbitrary constraints. Make each failure visible instead of accepting the last displayed curve.

• Concentrations cover both sides of the fitted midpoint.
• Responses provide evidence for the lower and upper asymptotes.
• Residuals do not show a repeated curve or concentration pattern.
• The same solution is reached from reasonable alternate starting values.
• No required parameter is at an unexplained bound.
• Excluded observations remain visible with reasons.
• The displayed digits are rounded only after calculations and criteria.

6. Control the workbook for regulated use

A workbook used for a regulated decision needs controls proportionate to its intended use and risk: an approved version, source reconciliation, protected formulas, visible parameter and Solver settings, authorized access, expected-result and failure tests, retained exclusions, change assessment, review, output reconciliation, and a defined record-retention process.

Risk comes from the implemented workflow, not the Excel product name. A governed and validated workbook can be appropriate. The practical difficulty is that source data, formulas, Solver state, exclusions, and report output often live in separate editable locations, so every dependency must be identified, tested, and retained.