|
VLINTERP2 function
VLINTERP2 provides efficient linear interpolation in a table of values.
The VLINTERP2 function is similar to VLOOKUP, except that it calculates linear interpolations between the values in a lookup table if an exact match cannot be found.
A single Lookup_Value and Col_Return_Nums will return a single interpolated value.
Multiple lookup values will return a column of values.
Multiple Col_Return_Nums will return a row of values.
VLINTERP2 is a multi-threaded, non-volatile array function.
VLINTERP2 Syntax
VLINTERP2(Lookup_Values, Table_Values, Col_Return_Nums, Lookup_ColNum, Extrapolate)
Lookup_Values
The value(s) to lookup in the Lookup_ColNum column of Table_Values. Multiple values will produce a column of results corresponding to the Lookup_Values.
If an exact match is found then VLINTERP2 will use that row.
If no exact match is found VLINTERP2 will interpolate between the row that contains the largest value that is smaller than lookup_row_value, and the row that contains the smallest value that is larger than lookup_row_value.
Table_Values
A rectangular set of values to be used for the interpolation. The first column must contain the values to be looked up. The other columns contain the values to be interpolated between.
Col_Return_Nums
The column number to return the interpolated values from, where the first column in Table_Values is 1. Multiple column numbers will produce a row of results corresponding to the requested columns.
Lookup_ColNum
Optional – Default 1. The column number to find the Lookup_Values from, where the first column in Table_Values is 1.
Extrapolate
Optional – True (default) to allow extrapolation beyond Table_Values
Calculation Method
If lookup_value1 and lookup_value2 are the two values found by the lookup in column 1, and interp_value1 and Interp_Value2 are the corresponding values from column Col_Return_Nums, then:
VLINTERP2=
interp_value1 + (interp_value2 – interp_value1) *
((Lookup_row_value-lookup_value1)/(lookup_value2-lookup_value1))
Remarks
Table_Values must be a rectangular set of values (range, array or expression) sorted by ascending on the Lookup column. It should not contain empty values in the Lookup column.
Table_Values should not contain column labels.
When using more than one Lookup_Value or Col_Return_Num VLINTERP2 should be entered as an array formula using Control/Shift/Enter.
VLINTERP2 returns #NA if:
Extrapolate is FALSE and
Lookup_row_value is greater than the largest value in the first column of Table_Values.
Lookup_row_value is less than the smallest value in the first column of Table_Values..
VLINTERP2 returns #VALUE if any of the used input values are not numeric, or if Lookup_ColNum is greater than the number of columns in Table_Values
|
