grade scale

[guy]

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,

 

[Gord Dibben]

Enter this in a cell. Adjust ranges and scores to suit.

=LOOKUP(A1,{80,85,90,95,100},{1,1.5,2,2.5,3})


Gord Dibben MS Excel MVP

 

[MartinW]

Hi Guy,

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

 

[Gord Dibben]

Martin

Very clever use of the TREND function.


Gord

 

[MartinW]

Thanks Gord, but the clever part comes from these groups
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

 

[Bernd P]

Hello Martin,

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]

Hi Bernd,

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

 

[Just Merks]

Hi,

Try =(A1-70)/10 were A1 is the input. (KIS= Keep it simple)

regards,

Just

 

[Bernd P]

Hello Martin,

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]

Hi Bernd,

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

 

[Pete_UK]

Exactly what I was thinking as I read through the thread. I would
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

 

[Bernd P]

Hi Martin,

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. ....

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]

OK Thanks Bernd.

Regards
Martin

 

[Bernd P]

A small optimisation:
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]

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

 

[Lori]

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.

 

[MartinW]

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

 

[Bernd P]

Hello Lorimer,

Fine approach for interpolation!

Extrapolation does not work, though.

Regards,
Bernd