Tarsier Tools

Linear Interpolation Function

Designed for OpenOffice.org Calc and Microsoft Excel, this "Tarsier Tool" interpolates between consecutive ordered points from the given data. The function can be configured to interpolate using either a step or linear function. If the input value is outside of the range of the X coordinates the function can be configured to either perform a linear extrapolation using the two nearest points; return a constant value of the nearest value or return the Excel #Num value.

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 linear interpolation 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 two optional arguments.

You must;

- specify the range of cells containing the known X values, (argument
**known_x**) - specify the range of cells containing the known Y values, (argument
**known_y**) - specify the X value you are interpolating against, (argument
**x_Value**)

With this information the function will perform linear interpolation to find the specified value, or extrapolate to find the Y value if the X value is outside the range of known X values.

Additionally, you can specify the following options;

- what value should be returned if the X value interpolated against is outside the range of known X values, (argument
**cutOffAction**, 0 = extrapolate; 1 = cut off at the nearest known value, 2 = return #NUM! or -999 in Calc) - use step interpolation, (argument
**step**, a binary value, false = perform linear interpolation, true = perform stepwise interpolation)

The known X values must be in numerical order, minimum to maximum.

The values in the **Example result** column are calculated using the following data.

Description

x_value

cutOff

step

Example formula for OO Calc (for MS Excel)

Result

Compulsory arguments - X value within range

1.5

Not specified

Not specified

=LinearInterpolator(A2:A7;B2:B7;1.5)

2.7

Compulsory arguments - X value outside of range

-1.0

Not specified

Not specified

=LinearInterpolator(A2:A7;B2:B7;-1.0)

0.5

6.0

Not specified

Not specified

=LinearInterpolator(A2:A7;B2:B7;6.0)

35.5

Cut off - X value outside of value

-1.0

1

Not specified

=LinearInterpolator(A2:A7;B2:B7;-1.0;1)

0.8

6.0

1

Not specified

=LinearInterpolator(A2:A7;B2:B7;6.0;1)

26.0

Return value - X value outside of value

-1.0

2

Not specified

=LinearInterpolator(A2:A7;B2:B7;-1.0;2)

-998

6.0

2

Not specified

=LinearInterpolator(A2:A7;B2:B7;6.0;2)

-998

Stepwise interpolation - X value within range

1.5

0, 1, 2

True

=LinearInterpolator(A2:A7;B2:B7;1.5;0;True)

1.1

2.001

0, 1, 2

True

=LinearInterpolator(A2:A7;B2:B7;2.001;0;True)

4.3

3.9999

0, 1, 2

True

=LinearInterpolator(A2:A7;B2:B7;3.9999;0;True)

9.9

Stepwise interpolation - X value outside of range

-1.0

0

True

=LinearInterpolator(A2:A7;B2:B7;-1.0;0;True)

0.8

6.0

1

True

=LinearInterpolator(A2:A7;B2:B7;6.0;1;True)

26

-4.0

2

True

=LinearInterpolator(A2:A7;B2:B7;-4.0;2;True)

-998

*Note, constant values shown in the example equations above can of course be replaced by cell references, definitions, etc...*

When using the Linear Interpolation Function, it will return an error code in the following circumstances;

Description

Calc

Excel

1.

- there are a different number of X & Y values
- if you only supply one point (one X and one Y value)
- if you specify errNum to be returned if input x value is out of the known x range

-999

#Num

2.

The specified **cutoff** is invalid, i.e. not in the range 0 to 2

-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 4) Will work for LibreOffice 3

Instructions for Microsoft Excel

Instructions for OpenOffice.org Calc (version 4) Will work for LibreOffice 3

Instructions for Microsoft Excel

Rev

Date

Details

1.0

26-Aug-2015

Initial version released, based on Rev 1.0 of my Excel version

Rev

Date

Details

1.0

10-Nov-2003

Initial version released

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 Linear Interpolation 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 *Linear Interpolation Function (TT03-002)* "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 *Linear Interpolation Function (TT03-002)* "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.