Overview

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

Usage Instructions

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)

Important Note

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

The range of known X values and known Y values must not contain blank cells. If you do, for x values greater than zero in Excel you get a #VALUE! error, but for OpenOffice or LibreOffice the macro will halt with a divide by zero error.

How to specify the result calculated by the function

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

Description

x_value

cutOff

step

Example formula for MS Excel (for OO Calc)

Result

Compulsory arguments - X value within range

1.5

Not specified

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

2.7

Compulsory arguments - X value outside of range

-1.0

Not specified

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

0.5

6.0

Not specified

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

35.5

Cut off - X value outside of value

-1.0

Not specified

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

0.8

6.0

Not specified

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

26.0

Return value - X value outside of value

-1.0

Not specified

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

#NUM!

6.0

Not specified

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

#NUM!

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

True

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

0.8

6.0

True

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

-4.0

True

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

#NUM!

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

Error Messages

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

Description

Calc

Excel

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

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

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

How to Un-install

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

Revision History

Open Office Calc

Rev

Date

Details

1.0

26-Aug-2015

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

Microsoft Excel

Rev

Date

Details

1.0

10-Nov-2003

Initial version released

Software for Download

Operating Sys.

Spreadsheet App

Software

MacOS Sequoia and earlier

Apache OpenOffice™ 4 Calc

tt03-002.ods

Ubuntu 12.04

LibreOffice 3

Win 7

Excel 2007

TT03-002.xla

MacOS Sequoia and earlier

Microsoft Excel for Mac

Please let me know if you have used my Linear Interpolation 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 Linear Interpolation Function (TT03-002) "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 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.

Back to Available Tarsier Tools