Tarsier Tools
Quadratic Regression Function
Overview
Designed for OpenOffice.org Calc and Microsoft Excel, this "Tarsier Tool" enables you to perform quadratic regression on your data. The function can provide the coefficients of the quadratic equation, evaluate the function for a known X value, calculate the X values for a known Y value, calculate the coordinates of the turning point and calculate the R^{2} (R squared) coefficient of multiple determination.
Please note that this Tarsier tool is distributed with NO WARRANTY.
This page contains the following information on this Tarsier Tool.
Usage Instructions
License Fee
How to Install
How to Uninstall
Revision History
Software for Download
Source Code
Technical Support
Further Information
Usage Instructions The following instructions assume you know how to use functions in Calc or Excel. In Excel my quadratic regression function can be found either under the "All" or under "User defined" in the function menu, but in Calc you will have to type the function name.
The function has three mandatory arguments and one optional argument. In all instances that you use the function, you must;
License Fee This "Tariser Tool" is licenced under the Creative Commons AttributionNonCommercialShareAlike 4.0 International License. There is no license fee for personal use of this tool. However, if you are using this software within software that you are developing and intend to sell, please email me for details of the license fee.
How to Install Instructions for OpenOffice.org Calc (version 2.0)
Instructions for OpenOffice.org Calc (version 3.0 & 4.0) Will work for LibreOffice 3
Instructions for Microsoft Excel
How to Uninstall Instructions for OpenOffice.org Calc (version 2.0)
Instructions for OpenOffice.org Calc (version 3.0)
Instructions for Microsoft Excel
Revision History
Software for Download
Technical Support
Frequently Asked Questions Currently there are no FAQ for this tool. If you have a question, please email it to me. Please ensure that you identify that the question is about my Quadratic Regression Function (TT03001).
Bug Reports If you suspect a bug in this Tarsier Tool, before contacting me, please check the FAQs and Bug Reports to see if it has already been identified. If it hasn't, email me a description. Please ensure you identify the Tarsier Tool, (Quadratic Regression Function TT03001), the operating system and spreadsheet program in the email and include as much detail as possible; including a simple example application is always helpful! but make sure it does not contain proprietary or confidential information.
Currently there are no reported bugs for this Tarsier Tool.
Further Information This section will be updated with links to information relating to the principles behind and the uses of this Tarsier Tool, as I become aware of them.
Please note that this Tarsier tool is distributed with NO WARRANTY.
This page contains the following information on this Tarsier Tool.
Usage Instructions
License Fee
How to Install
How to Uninstall
Revision History
Software for Download
Source Code
Technical Support
Further Information
Usage Instructions The following instructions assume you know how to use functions in Calc or Excel. In Excel my quadratic regression function can be found either under the "All" or under "User defined" in the function menu, but in Calc you will have to type the function name.
The function has three mandatory arguments and one optional argument. In all instances that you use the function, you must;
 specify the range of cells containing the known X values, (argument known_x in Excel function dialog)
 specify the range of cells containing the known Y values, (argument known_y in Excel function dialog)
 specify what information you want the function to return, (see below for what values return what information), (argument criteria in Excel function dialog)
If you want to evaluate the quadratic function obtained from the regressed data, you must specify the value you want evaluated using the fourth argument, (argument unknown in Excel function dialog). The argument is optional as it is only used for criteria = 3, 4 and 5.
How to specify the result calculated by the function The result calculated by the function is selected based on the value of the criteria argument as shown in the table below. "Example result" is calculated using the following example data,Criteria  Returned information  Example formula (Excel Calc)  Example result 
0  X^0 term  =QuadRegression($A$2:$A$7,$B$2:$B$7,0)  0.6143 
1  X^1 term  =QuadRegression($A$2:$A$7,$B$2:$B$7,1)  0.2414 
2  X^2 term  =QuadRegression($A$2:$A$7,$B$2:$B$7,2)  1.0643 
3  evaluate equation for entered X value  =QuadRegression($A$2:$A$7,$B$2:$B$7,3,2.5)  6.6625 
4  calculate the 1st X value that gives the entered Y value  =QuadRegression($A$2:$A$7,$B$2:$B$7,4,6.6625)  2.5 
5  calculate the 2nd X value that gives the entered Y value  =QuadRegression($A$2:$A$7,$B$2:$B$7,5,6.6625)  2.27315 
6  calculate the X value of the turning point  =QuadRegression($A$2:$A$7,$B$2:$B$7,6)  0.113423 
7  calculate the Y value of the turning point  =QuadRegression($A$2:$A$7,$B$2:$B$7,7)  0.600594 
8  calculate the R^{2} (R squared) coefficient of multiple determination  =QuadRegression($A$2:$A$7,$B$2:$B$7,8)  0.9992 
Note, constant values shown in the example equations above can of course be replaced by cell references, definitions, etc...
Error Messages
When using the Quadratic Regression function, it will return an error code in the following circumstances;
Description  Calc  Excel  
1.  If your data contains less than three X and Y values or the data range is blank.  999  #Num 
2.  The data provided fails to fit the equations. This can occur if;

998  #Value 
3.  The specified criteria is invalid, i.e. not in the range 0 to 8.  997  #N/A 
License Fee This "Tariser Tool" is licenced under the Creative Commons AttributionNonCommercialShareAlike 4.0 International License. There is no license fee for personal use of this tool. However, if you are using this software within software that you are developing and intend to sell, please email me for details of the license fee.
How to Install Instructions for OpenOffice.org Calc (version 2.0)
Instructions for OpenOffice.org Calc (version 3.0 & 4.0) Will work for LibreOffice 3
Instructions for Microsoft Excel
How to Uninstall Instructions for OpenOffice.org Calc (version 2.0)
Instructions for OpenOffice.org Calc (version 3.0)
Instructions for Microsoft Excel
Revision History
Rev  Date  Description 
1.0  12Mar06  Initial Version Released, based on Rev 2.1 of my Excel version 
1.1  28Feb10  Fix error that the error code 999 (for #Num) is returned for the #Value (998) and #N/A (997) 
1.2  03Mar10  Fix error that crashes if requested to find a nonexistant root, instead of returning #Value (998) 
1.3  06Mar10  Code changed to handle the case were the average of the x values is zero. I'd made a mistake saying that the algorithm failed when the average of the x's equalled zero. While implementing an algorithm given by Gerard de Graan, (www.optimatica.nl (English) and www.optimatica.com (Dutch)), to handle this condition I found that the divide by zero occurs when the number of data points is two, not when the average of the x's is zero. 
1.4  06Mar10  Fix error that the function crashes if requested to find the roots or turning points for a linear function, instead of returning #Value (998) 
1.5  06Mar10  Fix error that the function crashes if range of data is empty instead of returning #Num (999) 
Rev  Date  Description 
1.0  24May03  Initial Version Released 
2.0  21Feb06  Changed from using Gaussian elimination to solve the system of equations required to perform a least squares fit of data, to using explicit formulas for quadratic regression, given in "Probability and statistics for engineering and the sciences", Jay L. Devore, ISBN 0534143520 pages 517 ~ 519 
2.1  21Feb06  add calculation for R^{2} (R squared) coefficient of multiple determination 
2.2  12Mar10  Code changed to handle the case were the average of the x values is zero. I'd made a mistake saying that the algorithm failed when the average of the x's equalled zero. While implementing an algorithm given by Gerard de Graan, (www.optimatica.nl (English) and www.optimatica.com (Dutch)), to handle this condition I found that the divide by zero occurs when the number of data points is two, not when the average of the x's is zero. 
2.3  12Mar10  fix error that returns #Value if requested to find the roots for a linear function instead of returning the value of x that gives the specified value for y 
2.4  12Mar10  fix error that returns #Value if range of data is empty instead of returning #Num 
Software for Download
OS Ver.  Spreadsheet  Software 
OS X 10  Apache OpenOffice™ 4 Calc  tt03001.ods (14.5k) 
Ubuntu 9.04  OOo Calc V3.0  
Ubuntu 12.04  LibreOffice 3  
Win 2000  Excel 2003  TT03001.xla (46.5k) 
Win XP Pro  
Win 7  Excel 2007 
Please let me know if you have used my Quadratic Regression Function, successfully or not, on any other Operating System and spreadsheet combination.
Source Code In accordance with the GNU General Public License the Microsoft Excel Addin used to distribute this Tarsier Tool is not password protected allowing you to view and modify it if you require. You should review the GNU GPL to determine your rights to distribute this software and be informed that this software is distributed with NO WARRANTY.Technical Support
Frequently Asked Questions Currently there are no FAQ for this tool. If you have a question, please email it to me. Please ensure that you identify that the question is about my Quadratic Regression Function (TT03001).
Bug Reports If you suspect a bug in this Tarsier Tool, before contacting me, please check the FAQs and Bug Reports to see if it has already been identified. If it hasn't, email me a description. Please ensure you identify the Tarsier Tool, (Quadratic Regression Function TT03001), the operating system and spreadsheet program in the email and include as much detail as possible; including a simple example application is always helpful! but make sure it does not contain proprietary or confidential information.
Currently there are no reported bugs for this Tarsier Tool.
Further Information This section will be updated with links to information relating to the principles behind and the uses of this Tarsier Tool, as I become aware of them.
Copyright ©2003 Tarsier Software Solutions Creative Commons AttributionNonCommercialShareAlike 4.0 International License