Tarsier Tools

Quadratic Regression Function

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 specified X value
- calculate the X values for a specified Y value
- calculate the X and Y values of the turning point (X value that gives the minimum or maximum Y value)
- calculate the R2 (R squared) coefficient of multiple determination

Please note that this Tarsier tool is distributed with

NO WARRANTY# Section 5 Disclaimer of Warranties and Limitation of Liability.

.
**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.****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.**- 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

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.

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 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...*

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;

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

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.

Instructions for OpenOffice.org Calc (version 3.0 & 4.0) Will work for LibreOffice 3

Instructions for Microsoft Excel

Instructions for OpenOffice.org Calc (version 3.0)

Instructions for Microsoft Excel

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.

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)

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.

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

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.

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**.

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".

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.