TriLookup Examples

[Click here to see a screen capture of TVPOLYDATA interactive example]

4. TVPOLYDATA & TPOLY

The 10-row by 4-column lookup table below is set up for use by the TVPOLYDATA function. It contains column titles in the topmost row and the lookup (X) and return (Y) values in the remaining 9 rows. TVPOLYDATA uses the column titles in order to identify the lookup column and return column.

For lookup (X) column "X" and return (Y) column "Y1" calculate the coefficients of all polynomial curves; maximum order = 8; use array formula.

{=TVPOLYDATA($A$6:$D$15,"X","Y1",8)} equals:

Note that, in the above example, you can also use the column index numbers to select the lookup and return columns (1 instead of “X” and 2 instead of “Y1”), by specifying Index_mode = TRUE:

{=TVPOLYDATA($A$6:$D$15,1,2,8,,,,,TRUE)}

The following strings are returned in the "TPOLY(…)" and "Formula" rows in the above table for Order = 4:

TPOLY(X,4,{3.4434310371708,-0.82230762016921,0.328609594844368,-4.04670971229965E-02,1.04673785827208E-03})

3.4434310371708-0.82230762016921*X+0.328609594844368*X^2-4.04670971229965E-02*X^3+1.04673785827208E-03*X^4

Same as above, but specify Smooth_R2 = TRUE to use an additional point in-between each two table points when calculating R² and Sum(E²). Note that in this case the best fit is achieved with a the 5th order curve.

{=TVPOLYDATA($A$6:$D$15,"X","Y1",8,TRUE)} equals:

Same as above, but display only the best fit curve.

{=TVPOLYDATA($A$6:$D$15,"X","Y1",8, TRUE)} equals:

For lookup (X) column "X" and return (Y) column "Y3" return the coefficients the best fit polynomial curve; maximum order = 8; use array formula.

{=TVPOLYDATA($A$6:$D$15,"X","Y3",8)} equals:

Same as above, but add a cell with the same formula with Error_msg = TRUE to return a detailed text error message indicating the cause of the error.

=TVPOLYDATA($A$6:$D$15,"X","Y3",8,,,,TRUE) equals:
#VALUE! {Err.402} 2 missing or invalid cells found in Return column titled "Y3" (all cells must be numeric for Missing_pts = FALSE).

Same as above, but set Missing_pts = TRUE. Note that the maximum order has been reduced to 6 because of the two missing X-Y points.

{=TVPOLYDATA($A$6:$D$15,"X","Y3",8,,,TRUE)} equals:

Previous 1 2 3 4