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;

Please note that this Tarsier tool is distributed with
NO WARRANTY

Section 5 Disclaimer of Warranties and Limitation of Liability.

  1. Unless otherwise separately undertaken by the Licensor, to the extent possible, the Licensor offers the Licensed Material as-is and as-available, and makes no representations or warranties of any kind concerning the Licensed Material, whether express, implied, statutory, or other. This includes, without limitation, warranties of title, merchantability, fitness for a particular purpose, non-infringement, absence of latent or other defects, accuracy, or the presence or absence of errors, whether or not known or discoverable. Where disclaimers of warranties are not allowed in full or in part, this disclaimer may not apply to You.
  2. To the extent possible, in no event will the Licensor be liable to You on any legal theory (including, without limitation, negligence) or otherwise for any direct, special, indirect, incidental, consequential, punitive, exemplary, or other losses, costs, expenses, or damages arising out of this Public License or use of the Licensed Material, even if the Licensor has been advised of the possibility of such losses, costs, expenses, or damages. Where a limitation of liability is not allowed in full or in part, this limitation may not apply to You.
  3. The disclaimer of warranties and limitation of liability provided above shall be interpreted in a manner that, to the extent possible, most closely approximates an absolute disclaimer and waiver of all liability.
.

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

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.

Example Data
Criteria
Returned information
Example formula for MS Excel (for OO Calc)
Result
0
x0 term
=QuadRegression(A2:A7,B2:B7,0)
0.6143
1
x1 term
=QuadRegression(A2:A7,B2:B7,1)
-0.2414
2
x2 term
=QuadRegression(A2:A7,B2:B7,2)
1.0643
3
evaluate equation for entered X value, e.g. 2.5
=QuadRegression(A2:A7,B2:B7,3,2.5)
6.6625
4
calculate the 1st X value that gives the entered Y value, e.g. 6.6625
=QuadRegression(A2:A7,B2:B7,4,6.6625)
2.5
5
calculate the 2nd X value that gives the entered Y value, e.g. 6.6625
=QuadRegression(A2:A7,B2:B7,5,6.6625)
-2.27315
6
calculate the X value of the turning point
=QuadRegression(A2:A7,B2:B7,6)
0.113423
7
calculate the Y value at the turning point
=QuadRegression(A2:A7,B2:B7,7)
0.600594
8
calculate the R2 (R squared) coefficient of multiple determination
=QuadRegression(A2:A7,B2:B7,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
  • 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)
  • 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 licensed 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 3.0 & 4.0) Will work for LibreOffice 3
Instructions for Microsoft Excel

How to Un-install

Instructions for OpenOffice.org Calc (version 3.0)
Instructions for Microsoft Excel

Revision History

Open Office Calc

Rev
Date
Details
1.0
12-Mar-2006
Initial version released, based on Rev 2.1 of my Excel version
1.1
28-Feb-2010
Fix error that the error code -999 (for #Num) is returned for the #Value (-998) and #N/A (-997)
1.2
03-Mar-2010
Fix error that crashes if requested to find a non-existent root, instead of returning #Value (-998)
1.3
03-Mar-2010
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
03-Mar-2010
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
03-Mar-2010
Fix error that the function crashes if range of data is empty instead of returning #Num (-999)

Microsoft Excel

Rev
Date
Details
1.0
24-May-2003
Initial version released
2.0
21-Feb-2006
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-2006
add calculation for R2 (R squared) coefficient of multiple determination
2.2
12-Mar-2010
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.4
12-Mar-2010
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-2010
Fix error that returns #Value if range of data is empty instead of returning #Num

Software for Download

Operating Sys.
Spreadsheet App
Software
OS X 10
Apache OpenOffice™ 4 Calc
Ubuntu 12.04
LibreOffice 3
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

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 Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License to determine your rights to distribute this software and be informed that this software is distributed with NO WARRANTY.

Technical Support

Frequently Asked Questions

There are no FAQ for this "Tarsier 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) "Tarsier Tool".

Bug Reports

If you suspect a bug in this "Tarsier Tool" please e-mail me a description.

Please ensure you identify that the bug report is about my Quadratic Regression Function (TT03-001) "Tarsier Tool", 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.