Oct 20, 2019
Simultaneous fitting of sigmoid curves in Excel (Excel Solver Add-in)
- 1Department of Chemistry, Faculty of Science, Ochanomizu University
- OhganeLab
Protocol Citation: Kenji Ohgane 2019. Simultaneous fitting of sigmoid curves in Excel (Excel Solver Add-in). protocols.io https://dx.doi.org/10.17504/protocols.io.8g9htz6
License: This is an open access protocol distributed under the terms of the Creative Commons Attribution License, which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited
Protocol status: Working
We use this protocol and it's working
Created: October 20, 2019
Last Modified: October 20, 2019
Protocol Integer ID: 28929
Keywords: Nonlinear least square fitting, Excel, Solver, simultaneous fitting, dose-response analysis
Abstract
Sometimes it is desirable to fit sigmoid curves with shared parameters against several data sets. Such simultaneous fitting procedure is available in most of the specialized statistical softwares (R, Prism etc.), but also possible with Excel. Here, this protocol explains how to perform simultaneous fitting using Excel Solver plug-in against a pair of data set. For more basic protocol to fit sigmoid curve, see Kenji Ohgane (2019) Sigmoid fitting in Excel (Excel Solver Add-In). protocols.io dx.doi.org/10.17504/protocols.io.78ihrue. See Gerdi Kemmer & Sandro Keller (2010) Nat. Protocols 5: 267–281 for more detail on the use of Excel Solver in nonlinear least square fitting.
Guidelines
Although this protocol explains how to set up simultaneous fitting of two curves, you can easily accommodate this protocol to more complex data sets. However, for more sophisticated analysis, the use of Prism (commercial software) or R (open source software) is recommended for more clear analytical workflow and reproducibility. To perform simultaneous fitting of dose-response curves in R, the use of drc package would be a convenient option.
Before start
Excel Solver Add-In is currently bundled with Excel by default, but you need to load the add-in for the first time. To load the add-in, select "Excel Add-Ins" from "Tools" menu, check the Solver Add-In, and click OK.
Download the following excel file.
The file contains a sheet set up for simultaneous sigmoidal fitting with two set of example data, for which you can test the fitting procedure.
ExcelSolver_SimultaneousFitting.xlsx Open the excel file, and enter your data set in the two set of "x" and "y" columns. Delete unnecessary data.
Note
The data in the "x" column should be in decreasing or increasing order. Otherwise, the plot will not be correctly displayed.
Expected result
Guess initial parameters for the sigmoids (minimum, max, n, and ec_50), which can be easily guessed from the plot, and enter the estimated initial values into the cells (B2, B3, B4, and B5 for the first data set, and K2, K3, K4, and K5 for the second data set).
Note
Note that these cells for parameters are "named cells". So you can reference these cells from within equations by using "=min" (first data set) or "=min_" (second data set), where an underbar denotes parameters for the second data set. If you want to modify the equation for the curves, please modify equations within the cells in the "ycalc" columns.
Start the Excel Solver add-in from Tool > Solver on the menu bar.
Expected result
Solver add-in
Set "Objective / Target cells" to "$B$8", which contains the sum of SSR (Sum of Squared Residuals) calculated for the two data sets.
Expected result
Setting up Solver parameters
Set "equall to" section to "Value of 0".
Note
This results in a warning saying "unable to find solution", but you can ignore it.
Set "Changing variable cells" to the cells that contains initial values for the two sigmoid curves ("$B$2:$B$5, $K$2:$K$5").
Add constraints.
(1) We recommend to add "ec_50 >= 0.000001" etc., to avoid EC50 becoming negative values or zero.
(2) In this example, we fit two sigmoid curves that share minimum and maximum values against the pair of data set. So add two constraints as shown in the figure ("min = min_" and "max=max_").
Expected result
Confirm that "Assume non-negative" is unchecked, and click "Solve" to run the Solver.
Close the Solver window when the calculation finished. Then you can get fitted parameters and a curve overlaid over the raw data points. Check if the sigmoid curve is reasonably fitted over the data.
Expected result
Then you can get fitted parameters and curves overlaid over the raw data points. Check if the sigmoid curve is reasonably fitted over the data.
Expected result
Expected result
The result of simultaneous sigmoid curve fitting.