I need elegance.....
MDBJ
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
Three Lefts
>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)
Did you mean "langelier" water pool index, or are you in a Stephen
King novel? ;-)
Niek Otten
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
"MDBJ" <m...@no.com> wrote in message news:uF0cGDpk...@TK2MSFTNGP03.phx.gbl...
MDBJ
"Three Lefts" <spam...@spamtrap.invalid> wrote in message
news:g1nvu358alr7g64e4...@4ax.com...
> On Sun, 30 Mar 2008 14:06:26 -0400, "MDBJ" <m...@no.com> wrote:
>
>>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)
>
> Did you mean "langelier" water pool index, or are you in a Stephen
> King novel? ;-)
Yes, you are correct.
thank you for your prompt attention in addressing the most critical portion
of my posting.
I really do appreciate it.
Mike Tordoff
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
"Niek Otten" <nico...@xs4all.nl> wrote in message
news:OogwAZp...@TK2MSFTNGP06.phx.gbl...
Harlan Grove
>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.]
Ron Rosenfeld
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
Three Lefts
Apparently, you couldn't find any humor in it and you couldn't ignore
it.
>I really do appreciate it.
Hey, my main goal in life is to help you out. Which way did you come
in?
Lori
assuming data in the first two columns e.g. c1=79 gives 0.6375.
Niek Otten
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
"Harlan Grove" <hrl...@gmail.com> wrote in message news:3132fd3b-f45f-4279...@s13g2000prd.googlegroups.com...
Ron Rosenfeld
wrote:
>=percentile(b:b,percentrank(a:a,c1,30))
>
>assuming data in the first two columns e.g. c1=79 gives 0.6375.
Very nice!
--ron
Roger Govier
Very nice indeed Lori
--
Regards
Roger Govier
"Lori" <Lo...@discussions.microsoft.com> wrote in message
news:C33D2C73-1220-4143...@microsoft.com...
MDBJ
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.
"Lori" <Lo...@discussions.microsoft.com> wrote in message
news:C33D2C73-1220-4143...@microsoft.com...