I need elegance.....

M

MDBJ

I'm stumped and asking for elegance here....

I have a table that doesn't scale in a linear fashion
(it is for use with computing the langolier pool water index)
when I check the water I get a test result to compare to my table,
and I then take the result from the other side for further computation

32 0
37 0.1
46 0.2
53 0.3
60 0.4
66 0.5
76 0.6
84 0.7
94 0.8
105 0.9


what I want is for a test result like 35- I'd like to get a formula
response that is .06

so I'd like a formula that finds
which two table numbers it falls between, then computes the scale in
difference
(the steps between the higher & lower chart numbers)
then adds the # of steps necassary from the diff of my test result and the
lower table number.

i.e. if test result is 79--
1st- find that the result is between 76 & 84 on the table
2nd from 76 to 84 (8 digits) the steps are .0125 each
3rd 79 is three more steps than 76- so my answer is the .6 from 76 (on the
table) + 3 steps of .0125 or final answer .6375

I did in fact, when attempting this the first time, create on a second
worksheet a strict list of the table
from 32 to 105, and computed each individual # result.
(creating a new table 73 lines long)
the problem is- my next 2 table sets would be 995 lines long....
thanks for any ideas...

5 0.3
25 1
50 1.3
75 1.5
100 1.6
125 1.7
150 1.8
200 1.9
250 2
300 2.1
400 2.2
800 2.5
1000 2.6
 
N

Niek Otten

Use this User Defined Function (UDF)
If you're new to VBA, look at the instructions at the end of this post

' ====================================
Function TabInterpol(ToFind As Double, Table As Range) As Double
Dim RowNrLow As Long
Dim RowNrHigh As Long
Dim TableEntryLow As Double
Dim TableEntryHigh As Double
Dim ToFindLow As Double
Dim ToFindHigh As Double
Dim i As Long
Dim a As Double

For i = 1 To Table.Rows.Count
a = Application.WorksheetFunction.Index(Table, i, 1)
If a >= ToFind Then
RowNrLow = i - 1
Exit For
End If
Next i

RowNrHigh = RowNrLow + 1
TableEntryLow = Application.WorksheetFunction.Index(Table, RowNrLow, 2)
TableEntryHigh = Application.WorksheetFunction.Index(Table, RowNrHigh, 2)
ToFindLow = Application.WorksheetFunction.Index(Table, RowNrLow, 1)
ToFindHigh = Application.WorksheetFunction.Index(Table, RowNrHigh, 1)
TabInterpol = TableEntryLow + (ToFind - ToFindLow) / (ToFindHigh - ToFindLow) _
* (TableEntryHigh - TableEntryLow)
End Function

' ====================================

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm stumped and asking for elegance here....
|
| I have a table that doesn't scale in a linear fashion
| (it is for use with computing the langolier pool water index)
| when I check the water I get a test result to compare to my table,
| and I then take the result from the other side for further computation
|
| 32 0
| 37 0.1
| 46 0.2
| 53 0.3
| 60 0.4
| 66 0.5
| 76 0.6
| 84 0.7
| 94 0.8
| 105 0.9
|
|
| what I want is for a test result like 35- I'd like to get a formula
| response that is .06
|
| so I'd like a formula that finds
| which two table numbers it falls between, then computes the scale in
| difference
| (the steps between the higher & lower chart numbers)
| then adds the # of steps necassary from the diff of my test result and the
| lower table number.
|
| i.e. if test result is 79--
| 1st- find that the result is between 76 & 84 on the table
| 2nd from 76 to 84 (8 digits) the steps are .0125 each
| 3rd 79 is three more steps than 76- so my answer is the .6 from 76 (on the
| table) + 3 steps of .0125 or final answer .6375
|
| I did in fact, when attempting this the first time, create on a second
| worksheet a strict list of the table
| from 32 to 105, and computed each individual # result.
| (creating a new table 73 lines long)
| the problem is- my next 2 table sets would be 995 lines long....
| thanks for any ideas...
|
| 5 0.3
| 25 1
| 50 1.3
| 75 1.5
| 100 1.6
| 125 1.7
| 150 1.8
| 200 1.9
| 250 2
| 300 2.1
| 400 2.2
| 800 2.5
| 1000 2.6
|
|
|
|
|
 
M

MDBJ

Three Lefts said:
Did you mean "langelier" water pool index, or are you in a Stephen
King novel? ;-)
Click to expand...

Yes, you are correct.

thank you for your prompt attention in addressing the most critical portion
of my posting.

I really do appreciate it.
 
M

Mike Tordoff

It looks like the original table is a second order polynomial function that
has been rounded to the nearest integer or tenth of an integer. The
following formula should give you a close enough answer

=-0.54128+0.01848*A1-0.000045*A1^2

where A1 is the cell containing the value you want to convert
 
H

Harlan Grove

Niek Otten said:
Use this User Defined Function (UDF)
Click to expand...
....

Why a udf? The OP's problem requires simple linear interpolation. If
the OP's table were in a range named Tbl, and the measured value
entered in a cell named MV, the resulting interpolated index value
would be given by the formulas

=((INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-MV)*VLOOKUP(MV,Tbl,2)
+(MV-VLOOKUP(MV,Tbl,1))*INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,2))
/(INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-VLOOKUP(MV,Tbl,1))

or

=TREND(OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,1,2,1),
OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,0,2,1),MV)

or if you use another cell named FI containing the formula

=MATCH(MV,INDEX(Tbl,0,1))

you could return the desired result with the formula

=TREND(INDEX(Tbl2,FI,2):INDEX(Tbl2,FI+1,2),
INDEX(Tbl2,FI,1):INDEX(Tbl2,FI+1,1),MV)


Perhaps none of these is elegant, but linear interpolation into a
table with the key values in the leftmost column is elementary. IMO,
simplicity is lost in your udf due to overly long variable names which
distract and obscure. Also, the udf should include initial error
checking to catch common errors.

I can't see any benefit to using a For loop repeatedly calling
Application.WorksheetFunction.Index vs a simple assignment calling
Application.WorksheetFunction.MATCH once. Also no clear benefit to
multiple Application.WorksheetFunction.Index calls to fetch table
values rather than using the .Cells property of the table range
object.

Finally, this is simple to generalize to tables containing more than 2
columns which would allow the user to specify which column to
interpolate. Default to the presumably most common 2nd column.


Function lininterp( _
x As Double, _
tbl As Range, _
Optional ycol As Long = 2 _
) As Variant
'------------------------
Dim k As Long
Dim xlo As Double, xhi As Double, ylo As Double, yhi As Double

If tbl.Rows.Count < 2 Or tbl.Columns.Count < ycol Then
lit = CVErr(xlErrRef)
Exit Function
End If

If x < tbl.Cells(1, 1).Value2 _
Or x >= tbl.Cells(t.Rows.Count, 1).Value2 Then
lit = CVErr(xlErrNA)
Exit Function
End If

k = Application.WorksheetFunction.Match(x, tbl.Resize(, 1))

xlo = tbl.Cells(k, 1).Value2
xhi = tbl.Cells(k + 1, 1).Value2
ylo = tbl.Cells(k, ycol).Value2
yhi = tbl.Cells(k + 1, ycol).Value2

lininterp = ((xhi - x) * ylo + (x - xlo) * yhi) / (xhi - xlo)

End Function


[Digression: maybe long variable names serve some purpose in long and/
or complex nonmathematical procedures, but they're decidedly
deleterious in simple mathematical procedures.]
 
R

Ron Rosenfeld

I'm stumped and asking for elegance here....

I have a table that doesn't scale in a linear fashion
(it is for use with computing the langolier pool water index)
when I check the water I get a test result to compare to my table,
and I then take the result from the other side for further computation

32 0
37 0.1
46 0.2
53 0.3
60 0.4
66 0.5
76 0.6
84 0.7
94 0.8
105 0.9


what I want is for a test result like 35- I'd like to get a formula
response that is .06

so I'd like a formula that finds
which two table numbers it falls between, then computes the scale in
difference
(the steps between the higher & lower chart numbers)
then adds the # of steps necassary from the diff of my test result and the
lower table number.

i.e. if test result is 79--
1st- find that the result is between 76 & 84 on the table
2nd from 76 to 84 (8 digits) the steps are .0125 each
3rd 79 is three more steps than 76- so my answer is the .6 from 76 (on the
table) + 3 steps of .0125 or final answer .6375

I did in fact, when attempting this the first time, create on a second
worksheet a strict list of the table
from 32 to 105, and computed each individual # result.
(creating a new table 73 lines long)
the problem is- my next 2 table sets would be 995 lines long....
thanks for any ideas...

5 0.3
25 1
50 1.3
75 1.5
100 1.6
125 1.7
150 1.8
200 1.9
250 2
300 2.1
400 2.2
800 2.5
1000 2.6
Click to expand...

Set up two columns:

Reading LangIndex
32 0
37 0.1
46 0.2
53 0.3
60 0.4
66 0.5
76 0.6
84 0.7
94 0.8
105 0.9

I selected the table, and then NAME'd the columns using the column labels at
the top. (So if the table started in A1, Reading would refer to: A2:A11 in
this example).

Then use the formula:

=FORECAST(TestValue,OFFSET(Reading,-1+MATCH(Value,Reading),1,2),
OFFSET(Reading,-1+MATCH(Value,Reading),0,2))

This should work for MIN(Reading)<=TestValue<MAX(Reading)


--ron
 
L

Lori

=percentile(b:b,percentrank(a:a,c1,30))

assuming data in the first two columns e.g. c1=79 gives 0.6375.
 
N

Niek Otten

<Why a udf?>

Because

=TabInterpol(10,A1:A20)

is easier to remember than

=((INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-MV)*VLOOKUP(MV,Tbl,2)
+(MV-VLOOKUP(MV,Tbl,1))*INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,2))
/(INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-VLOOKUP(MV,Tbl,1))

Thanks very much for your comments on my programming style, although I'm not entirely convinced that will help the OP solving
his/her problem.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| >Use this User Defined Function (UDF)
| ...
|
| Why a udf? The OP's problem requires simple linear interpolation. If
| the OP's table were in a range named Tbl, and the measured value
| entered in a cell named MV, the resulting interpolated index value
| would be given by the formulas
|
| =((INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-MV)*VLOOKUP(MV,Tbl,2)
| +(MV-VLOOKUP(MV,Tbl,1))*INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,2))
| /(INDEX(Tbl,MATCH(MV,INDEX(Tbl,0,1))+1,1)-VLOOKUP(MV,Tbl,1))
|
| or
|
| =TREND(OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,1,2,1),
| OFFSET(Tbl,MATCH(MV,INDEX(Tbl,0,1))-1,0,2,1),MV)
|
| or if you use another cell named FI containing the formula
|
| =MATCH(MV,INDEX(Tbl,0,1))
|
| you could return the desired result with the formula
|
| =TREND(INDEX(Tbl2,FI,2):INDEX(Tbl2,FI+1,2),
| INDEX(Tbl2,FI,1):INDEX(Tbl2,FI+1,1),MV)
|
|
| Perhaps none of these is elegant, but linear interpolation into a
| table with the key values in the leftmost column is elementary. IMO,
| simplicity is lost in your udf due to overly long variable names which
| distract and obscure. Also, the udf should include initial error
| checking to catch common errors.
|
| I can't see any benefit to using a For loop repeatedly calling
| Application.WorksheetFunction.Index vs a simple assignment calling
| Application.WorksheetFunction.MATCH once. Also no clear benefit to
| multiple Application.WorksheetFunction.Index calls to fetch table
| values rather than using the .Cells property of the table range
| object.
|
| Finally, this is simple to generalize to tables containing more than 2
| columns which would allow the user to specify which column to
| interpolate. Default to the presumably most common 2nd column.
|
|
| Function lininterp( _
| x As Double, _
| tbl As Range, _
| Optional ycol As Long = 2 _
| ) As Variant
| '------------------------
| Dim k As Long
| Dim xlo As Double, xhi As Double, ylo As Double, yhi As Double
|
| If tbl.Rows.Count < 2 Or tbl.Columns.Count < ycol Then
| lit = CVErr(xlErrRef)
| Exit Function
| End If
|
| If x < tbl.Cells(1, 1).Value2 _
| Or x >= tbl.Cells(t.Rows.Count, 1).Value2 Then
| lit = CVErr(xlErrNA)
| Exit Function
| End If
|
| k = Application.WorksheetFunction.Match(x, tbl.Resize(, 1))
|
| xlo = tbl.Cells(k, 1).Value2
| xhi = tbl.Cells(k + 1, 1).Value2
| ylo = tbl.Cells(k, ycol).Value2
| yhi = tbl.Cells(k + 1, ycol).Value2
|
| lininterp = ((xhi - x) * ylo + (x - xlo) * yhi) / (xhi - xlo)
|
| End Function
|
|
| [Digression: maybe long variable names serve some purpose in long and/
| or complex nonmathematical procedures, but they're decidedly
| deleterious in simple mathematical procedures.]
 
M

MDBJ

wow.

I've now read up on the percentile & percentrank functions,
picked apart the statement and I see how this works-
that is amazing- I am dumbfounded.. I haven't comprehended/absorbed this
into my
understanding of excel yet, but I must say- you have definately hit my
desire to a T

I was expecting something I'd have to merge with an if statement for when it
was a perfect table match result--
but that is an absolute killer, take a bow-- thank you, both for the answer
and the education....

Just wow.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF and AND statements 4
how do I get excel to round to specific numbers? 3
Two Calculations in one cell? 3
Finding a number in a table? 10
countif value falls in a range of possible values 1
Transpose question 5
Need a lookup querry 1
I need to find a querry to do this 8

Top