Tarsier Tools

Quadratic Regression Function - TT03-001
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 R² (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 our quadratic regression function can be found either under the "All" "data"ing or under "User defined", 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 data calculated by the function:
The data returned 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 data.

Criteria	Returned information	Example formula	Example result
0	X^0 term	=QuadRegression($A$2:$A$7;$B$2:$B$7;0) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,0) Excel	0.6143
1	X^1 term	=QuadRegression($A$2:$A$7;$B$2:$B$7;1) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,1) Excel	-0.2414
2	X^2 term	=QuadRegression($A$2:$A$7;$B$2:$B$7;2) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,2) Excel	1.0643
3	evaluate equation for entered X value	=QuadRegression($A$2:$A$7;$B$2:$B$7;3;2.5) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,3,2.5) Excel	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) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,4,6.6625) Excel	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) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,5,6.6625) Excel	-2.27315
6	calculate the X value of the turning point	=QuadRegression($A$2:$A$7;$B$2:$B$7;6) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,6) Excel	0.113423
7	calculate the Y value of the turning point	=QuadRegression($A$2:$A$7;$B$2:$B$7;7) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,7) Excel	0.600594
8	calculate the R2 (R squared) coefficient of multiple determination	=QuadRegression($A$2:$A$7;$B$2:$B$7;8) Calc =QuadRegression($A$2:$A$7,$B$2:$B$7,8) Excel	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
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.

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 OpenOffice.org Calc spreadsheet and the Microsoft Excel Add-in used to distribute this Tarsier Tool are 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 distributebuted with NO WARRANTY.