grade scale

[Harlan Grove]

It is an optimisation on Tushar's LinearInterp UDF, not an
optimisation in the sense of a shorter formula. ....
But your suggestion ....
is volatile, value-dependant and quite complex for a beginner, for
example.

....

Whereas writing udfs is easy for a beginner?

Either formulas are going to be black boxes or udfs are. Either way, a
beginner isn't going to know what's going on.

Also, you dredged my formula from a different branch. It is volatile, so
that's a fair point. It doesn't have to be. You could use the array formula

=FORECAST(A7,IF(ABS(ROW(X)-MIN(ROW(X))+0.5-MATCH(A7,X))<=0.5,Y),
IF(ABS(ROW(X)-MIN(ROW(X))+0.5-MATCH(A7,X))<=0.5,X))

which will be shorter than alternatives calling INDEX twice for both X and Y
ranges. Still, Lori's formula further in this branch looks much better,
likely the most efficient approach.

 

[Lori]

Thanks for the positive feedback. This has come up a few times and I
would be surprised if this combination wasn't considered previously.

Note however that the result applies when x and y values both
increase, and that changing the order does not affect the return
value. In practice TREND often makes sense when this is not the case.

On the other hand the formula is flexible and can be used with arrays,
whole columns, multiple or 3D-ranges.

 

[Bernd P]

Hello again,

The PERCENTILE(PERCENTRANK()) approach is about 20x faster than my UDF
but there is a serious issue:

Take -1, 1 as x-values. Take 1, -1 as y-values. And now look for 1 and
-1.

So I would be _very_ careful before using the
PERCENTILE(PERCENTRANK()) approach. You need to have positive x-values
(and even increasing y-values?), as it seems.

Regards,
Bernd

 

[Bernd P]

Hello Harlan,

I suggest to USE my UDF, not to write it. And the huge advantage is
encapsulation, separating data from the program (formula).

If speed will ever going to become an issue it's far easier to switch
to identical functions which reside in a DLL - compared to bulky &
clumsy formulas which mix algorithms and data.

Having seen the data constraints which lie behind the usage of
PERCENTILE(PERCENTRANK()) I definitely prefer my Interp UDF.

Regards,
Bernd

 

[Lori]

In many (most?) practical examples there is a one-to-one relationship
between variables. When this is not the case forecast/trend can be
used as has been mentioned before:

http://groups.google.co.uk/group/mi..._frm/thread/f71fa0b84352fbf6/a472084e237b61d9

which is basically Harlan's second formula using only one if
condition. This seems preferable to UDFs to me.

 

[Harlan Grove]

I suggest to USE my UDF, not to write it. And the huge advantage is
encapsulation, separating data from the program (formula).

....

Offset by the need to enable macros.

If speed will ever going to become an issue it's far easier to switch
to identical functions which reside in a DLL - compared to bulky &
clumsy formulas which mix algorithms and data.

And how easy would it be for the usual Excel beginner to write DLLs?

If you don't want to mix code and data, NEVER USE SPREADSHEETS. But the
converse holds: if you use spreadsheets, MIXING ALGORITHMS AND DATA IS
UNAVOIDABLE.

Having seen the data constraints which lie behind the usage of
PERCENTILE(PERCENTRANK()) I definitely prefer my Interp UDF.

You would have preferred your udf in any event.

 

[Harlan Grove]

The PERCENTILE(PERCENTRANK()) approach is about 20x faster than my UDF
but there is a serious issue:

Take -1, 1 as x-values. Take 1, -1 as y-values. And now look for 1 and
-1.

Big deal. Linear interpolation only makes sense when the tabular Y values
are monotonic, either all values nondecreasing or nonincreasing. When that's
the case, all that's needed is checking whether Y values increase or
decrease.

Given table ranges X and Y and lookup X value xx, use the array formula

=SIGN(INDEX(Y,COUNT(Y))-N(Y))*
PERCENTILE(SIGN(INDEX(Y,COUNT(Y))-N(Y))*Y,
PERCENTRANK(X,xx,308))

HAND

 

[Bernd P]

Hello Harlan,

I disagree, sorry.

....

You would have preferred your udf in any event.

....

It is not easy to prove an open mind to you, as it seems Actually,
I wanted to put the PERCENTILE(PERCENTRANK()) approach as a good
alternative onto my website. But even for positive increasing x-values
and decreasing y-values it fails:
x-values: 1; 2
y-values: 2; 1
Search for 1 and 2...

Regards,
Bernd

 

[Harlan Grove]

It is not easy to prove an open mind to you, as it seems . . .

Especially when it's closed.

I wanted to put the PERCENTILE(PERCENTRANK()) approach as a good
alternative onto my website. But even for positive increasing x-values
and decreasing y-values it fails:
x-values: 1; 2
y-values: 2; 1
Search for 1 and 2...

You haven't read my other response yet.

A1:A5 = {1;2;3;4;5}
B1:B5 = {10;6;3;1;0}

C1:C5 = {2.25;2.5;2.75;3.375;4.125}

D1 [array formula]:
=-PERCENTILE(-$B$1:$B$5,PERCENTRANK($A$1:$A$5,C1,15))

D1 filled down into D25, which produces

D15 = {5.25;4.5;3.75;2.25;0.875}

which agrees with manual linear interpolation.

And if you ever get around to reading my other response, you'll see there's
a way to make the formula adapt appropriately to Y increasing or decreasing
with X. Pretty simple, no?

 

[Bernd P]

Hello Harlan,

You developed a nice formula to represent zeros:
X-values -1; 0; 1
Y-values 1; 2; 1
xx-values -1; 0; 1 or -0.5; 0; 0.5

And if you apply any function/point metric your approach to save the
PERCENTILE(PERCENTRANK()) formula will converge to "death in
complexity", I am afraid.

Again: Please do not get me wrong. I think you are able to write
correct formulas and I know that you already developed quite brilliant
ones.

Regards,
Bernd

 

[Bernd P]

You developed a nice formula to represent zeros:

Because I do not agree that Y-values should be assumed monotonic...

Regards,
Bernd

 

[Harlan Grove]

Because I do not agree that Y-values should be assumed monotonic...

Mathematically, interpolation ONLY makes sense when the Y values are
either monotonically increasing or decreasing with X. Divided
difference formulas make far more sense when Y values vary increasing
then decreasing or vice versa with X.

If you want to claim your udf makes more sense in mathematically
invalid/absurd situations, I won't question your claim to such
distinction.

 

[Harlan Grove]

Mathematically, interpolation ONLY makes sense when the Y values are
either monotonically increasing or decreasing with X. Divided
difference formulas make far more sense when Y values vary increasing
then decreasing or vice versa with X.

....

OK, linear interpolation isn't INVALID for nonmonotonic functions, but it's
much less accurate. If you know you have extrema in the tabulated Y values,
you'd be FAR BETTER OFF using a spline to interpolate values between pairs
of (X,Y) points in the table.

If you want to see how to handle splines, see

http://groups.google.com/group/comp.apps.spreadsheets/msg/cfc03299cdc0a12b

 

[Rick Rothstein \(MVP - VB\)]

You developed a nice formula to represent zeros:

...

OK, linear interpolation isn't INVALID for nonmonotonic functions, but
it's much less accurate. If you know you have extrema in the tabulated Y
values, you'd be FAR BETTER OFF using a spline to interpolate values
between pairs of (X,Y) points in the table.

I'm glad you modified your statement there. Back when I was in grade/high
school (many years before hand-held calculators, let alone desktop
computers, were invented), we used linear interpolations when calculating
logarithms and anti-logarithms (which were use to simplify calculations
involving multiplication, division and powers as I recall). We also used
linear interpolations in the early years of my work life for trig function
evaluations (calculations for road design layout), although in those cases,
the look up tables provided answer to more (supposed) precision than the
number of decimal places we rounded our final answers to (main frame
computer access was expensive and limited back then so we used these large,
desk-top Monroe brand multi-button, mechanical crank-type calculators,
again, if memory serves me correctly).

Rick

 

[Harlan Grove]

On Jul 7, 2:29 pm, "Rick Rothstein \(MVP - VB\)" ...
....

. . . we used linear interpolations when calculating
logarithms and anti-logarithms . . .

Logarithms are monotonically increasing functions.

. . . We also used linear interpolations in the early years of
my work life for trig function evaluations . . .

Fine for TAN, which is monotonically increasing from -PI/2 to PI/2,
fine for SIN and COS not too near their extreme values (+/-1 at +/-PI/
2 for SIN and 0 and PI for COS). However, near their respective
extreme values, linear interpolation would give less accurate results.
Still, if you're talking about the slide rule era (I caught the tail-
end of it myself), 3 significant digits were all you could hope for,
and linear interpolation was accurate enough.

. . . although in those cases, the look up tables provided answer
to more (supposed) precision than the number of decimal places we
rounded our final answers to . . .

Didn't have a copy of Abromowitz & Stegun, eh?

Getting technical again, if there are extrema in the tabulated
function, then the magnitude of the second derivative will be
maximized at those extrema, and that implies the error from linear (2-
point) interpolating polynomials will be maximized.