Tarsier Tools
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 co-ordinates of the turning point and calculate the R2 (R squared) coefficient of multiple determination.

Please note that this Tarsier tool is distributed with NO WARRANTY.

Usage Instructions
How to Install
How to Uninstall
Revision History
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;
1. specify the range of cells containing the known X values, (argument known_x in Excel function dialog)
2. specify the range of cells containing the known Y values, (argument known_y in Excel function dialog)
3. 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 R2 (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; there are a different number of X & Y values, or if, there are no values of X that equate to the specified Y value, (for example, for the example data above the minimum Y value is 0.600594, i.e. there is no value of X for which Y is less than this. Therefore if you try to find, for example, the X value for which Y = 0 the function will return an error), or if, you are trying to find the turning point and the data follows a linear function. -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 Attribution-NonCommercial-ShareAlike 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 e-mail 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 Un-install 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 12-Mar-06 Initial Version Released, based on Rev 2.1 of my Excel version 1.1 28-Feb-10 Fix error that the error code -999 (for #Num) is returned for the #Value (-998) and #N/A (-997) 1.2 03-Mar-10 Fix error that crashes if requested to find a non-existant root, instead of returning #Value (-998) 1.3 06-Mar-10 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 06-Mar-10 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 06-Mar-10 Fix error that the function crashes if range of data is empty instead of returning #Num (-999)

 Rev Date Description 1.0 24-May-03 Initial Version Released 2.0 21-Feb-06 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 0-534-14352-0 pages 517 ~ 519 2.1 21-Feb-06 add calculation for R2 (R squared) coefficient of multiple determination 2.2 12-Mar-10 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 12-Mar-10 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 12-Mar-10 fix error that returns #Value if range of data is empty instead of returning #Num

 OS Ver. Spreadsheet Software OS X 10 Apache OpenOffice™ 4 Calc tt03-001.ods  (14.5k) Ubuntu 9.04 OOo Calc V3.0 Ubuntu 12.04 LibreOffice 3 Win 2000 Excel 2003 TT03-001.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 Add-in 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 e-mail it to me. Please ensure that you identify that the question is about my Quadratic Regression Function (TT03-001).

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, e-mail me a description. Please ensure you identify the Tarsier Tool, (Quadratic Regression Function TT03-001), the operating system and spreadsheet program in the e-mail 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.

Back to Available Tarsier Tools 