grade scale
guy
I am having trouble even verbalizing this, but here it goes. I need:
80=1
90=2
100=3
etc, will the gradations in between (as in 85 = 1.5).
So, when 80 is entered, excel will produce the result 1, etc.
Hope this was even remotely clear.
Thanks,
Gord Dibben
=LOOKUP(A1,{80,85,90,95,100},{1,1.5,2,2.5,3})
Gord Dibben MS Excel MVP
MartinW
Try this,
A1 = 80
A2 = 90
A3 = 100
B1 = 1
B2 = 2
B3 = 3
C1 = 85
D1 = =TREND(B1:B3,A1:A3,C1)
85 in C1 will return 1.5
86 in C1 will return 1.6
92 in C1 will return 2.2
etc. etc.
HTH
Martin
"guy" <hombr...@gmail.com> wrote in message
news:1183249234.1...@k29g2000hsd.googlegroups.com...
Gord Dibben
Very clever use of the TREND function.
Gord
MartinW
I'm just helping spread the knowledge. Can't remember
who it was put me onto this one but probably Jerry Lewis
or Jon Peltier or the like.
Regards
Martin
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:2p2e83p4cirh6a59u...@4ax.com...
Bernd P
And now put 70,90,100 into column A and enter 80 into C1.
Would you expect 1.5714 in cell D1 now?
Regards,
Bernd
MartinW
For the way I use this function, Yes I would! That is because
I use a line of best fit through my data and a linear regression
trendline fits the bill very well. I might add it also fits the bill
for what the OP asked.
Depending on what outcome was needed I could also see
that you could use the FORECAST function in a way that basically
connects the data in a straight line dot to dot fashion.
I can also see that in some circumstances it maybe better to
use a polynomial trendline, in which case, you would need to
use the LINEST function and some helper cells to achieve
the desired result.
I would appreciate your thoughts on whether that is correct
or if there are better ways to handle this type of problem.
I am particularly interested in any ways to simplify the
LINEST polynomial approach.
Regards
Martin
"Bernd P" <bplu...@gmail.com> wrote in message
news:1183276407.8...@n2g2000hse.googlegroups.com...
Just Merks
Try =(A1-70)/10 were A1 is the input. (KIS= Keep it simple)
regards,
Just
"guy" <hombr...@gmail.com> schreef in bericht
news:1183249234.1...@k29g2000hsd.googlegroups.com...
Bernd P
IMHO you just need a linear interpolation.
Which is a piecewise TREND (take the surrounding points for TREND
only, not all of them).
Regards,
Bernd
MartinW
OK so in the given example, using either TREND or FORECAST
I would set up 2 helper cells say G1 and G2 like this,
G1 : =IF(AND(C1>=70,C1<=90),TREND(B1:B2,A1:A2,C1),0)
G2 : =IF(AND(C1>=90,C1<=100),TREND(B2:B3,A2:A3,C1),0)
Then in D1 put: =MAX(G1:G2)
Is this what you mean by a piecewise TREND? and...
Is there a more elegant way of doing this?
Regards
Martin
"Bernd P" <bplu...@gmail.com> wrote in message
news:1183300505....@n2g2000hse.googlegroups.com...
Pete_UK
suggest a slight amendment:
=If(A1<80,"n/a",ROUND((A1-70)/10,1))
depending on what the OP wants to do with values less than 80.
Pete
On Jul 1, 11:01 am, "Just Merks" <jvw.me...@hccnet.nl> wrote:
> Hi,
>
> Try =(A1-70)/10 were A1 is the input. (KIS= Keep it simple)
>
> regards,
>
> Just
>
> "guy" <hombrea...@gmail.com> schreef in berichtnews:1183249234.1...@k29g2000hsd.googlegroups.com...
>
>
>
> > Hi,
>
> > I am having trouble even verbalizing this, but here it goes. I need:
>
> > 80=1
> > 90=2
> > 100=3
>
> > etc, will the gradations in between (as in 85 = 1.5).
>
> > So, when 80 is entered, excel will produce the result 1, etc.
>
> > Hope this was even remotely clear.
>
> > Thanks,- Hide quoted text -
>
> - Show quoted text -
Bernd P
Yes, that's what I meant.
Maybe not more elegant but more general (the interpolation):
http://www.tushar-mehta.com/excel/newsgroups/interpolation/index.html
In this example I would not extrapolate because x-values beyond 80-100
should not appear. Take Pete's precaution if you want to be on the
safe side.
Regards,
Bernd
Harlan Grove
>Martin
>
>Very clever use of the TREND function.
>On Sun, 1 Jul 2007 11:47:01 +1000, "MartinW" <mt...@hotmail.invalid> wrote:
>>A1 = 80
>>A2 = 90
>>A3 = 100
>>B1 = 1
>>B2 = 2
>>B3 = 3
>>
>>C1 = 85
>>D1 = =TREND(B1:B3,A1:A3,C1)
This works in this case due to uniform increments in the both ranges
(tabular representation of a LINEAR function). If the ranges had been 0, 10,
25 and 0, 1, 2, the formula (so a nonlinear function)
=TREND({0;1;2},{0;10;25},5)
would return 0.464285714285714 rather than 0.5. For linear interpolation
within general tables, you need to use 'ranges' with just the two
bracketting entries. In this case, something like
=TREND(OFFSET(B1:B3,MATCH(85,A1:A3)-1,0,2,1),
OFFSET(A1:A3,MATCH(85,A1:A3)-1,0,2,1),85)
MartinW
Regards
Martin
"Bernd P" <bplu...@gmail.com> wrote in message
news:1183316491.8...@n60g2000hse.googlegroups.com...
Bernd P
http://www.sulprobil.com/html/interpolate.html
Harlan Grove
>A small optimisation:
>http://www.sulprobil.com/html/interpolate.html
Optimization only in the sense of shorter formulas. This url points to a web
page that shows a udf named interp, but udfs are always slower than built-in
functions by such a big gap that several nested built-in functions in a
formula will often recalculate faster than one udf.
Bernd P
It is an optimisation on Tushar's LinearInterp UDF, not an
optimisation in the sense of a shorter formula.
I know that you know Excel's calculation secrets. And I like many of
your array- or matrix-formulas.
But your suggestion
=TREND(OFFSET(B1:B3,MATCH(85,A1:A3)-1,0,2,1),
OFFSET(A1:A3,MATCH(85,A1:A3)-1,0,2,1),85)
is volatile, value-dependant and quite complex for a beginner, for
example.
If somebody uses such an approach in a wider environment he might get
lost in complexity easily.
Regarding speed: Let me come back to you with FastExcel and analyze
some of your array- or matrix formulas in future :-)
Have fun,
Bernd
Lori
=PERCENTILE(B1:B3,PERCENTRANK(A1:A3,85,308))
It's like "index...match..." only using percentages instead of
indices, and applies to any sorted data set. 308 just means use full
precision.
MartinW
I have been using a piecewise (didn't know that is what it's called)
TREND for over a year now and despite the undoubted knowledge
of Harlan, Bernd and Tushar I had decided to stick with it.
I applied my approach to Tushar's example at the above mentioned website and
got the same result with 7 helper cells containing 2 fairly simple formulae.
No need for playing with VBA and UDF's.
Your simple (Not) formula does it all in one hit. I was falsely accused
of being clever at the start of this thread but your solution truly is
CLEVER!
I hope the OP got as much out of this thread as I did.
Thank You
Martin
"Lori" <lorimer...@hotmail.com> wrote in message
news:1183540499.8...@n60g2000hse.googlegroups.com...
Bernd P
Fine approach for interpolation!
Extrapolation does not work, though.
Regards,
Bernd
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
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.
On 4 Jul, 11:09, "MartinW" <m...@hotmail.invalid> wrote:
> Now that is elegance Lori.
>
> I have been using a piecewise (didn't know that is what it's called)
> TREND for over a year now and despite the undoubted knowledge
> of Harlan, Bernd and Tushar I had decided to stick with it.
>
> I applied my approach to Tushar's example at the above mentioned website and
> got the same result with 7 helper cells containing 2 fairly simple formulae.
> No need for playing with VBA and UDF's.
>
> Your simple (Not) formula does it all in one hit. I was falsely accused
> of being clever at the start of this thread but your solution truly is
> CLEVER!
>
> I hope the OP got as much out of this thread as I did.
>
> Thank You
> Martin
>
> "Lori" <lorimer_mil...@hotmail.com> wrote in message
>
> news:1183540499.8...@n60g2000hse.googlegroups.com...
>
>
>
> > Another option for a linear fit is:
>
> > =PERCENTILE(B1:B3,PERCENTRANK(A1:A3,85,308))
>
> > It's like "index...match..." only using percentages instead of
> > indices, and applies to any sorted data set. 308 just means use full
> > precision.
>
> > On 4 Jul, 07:07, Bernd P <bplumh...@gmail.com> wrote:
> >> Hello Harlan,
>
> >> It is an optimisation on Tushar's LinearInterp UDF, not an
> >> optimisation in the sense of a shorter formula.
>
> >> I know that you know Excel's calculation secrets. And I like many of
> >> your array- or matrix-formulas.
>
> >> But your suggestion
> >> =TREND(OFFSET(B1:B3,MATCH(85,A1:A3)-1,0,2,1),
> >> OFFSET(A1:A3,MATCH(85,A1:A3)-1,0,2,1),85)
> >> is volatile, value-dependant and quite complex for a beginner, for
> >> example.
>
> >> If somebody uses such an approach in a wider environment he might get
> >> lost in complexity easily.
>
> >> Regarding speed: Let me come back to you with FastExcel and analyze
> >> some of your array- or matrix formulas in future :-)
>
> >> Have fun,
> >> Bernd- Hide quoted text -
Bernd P
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
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
between variables. When this is not the case forecast/trend can be
used as has been mentioned before:
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
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 D2:D5, which produces
D1:D5 = {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
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
Because I do not agree that Y-values should be assumed monotonic...
Regards,
Bernd
Harlan Grove
>>You developed a nice formula to represent zeros:
>
>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
>Bernd P <bplumh...@gmail.com> wrote...
>>>You developed a nice formula to represent zeros:
>>
>>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.
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)
>>>
>>>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.
> ...
>
> 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
...
> . . . 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.