Thanks for the reply and glad it helped.

For the second part, it should be F2 for the waterlvl lookup value, try this
instead. (i entered this in the cell below, F3, in my test).

You might want to try this in a simple interpolation example without the
extra criteria to see how it works. e.g. see
http://groups.google.com/group/micro...177508048be66d

"MarkFranklin" wrote:

Lori,

That is an amazing formula and works really well for calculating the values
where there is an exact match. However, I can't work out the significance
of "F3" in the second formula - I get a #NUM! error....

The idea is to use the values in "tbl" to lookup and interpolate
NS_Wave_height,NS_Wave_period, and NS_Wave_dir - I calculate WaterLvl
elsewhere as a input to the lookup.

Can you tell me how to fix the forumla? I have never used MMULT,
Percentrank, or percentile before - although I will be looking up these
functions to workout how you got these formulas.

Thanks for your help.

Mark

"Lori" wrote:

Let "Tbl" be the matrix (Hs,Ts,WaveDir,WindSpd,WaterLvl,WindDir)
Then with corresponding lookup values in B2:G2 of a new sheet, try entering
in H2:

=LOOKUP(2,1/(MMULT(--(Tbl=B2:G2),{1;1;1;1;1;1})=6),NS_Wave_Height)

and similar formulas for NS Wave Period, NS Wave Dir & Ref.
which should give 0.61814, 2.2983, 254.577& aabbgb respectively.
[You can use insertNamesCreate to create the column names]
For interpolating water level values try:

=PERCENTILE(IF(MMULT(--(Tbl=B2:G2),{1;1;1;1;0;1})=5,NS_Wave_Height),
PERCENTRANK(IF(MMULT(--(Tbl=B2:G2),{1;1;1;1;0;1})=5,WaterLvl),F3,30))

With WaterLvl =6.25, NS Wave Height=0.4535075,NS Wave Period=1.88265
(WaveDir is not reliable as it fluctuates and can cross 360 to 0.)