2014
AssayFit 1.6 user manual
AssayFit
version 1.6 research use only
user manual
Curve fitting in excel 2007, 2010 and 2013 using the excel solver
1
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
AssayFit the flexible curve fitting software in Microsoft Excel.
AssayFit is a software program for curve fitting and regression. It is specially designed for research
and development of assays like ELISA tests, Luminex assays, other immunoassays or enzyme assays,
however it can be used for a variety of other purposes. It is for research use only.
Linear, 4PL (four parameter logistic), 5PL (5 parameter logistic), spline, point to point and second and
third order polynomial regressions are included in the software. Users have the ability to add 10
custom fit functions.
A calibration curve is fitted and unknown values are read from this calibration curve.
The software works as an Add-In in Microsoft excel 2007, 2010 and 2013. A trial version is available.
A video how the software works can be viewed on youtube: http://youtu.be/QztYGyK5I-A?hd=1
The IVDtools website is located at http://www.IVDtools.com
2
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Installing AssayFit
Before installing AssayFit make sure that the excel solver is installed. AssayFit will not work without
the solver.
The trial and full version of the software from IVDtools come with an installer. Run the installer, then
it will install the software in a directory. It is advised to install it in the directory the installer
indicates. You must have read and write permission on the installation directory. If you cannot write,
this will result in errors. You may need administrative rights to run the installer however in most
cases this will not be required.
appearance of the installed Add-Ins
Then on the same sheet click on the Go button
A form is shown with all already installed Add-Ins
3
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
You can see here that the Solver Add-in is installed.
4
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
The AssayFit menu
There are two options how to use the AssayFit plugin. One is to use the special AssayFit tab in the
excel ribbon. The other is through the AssayFit menu in the Add-Ins tab.
If you click the AssayFit tab the AssayFit buttons appear. Clicking them will run the different scripts.
Under the Add-ins tab a menu is located with the same options.
5
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Adding a new curve fitting sheet
You can add a new curve fitting sheet by selecting clicking the ribbon button named “Add fit sheet”
or the menu option: Add new empty IVDtools sheet. As alternative this option can be activated by
the key combination: ctl, shift and N.
Or use the menu option
A new IVDtools sheet will appear with the default settings (in this case a 4PL regression) and default
calibrators. These settings can be modified and saved in order to obtain your own personal sheet
when adding a new IVDtools sheet.
6
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
7
2014
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Entering calibrator values
You can enter calibrator ID, dose and response values in the corresponding fields. Also the layout of
the fields including the number of digits can be modified. The data points will be added to the graph,
but the curve fit does not update until the menu option is selected to perform a curve fitting.
When adding calibrators the field %, meaning the % of the maximum will be updated.
8
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Performing a curve fit
The curve fit will be performed after selecting the menu option run AssayFit (formulas), or the key
combination, clt-shift-f. The program will then perform a curve fit adding formulas in the fields.
or
As an alternative also the menu option Run AssayFit(values) can be selected. This also performs a
curve fit, but the results of the fit will be entered as values. The sheet will then be locked. This option
is preferable if you would like to lock the data or send the data to a person that does not have the
AssayFit software installed.
or
A sheet can also be locked afterwards with the “Lock values” menu option. It can be unlocked by the
“Unlock – formulas option”
9
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
10
2014
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Reading unknown values from the fitted curve
Once a curve fitting has been performed, unknown response values can be used to read the
corresponding dose value from the calibration curve.
The response is entered and the software calculates the corresponding dose from the fitted curve.
The response can be entered starting from row 24.
11
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
When using ELISA 96 well data the data can be entered in the corresponding fields on the right of the
spreadsheet.
One block can contain the ID values, one block the response values. In the last block the calculated
dose values are displayed.
12
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Selecting a new curve fit function
The menu item “Fit function select” has a submenu with all the possible fit functions.:
The ribbon has three submenu’s showing the same functions:
4PL1, 4PL2 Two four parameter logistic functions
5PL1 A Five parameter logistic function
PtoP Point to point curve fitting, a straight line from one calibrator to the next
Spline A spline function: a smooth curve through the data points
LIN: Linear fit
2nd Poly: Second order polynomial regression
3rd Poly: Third order polynomial regression
Additional to these standard functions 10 custom functions can be entered and selected through this
menu.
After selecting a new function the program will automatically start a new fit and display the fit
function.
In the example below the 4Pl2 function is selected
13
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
AssayFit will start fitting the data points with the new function. The fit function is entered into the
Fitmodel field. The backfitmodel is used to backcalculate the response to the dose. The response of
unknown values can be entered into the corresponding field and the software will calculate the
corresponding dose values. If the fit has been performed the software immediately calculates the
dose value if a new unknown response value is entered. Is the value lower than the value in the curve
it will indicate < and the minimal dose. If it it higher than the curve, it will indicate > and the
maximum dose value. With the default setting the program does not extrapolate. The limit values
however can be changed in the backfit limits fields.
When the fit is performed, the program calculates the fitted curve at the data points and it will
calculate the % bias. This is the bias between the fitted curve and the real data points. Also the fit
parameters are calculated. In this case, the formula uses four parameters, A, B, C_ and D that are
used by the fit function. The program calculates the optimal values for A, B, C_ and D and these can
14
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
be found in the table below the graph.
15
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Weight settings
In the fit above the weight settings of all the calibrators were set to 1. This means that the calculation
of the bias al the calibrators equally contributed. You can adjust the weight of a calibrator, making
this calibrator more important for correct fitting. In this example we have set the weight of the fifth.
After performing the fit, the fitted curve fits this point better than before.
The weights can also be set with a formula for example 1/Y^2 is commonly used.
16
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Calibrator order and empty values
The fit is independent on the order of the calibrators in the table, it also can accept empty values.
This is very easy if you would like to see what effect dropping a point in the curve would have on the
fit. In this example both the order of the calibrators and empty values are introduced, giving exactly
the same fit as above. Please note that both the dose and response value have to be removed.
17
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Fit parameters and start values
The fit parameters are in the table below the graph. When the program starts fitting it will use the
start values, enter these into the fit function and from there will try and find the most optimal values
for the parameters. A linear regression function y= Ax + B will only use the two top parameter, while
a 5PL function will use all five parameters.
The start values for a linear regression and for a 4PL or 5PL are different. When entering a custom
function the correct start values will need to be applied, because otherwise the start values may be
so far off, the software cannot find the optimal values.
The R2 column represents the goodness of fit. The number in the left column is the total sum of
squares. This adds up all the differences of the values from the actual values, though the least
squares method. The program will try and make this value as small as possible. In the right column
the actual correlation coefficient is displayed.
18
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Solver parameters
The program uses the excel solver to optimize the parameters. Some settings of the solver can be
adjusted in the AssayFit program. The options are in the Solver parameters table. The solver of excel
2007 and 2010/2013 is different and the 2010 solver is not using all of the parameters indicated.
Runs is the number of runs that the solver performs.
Additional settings of the solver (if needed) can be set by selecting “solver” on the data tab. Please
make sure you use an unprotected sheet if the solver is activated as it will not work with a protected
sheet.
19
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Entering a new custom fit function
By selecting the menu item “Enter new custom functions” a form appears. In this form new fit
functions can be added.
You can enter new functions in this form. X as unknown needs to be present in the functions as well
as some of the parameters A, B, C_, D, E. Please write the C parameter as C_ , otherwise this will
result in errors. Some functions like Ln need to be written as application.worksheetfunction.ln. After
pressing the button “Save functions” the functions will be saved and available under the fit function
20
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
select menu.
Copying of AssayFit sheets
You can easily copy a AssayFit tab by right clicking onto the tab and then selecting the “move or copy
option”. You can then either copy the sheet to a new file or to the same file.
21
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
A second sheet is created. If you modify the calibrators, run the fit again to ensure proper fit.
22
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
Creating an overview of all the AssayFit sheets in a file
If you have multiple sheets and want to create an overview of all the calibration curves select the
option Curve fit overview. You have two options: Overview large, with all the calibrators and
overview small, with a smaller number of calibrators. The program will create a sheet called
“Graphoverview” with a copy of the calibration curve and values. The name of the sheet will be
added as a title.
23
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Settings
Under the settings and layout menu, color and default settings can be saved and restored.
You can remove the purple color from the sheet by selecting the “Decolorize” option, the Colorize
option brings the color back again.
You can save all settings by selecting “Save current sheet as custom sheet”. All settings, like fit
function, calibrator values, color and formatting settings, solver parameters, start values and limits
are stored by using this option. If you then add a new sheet it will have these settings.
You can also apply settings on an existing sheet. This is done by the option “restore saved settings
with/no cal values.”
“Reset settings to default” restores the default settings.
Fit functions structure in Excel
When you select a cel of the fitted curve, the fit function will show. It will have references to the
input value (K8) , the fit model (B5) and the five parameters.
24
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
When you would like to add an AssayFit function to a cell, click the fx sign it will show the available
functions.
You can select the IVDtools option to see all IVDtools functions
25
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
The four possible functions are shown with an explanation of the parameters.
The use of the point to point and the spline function does not work from a different sheet. The other
functions will also work from a different sheet. To use the point to point backfit function use:
=PtoPb(calibrators, unknown) . The spline backfit cannot be used from a different sheet.
26
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
You can then add or select the reference to the different cells.
27
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Automation of AssayFit with visual basic scripts
If you wish to automate the fit function using VBA, then “runAssayFit” and “runAssayFitvalues” can
be called to trigger curve fitting. Below is a very simple script.
Sub automate()
runAssayFit
End sub
More complicated scripts can be used to copy data into the AssayFit sheet, perform curve fitting and
copy the output to a new sheet.
Trial version and full version
A trial version is available that has all the functionality of the full version but only with the Linear
regression and 4PL function. This trial can be used to test the suitability of the software. It can be
downloaded for free from the IVDtools website (www.IVDtools.com).
The full version can be obtained from the IVDtools webshop (www.IVDtools.com) and downloaded
directly. It will perform the full set of curve fitting including custom formula entry
Troubleshooting
If you have trouble curve fitting you can use the following suggestions
1. Make sure the Excel solver is installed and active.
2. Start with the example sheet which is installed on the desktop and see if AssayFit will run
from this sheet
3. Make sure the location you install AssayFit to is writable
4. Make sure your Excel security settings will allow VBA macro’s to be run
5. Old excel sheets may have too less rows and columns, copy the data to a new xlsx sheet
6. If you are still having problems contact IVDtools at [email protected] or use the website’s
contact box
28
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Version Info
Version 1.6 Version changes April 24 2014
Added 96 well fitting support to facilitate fitting of ELISA plates
Version 1.5 Version changes March 15 2014
Changed name to AssayFit. Changed SST to sum of squares total, without devision. And clarified this
in the header
Version 1.4 Version changes March 3 2014
Minor changes
Version 1.3 Version changes February 24 2014
Name change back to IVDtools and AssayFit RUO research use only. Addition of the Excel Ribbon
user interface. Update of the manual.
Version 1.2 Version changes January 12 2014
Name change to IVRtools and IVRfit throughout all the functions and sheets
Version 1.1.5 Version changes December 12 2013
Minor bug fix, changes in the license and manual
Version 1.1.4 Version changes. November 14 2013
Fixed a small bug related to sheet locking
Version 1.1.3 Version changes. November 7 2013
Fixed a bug with run-time error 5, which prevented running AssayFit in certain languages
Version 1.1.2 version changes. April 2 2013
Added a warning when using xls and csv sheets because the number of rows and columns is too small
to add a new AssayFit sheet.
Version 1.1.1 version changes. December 12 2012
Installer is compatible with office 2013
Changed color settings of some items
Changed menu item order
Light version 1.1.1 introduced January 20 2012
29
all rights reserved copyright © 2014 by IVDtools
2014
AssayFit 1.6 user manual
Version 1.1 version changes: September 24 2012
Added correlation coefficient calculation
Plugins are independent on the location of the solver
Plugins are now office version independent
Software is compatible with Mac OS office 2011, but the solver on Mac runs slow
Warning added for custom functions with formulas
Start parameter D for 4PL and 5PL set to 0
Version 1.0 original version April 10 2012
Original version
30
all rights reserved copyright © 2014 by IVDtools
AssayFit 1.6 user manual
2014
License
Before downloading you have to agree to the software license agreement. It is also in the software
under the license menu.
Software License Agreement
The AssayFit software add-ins (AssayFit.xlam, AssayFitcust.xlam) together with installer and
accompanying documentation (referred to as "Software" in this Agreement) is the property of
IVDtools.com ("IVDtools"). By downloading, installing or using this Software or parts of this software,
you (or you on behalf of your organization) are agreeing to the terms and conditions of this
Agreement. This Software is protected under International Copyright Laws.
1. License Grants
One purchased single user copy from the IVDtools website (http://www.IVDtools.com) allows the use
of the Software by one user on one computer in your organization.
2. License Restrictions
You may not sell, copy, emulate, clone, rent, lease, adapt, translate, sublicense, decompile,
disassemble, otherwise reverse engineer Software except as specified in this agreement. Any
unauthorized use shall result in immediate and automatic termination of this license and may result
in prosecution.
3. Transfer
You may make a one time permanent transfer of all your rights of use of the Software to another
person or legal entity provided you also transfer this Agreement and you retain no copies of the
Software. The trial or light version of the software may be transferred to other persons for the trial
period, provided you also transfer this Agreement.
4. Termination
This Agreement takes effect upon your use of the Software and remains effective until terminated. It
will be terminated automatically without notice from IVDtools if you fail to comply with any provision
contained here. You agree on termination of this Agreement to destroy all copies of the Software in
your possession.
5. Limitation of liability
In no event will IVDtools be liable to you for any damages, including any lost profits, lost savings, or
other incidental or consequential damages arising from the use or the inability to use the software,
even if an authorized dealer or distributor has been advised of the possibility of these damages.
6. Warranty Disclaimer
IVDtools disclaims all warranties, whether expressed, implied, or otherwise, including the warranties
of merchantability or fitness for a particular purpose. IVDtools does not warrant that the software is
error-free or will operate without interruption.
7. Intended Use
The software is for research use only.
All rights reserved © 2014 by IVDtools
31
all rights reserved copyright © 2014 by IVDtools