Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

2D / Bilinear Interpolation

Dear all,

I have a dataset of 360 columns and 180 rows in Excel 2007 and I want to increase the resolution of the dataset by 2 (i.e. I want to insert a value between each pair of cells, both vertically and horizontally). The calculation (interpolation) need not be complicated - I was thinking along the lines of a simple bilinear interpolation, where each new value is based on an average of the 4 cells surrounding it.

I'm fairly new to this type of operation in Excel, so I'd appreciate it if any responses I receive aren't too vague!

Many thanks!!

smurray444


Post your answer or comment

comments powered by Disqus
Hi. Downloaded your program for bilinear interpolation. (Function BiLinterp and BiLinterp2) it works fine.
My problem is that my Tbl. is in descending order left to right by the first row and top to bottom by the first column.
Please advise what programsteps needs to be changed, and the change.
Your help is appreciated.
Sincerely
ehcet

[FONT=Arial]I am trying to map temperature data from one 2d grid to another 2d grid. I have the node, coordinate, and temperature data for the first set (points 1 through 4) and would like to find the interpolated temperature in the closest coordinate location on the second set of data (point A). Is there an easy way to do this? I've tried to illustrate what I mean below.

Y
|
|
| [/FONT][FONT=Arial] [SIZE=3]1[/SIZE] (T=100F) [SIZE=3]2[/SIZE] (T=120F)
|
| [SIZE=3]A[/SIZE] (T=?)
|
| [SIZE=3]3[/SIZE] (T=93F) [SIZE=3]4[/SIZE] (T=125F)
|
|------------------------------------------------------------------------------------------- X[/FONT]
[FONT=Courier New]

Thank you.[/FONT]

I am trying to write a macro which will do a 2d linear interpolation if I give it as an input the x and y inputs I want and a range of cells which have x along the top, y down the side and the z values to be interpolated in the middle.

My code it below. If anyone can tell me why it doesn't work that'd be GREAT!

Thanks!


	VB:
	
 
     
    Dim nx As Long, ny As Long 
    Dim lowerx As Long, lowery As Long, upperx As Long, uppery As Long, i As Long 
    Dim inputsarray() As Variant 
     
    nx = inputs.Columns.Count - 1 
    ny = inputs.Rows.Count - 1 
    Redim inputsarray(nx + 1, ny + 1) 
    inputsarray = inputs.Value 
     
    If X < inputsarray(1, 0) Then 
        lowerx = 1 
        upperx = 1 
    ElseIf X > inputsarray(nx, 0) Then 
        lowerx = nx 
        upperx = nx 
    Else 
        For i = 1 To nx 
            If inputsarray(i, 0) >= X Then 
                upperx = i 
                lowerx = i - 1 
                Exit For 
            End If 
        Next 
    End If 
     
    If Y < inputsarray(0, 1) Then 
        lowery = 1 
        uppery = 1 
    ElseIf Y > inputsarray(0, ny) Then 
        lowery = ny 
        uppery = ny 
    Else 
        For i = 1 To ny 
            If inputsarray(0, i) >= Y Then 
                uppery = i 
                lowery = i - 1 
                Exit For 
            End If 
        Next 
    End If 
     
    Dim XL As Double, XU As Double, YL As Double, YU As Double 
    Dim temp1 As Double, temp2 As Double 
     
    XL = inputsarray(lowerx, 0) 
    XU = inputsarray(upperx, 0) 
    YL = inputsarray(0, lowery) 
    YU = inputsarray(0, uppery) 
    temp1 = (inputsarray(lowerx, lowery) * (XU - X) _ 
    + inputsarray(upperx, lowery) * (X - XL)) / (XU - XL) 
    temp2 = (inputsarray(lowerx, uppery) * (XU - X) _ 
    + inputsarray(upperx, uppery) * (X - XL)) / (XU - XL) 
    Linearinter22d = (temp1 * (YU - Y) + temp2 * (Y - YL)) / (YU - YL) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to write a macro which will do a 2d linear interpolation if I give it as an input the x and y inputs I want and a range of cells which have x along the top, y down the side and the z values to be interpolated in the middle.

My code it below. If anyone can tell me why it doesn't work that'd be GREAT!

Thanks!


	VB:
	
 
     
    Dim nx As Long, ny As Long 
    Dim lowerx As Long, lowery As Long, upperx As Long, uppery As Long, i As Long 
    Dim inputsarray() As Variant 
     
    nx = inputs.Columns.Count - 1 
    ny = inputs.Rows.Count - 1 
    Redim inputsarray(nx + 1, ny + 1) 
    inputsarray = inputs.Value 
     
    If X < inputsarray(1, 0) Then 
        lowerx = 1 
        upperx = 1 
    ElseIf X > inputsarray(nx, 0) Then 
        lowerx = nx 
        upperx = nx 
    Else 
        For i = 1 To nx 
            If inputsarray(i, 0) >= X Then 
                upperx = i 
                lowerx = i - 1 
                Exit For 
            End If 
        Next 
    End If 
     
    If Y < inputsarray(0, 1) Then 
        lowery = 1 
        uppery = 1 
    ElseIf Y > inputsarray(0, ny) Then 
        lowery = ny 
        uppery = ny 
    Else 
        For i = 1 To ny 
            If inputsarray(0, i) >= Y Then 
                uppery = i 
                lowery = i - 1 
                Exit For 
            End If 
        Next 
    End If 
     
    Dim XL As Double, XU As Double, YL As Double, YU As Double 
    Dim temp1 As Double, temp2 As Double 
     
    XL = inputsarray(lowerx, 0) 
    XU = inputsarray(upperx, 0) 
    YL = inputsarray(0, lowery) 
    YU = inputsarray(0, uppery) 
    temp1 = (inputsarray(lowerx, lowery) * (XU - X) _ 
    + inputsarray(upperx, lowery) * (X - XL)) / (XU - XL) 
    temp2 = (inputsarray(lowerx, uppery) * (XU - X) _ 
    + inputsarray(upperx, uppery) * (X - XL)) / (XU - XL) 
    Linearinter22d = (temp1 * (YU - Y) + temp2 * (Y - YL)) / (YU - YL) 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.

I have attached the workbook as it currently stands.

Hello again

For long time, I was using the macro provided by shg in post#6 of this thread: http://www.ozgrid.com/forum/showthread.php?t=64224

and post#13 answers of This Thread

The macro is:
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

	VB:
	
  Range, _ 
    dColVal As Double, dRowVal As Double) As Variant 
     ' Returns the bilinear interpolation of Tbl using dColVal and dRowVal
     
     ' Tbl must be in ascending order left to right by the first row,
     '    and top to bottom by the first column
     
    Dim nRow As Long, nCol As Long ' dimensions of table
    Dim iRow1 As Long, iRow2 As Long ' bounding rows
    Dim iCol1 As Long, iCol2 As Long ' bounding columns
    Dim rf As Double, cf As Double ' row and column fractions, 0..1
    Dim sL As Double, sR As Double ' column  header values flanking dColVal
    Dim sT As Double, sB As Double ' row header values flanking dRowVal
     
     ' four corner table values flanking dColVal, dRowVal
    Dim sTL As Double, sTR As Double, sBR As Double, sBL As Double 
     
    nRow = Tbl.Rows.Count 
    nCol = Tbl.Columns.Count 
     
     ' Tbl must be at least 3x3 including header row and column
    If nRow < 3 Or nCol < 3 Then 
        BiLinterp = "Tbl must be at least 3x3" 
        Exit Function '-------------------------------------------------------->
    End If 
     
     ' value to be interpolated must lie within row and column headers
    If dColVal < Tbl(1, 2) Or dColVal > Tbl(1, nCol) _ 
    Or dRowVal < Tbl(2, 1) Or dRowVal > Tbl(nRow, 1) Then 
        BiLinterp = "Value not within table extents" 
        Exit Function '-------------------------------------------------------->
    End If 
     
    iCol1 = Application.Match(dColVal, Tbl(1, 2).Resize(, nCol - 1)) + 1 
    sL = Tbl(1, iCol1) 
    If dColVal = sL Then 
        iCol2 = iCol1 
        sR = sL 
    Else 
        iCol2 = iCol1 + 1 
        sR = Tbl(1, iCol2) 
        cf = (dColVal - sL) / (sR - sL) ' column fraction
    End If 
     
    iRow1 = Application.Match(dRowVal, Tbl(2, 1).Resize(nRow - 1), 1) + 1 
    sT = Tbl(iRow1, 1) 
    If dRowVal = sT Then 
        iRow2 = iRow1 
        sT = sB 
    Else 
        iRow2 = iRow1 + 1 
        sB = Tbl(iRow2, 1) 
        rf = (dRowVal - sT) / (sB - sT) 
    End If 
     
    sTL = Tbl(iRow1, iCol1) 
    sTR = Tbl(iRow1, iCol2) 
    sBR = Tbl(iRow2, iCol2) 
    sBL = Tbl(iRow2, iCol1) 
     
     ' Compute the weighted  sum of four locations in Tbl
    BiLinterp = sTL * (1 - rf) * (1 - cf) _ 
    + sTR * (1 - rf) * cf _ 
    + sBR * rf * cf _ 
    + sBL * rf * (1 - cf) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

It was extremely helpful. However, I recently started to realize that in my case the macro needs to be slightly modified to extrapolate (in addition to the interpolation) for the values that are outside the given X and Y ranges (please see the attached file). At the begining I needed the function to do interpolation only but when I started to get X and Y values outside the ranges appearing in the table, I think the macro needs to be modified. More precisely, I would like the macro to account for X values 0.9 (in addition to 0

there is an excellent UDF for bilinear interpolation in this link

http://www.excelforum.com/excel-prog...rpolation.html

but if if the x value for example is beyond or less than the max or min values in the table the function gives
error value and in other version of the same function it gives ( x outside table extents)

is there any possibility to overcome this limit, i mean to modify the macro to deal with these values and calculate also extrapolation.
this off course with shg permission
or any other solution to do so

thanks

Hi guys,

Here's a challenge I hope you can help me solve.

Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.

Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40

Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.

From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I
am
finding this trickier.

Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.

Any suggestions are greatly appreciated.

Thanks
Edu

Hi,

I've got a table of 2D points with columns X and Y. The points are sorted in ascending order of their X coordinates. Given a particular X value, x*, I want a formula to do a lookup on the point table to find y*. If x* does not exist in the table, I want y* to be interpolated based on the two points either side of x* in the table. I've had a crack at this myself (see attached) which seems to work but the formula is really messy.

I am interested to know if there is a simpler way to do this.

Thanks,

Paul

Actually, I've created the user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output. I'm stumped, any suggestions? Below is my code.


	VB:
	
 
    Dim rnge, mtrnge As Range 
    Dim w, x, y, z, xx, yy, b As Single 
    Dim scenario, a As Integer 
    Dim J As Variant 
    scenario = Worksheets("Input").Range("B1").Value 
    pwr = pwr / 100# 
    If (scenario = 1) Then 
        Worksheets("ITC").Select 
         'Make table into a range for VLookUp
        Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight)) 
        Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown)) 
         'If the given value does not match a table value exactly
        On Error Resume Next 
        J = Application.WorksheetFunction.VLookup(efpd, rnge, 1, False) 
        If Err.Number = 1004 Then 
            w = Application.WorksheetFunction.VLookup(efpd, rnge, 1, True) 
            x = Application.WorksheetFunction.VLookup(efpd, rnge, 2, True) 
            y = Application.WorksheetFunction.VLookup(efpd, rnge, 3, True) 
             'If the given value is greater than, or equal to the largest table value
            If (w = Range("A3").End(xlDown).Value) Then 
                itcinter = x + pwr * (y - x) 
            Else 
                 'If the given value requires a table interpolation
                a = Application.WorksheetFunction.Match(efpd, mtrnge, 1) 
                z = Range("A" & a + 3).Value 
                xx = Range("B" & a + 3).Value 
                yy = Range("C" & a + 3).Value 
                b = (z - w) 
                itcinter = (x / b) * (z - efpd) * (1 - pwr) + _ 
                (xx / b) * (efpd - w) * (1 - pwr) + _ 
                (y / b) * (z - efpd) * (pwr - 0) + _ 
                (yy / b) * (efpd - w) * (pwr - 0) 
            End If 
             'If the given value does match a table value exactly
        ElseIf (Application.WorksheetFunction.VLookup(efpd, rnge, 1, False) >= 0) Then 
            a = Application.WorksheetFunction.Match(efpd, mtrnge, 0) 
            x = Range("B" & a + 2).Value 
            y = Range("C" & a + 2).Value 
            itcinter = x + pwr * (y - x) 
             'Only scenario left is an error
        Else 
            itcinter = "error" 
        End If 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm attempting multiple interpolations for the table below. This is merely a sub-set of the entire table. I also have a UDF for interpolation that accepts (5) variables [X1, Y1, X2, Y2, X] and return the interpolated value [Y]. I'm uncertain how the best method of returning an interpolated value from a 2D table given three criteria. R[1], R[2], and C[1] are the criteria. How best to return a value from the table given the criteria R[1]=75; R[2]=60; C[1]=2.5

******** ******************** ************************************************************************>Microsoft Excel - Lighting Zonal Cavity.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD4=
DEFGHIJKL4 80808080707070705 7050301070503010611.0921.0821.0751.0681.0771.0701.0641.059721.0791.0661.0551.0471.0681.0571.0481.039831.0701.0541.0421.0331.0611.0481.0371.028941.0621.0451.0331.0241.0551.0401.0291.0211051.0561.0381.0261.0181.0501.0341.0241.0151161.0521.0331.0211.0141.0471.0301.0201.0121271.0471.0291.0181.0111.0431.0261.0171.0091381.0441.0261.0151.0091.0401.0241.0151.0071491.0401.0241.0141.0071.0371.0221.0141.00615101.0371.0221.0121.0061.0341.0201.0121.005Sheet4 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi excel-users!

I have a problem (once again...):
I know INDEX and MATCH, of course.
But now I need to interpolate.
My data is like this:
ABCD1 51015252648713104997147415691482275 6 7 x12 8 y13 9 10 Value??? Now I need a formula in C10 that interpolates the existing entries in 1:1 and A:A, to receive a matching result.
The values are fictitious, a linear interpolation should be sufficient, since the original values are much more closer.

Thanks in advance for any links or solutions!

Hi All,

Basically, I need a UDF for 3D Interpolation.
I was able to make UDF for 2D Interpolation, but 3D is very difficult.

Scenario: I will have a table with X, Y, Z and appropriate value f(x,y,z). With given table, if I pick any number to X,Y,Z , then interpolated f(x,y,z) is generated.

I attached a file! so please have a look!

My table will be either Table 1 or Table 2 or anything else. Means, Range Y wouldn't be same, Interval wouldn't be same, etc.

Thank you in advance!

Chris

P.S
.pdf file might help you understanding mathematical concepts.

I've created a user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output. I'm stumped, any suggestions? Below is my code.

Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
    Worksheets("ITC").Select
    'Make table into a range for VLookUp
    Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
    Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
    'If the given value does not match a table value exactly
    On Error Resume Next
    J = Application.WorksheetFunction.VLookup(efpd, rnge, 1, False)
    If Err.Number = 1004 Then
        w = Application.WorksheetFunction.VLookup(efpd, rnge, 1, True)
        x = Application.WorksheetFunction.VLookup(efpd, rnge, 2, True)
        y = Application.WorksheetFunction.VLookup(efpd, rnge, 3, True)
        'If the given value is greater than, or equal to the largest table value
        If (w = Range("A3").End(xlDown).Value) Then
                itcinter = x + pwr * (y - x)
        Else
            'If the given value requires a table interpolation
            a = Application.WorksheetFunction.Match(efpd, mtrnge, 1)
            z = Range("A" & a + 3).Value
            xx = Range("B" & a + 3).Value
            yy = Range("C" & a + 3).Value
            b = (z - w)
            itcinter = (x / b) * (z - efpd) * (1 - pwr) + _
            (xx / b) * (efpd - w) * (1 - pwr) + _
            (y / b) * (z - efpd) * (pwr - 0) + _
            (yy / b) * (efpd - w) * (pwr - 0)
        End If
    'If the given value does match a table value exactly
    ElseIf (Application.WorksheetFunction.VLookup(efpd, rnge, 1, False) >= 0) Then
        a = Application.WorksheetFunction.Match(efpd, mtrnge, 0)
        x = Range("B" & a + 2).Value
        y = Range("C" & a + 2).Value
        itcinter = x + pwr * (y - x)
    'Only scenario left is an error
    Else
        itcinter = "error"
    End If
End If
End Function


Hi there,

Im not really familiar with excel, and i have one question, can anyone please help me 'cause im in hurry now.

I have a set data of (x,y- coordinates, z- velocity), and some other nodes (already known x,y) without knowing the z value. You can find the excel file in the attachment.

I wanna interpolate the z values of these nodes.

Any help would be appreciated!

Thank you so much.

Dear All,

I had a problem where I like to get the values from table having nos of column and rows also with interpolation of these. plz see the below table

First Row indicates the Bc/2H Ratio and first Col indicates the L/2H
My Question is I like the get the values for the in between values of Bc/2H, L/2H that to in separate sheet like

Bc/2H =.055 and L/2H =0.15

Bc/2H 0.0000 00.05 0.1 0.15 0.20 L/2H 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.0 0.01 0 0.019 0.028 0.037 0.2 0.0 0.019 0.037 0.055 0.072 0.3 0.0 0.027 0.053 0.078 0.103 0.4 0.0 0.034 0.063 0.099 0.131
Plse see the attachment for detailed question

Hi
Im trying to apply Linear Interpolation to the Exercise Price (Column A - assuming X axis) and the Imp Vol (Column F - assuming Y axis).

Attached is the data provided.

Assuming that I'm trying to look for the Impl Vol for the date "Jul-12" with the exercise price "12.7", using the logic that I have to pick the nearest month before Jul-12 (in this case, it will be Jun-12). And applying the exercise price of 12.7, i have to use the exercise price btw 12.5 and 13.

How can i apply an Excel formula to find the Imp Vol, by ensuring that the following is incorporated:

1. The nearest month/date
2. The correct range of exercise price
3. Linear Interpolation being applied

Thanks

I'm new to VBA coding & my project requires that i take in 2 coordinates ( X &Y) along with a third corresponding data and put that data in appropriate space dimensions in xy plane ( in a particular cell in EXCEL) in 2D form. To make it sample. Here's the input:

X Y Data 2 2 87.71266 6 2 91.94485 10 2 93.62936 14 2 94.33458 18 2 94.4084 22 2 93.88046 26 2 92.52193 30 2 89.39275 2 6 91.21322 6 6 94.38502 10 6 95.63327 14 6 96.13453 18 6 96.15955 22 6 95.73236 26 6 94.68899 30 6 92.44712 2 10 92.86038 6 10 95.46981 10 10 96.46286 14 10 96.85223 18 10 96.86577 22 10 96.53213 26 10 95.743 30 10 94.09322 2 14 93.87038 6 14 96.11381 10 14 96.93347 14 14 97.25022 18 14 97.26652 22 14 97.01496 26 14 96.4193 30 14 95.15536 2 18 94.5569 6 18 96.5459 10 18 97.24159 14 18 97.50795 18 18 97.53125 22 18 97.34569 26 18 96.8921 30 18 95.88966 2 22 95.04046 6 22 96.84953 10 22 97.45574 14 22 97.68629 18 22 97.71662 22 22 97.58115 26 22 97.23014 30 22 96.40911 2 26 95.37888 6 26 97.06244 10 26 97.60536 14 26 97.8107 18 26 97.84665 22 26 97.74729 26 26 97.4684 30 26 96.77238 2 30 95.60324 6 30 97.204 10 30 97.70479 14 30 97.89333 18 30 97.93315 22 30 97.85793 26 30 97.62669 30 30 97.01246 2 34 95.7307 6 34 97.28461 10 34 97.76141 14 34 97.94034 18 34 97.98232 22 34 97.92074 26 34 97.71636 30 34 97.1481 2 38 95.76994 6 38 97.30941 10 38 97.77878 14 38 97.95467 18 38 97.99719 22 38 97.93961 26 38 97.74325 30 38 97.18889 2 42 95.72337 6 42 97.27978 10 42 97.75784 14 42 97.93712 18 42 97.97857 22 42 97.91559 26 42 97.70895 30 42 97.13744 2 44 96.73378 6 44 98.33013 10 44 98.81877 14 44 99.00222 18 44 99.04252 22 44 98.97321 26 44 98.75434 30 44 98.15873 2 48 96.58768 6 48 98.2519 10 48 98.77502 14 48 98.97217 18 48 99.00892 22 48 98.91912 26 48 98.66045 30 48 97.99746 2 52 96.35784 6 52 98.13005 10 52 98.70792 14 52 98.92702 18 52 98.95849 22 52 98.83644 26 52 98.51451 30 52 97.74374 2 56 96.02618 6 56 97.95637 10 56 98.61411 14 56 98.86577 18 56 98.89049 22 56 98.72218 26 56 98.30765 30 56 97.3774 2 60 95.55963 6 60 97.7151 10 60 98.48656 14 60 98.78595 18 60 98.80365 22 60 98.57208 26 60 98.02592 30 60 96.86371 2 64 94.89262 6 64 97.37324 10 64 98.3092 14 64 98.68055 18 64 98.69456 22 64 98.37979 26 64 97.6476 30 64 96.14069 2 68 93.86909 6 68 96.85091 10 68 98.03995 14 68 98.52605 18 68 98.54827 22 68 98.13007 26 68 97.13629 30 68 95.0886 2 72 91.96251 6 72 95.89738 10 72 97.52758 14 72 98.21388 18 72 98.2749 22 72 97.7369 26 72 96.38661 30 72 93.43911
I've also attached a excel file. So for the first data.. the code should take in coordinates(2,2) & output data 87.7% at a point in excel grid consisting X and Y axis where X=2 & Y=2 & so on..

So can anyone give me a piece of VBA code to achieve this.. ?

Hi folks,

Consider a 2D array that has been filled with data within VBA, resulting in it being myArray(1 to ubound(myArray,1), 1 to 5). Note the number of columns is fixed.

How do you delete the first (for example) 100 rows of data from this array?
Seperate to this, how do you delete the last (for example) 100 rows of data from this array?

I note redim preserve only works with the last dimension (not 1st). I imagine the solution requires transposing and redim preserving & then copying to another array variable, but I'm lost with deleting the 1st 100 rows.

Many thanks in advance for reading and considering this,
Peter.

Good day!

I am currently trying to establish a method whereby I can find the value on a plot with different polynomials. (Sorry, not too good in explaining verbally)

and I have no idea how to start. Anyone can provide some guidance?

Let me give an example

untitled.jpg

Im trying to find a Profile Loss Parameter with a specified diffusion ratio and Mach No. (the polynomials each represent a different Mach No.)

The problem I have is that the Mach No. specified need not necessarily be 1.0, 0.7, or 0.3. It can be anything within, above or below the range. Now, I easily do this in MATLab with the interpolate function

But does anyone knows how to do this in Excel?

Any help is good help, guys!

Thanks

I have searched through Google but I haven't found any solutions. I have a 2D dynamic array. The values to be added into the array is contained in Sheet 1. The cells with values starts at C6 and the matrix on Sheet 1 has 2 rows and 2 columns for now, but it will have up to 1000-1500 rows and 1000-1500 columns later. So I'm searching for a fast and efficient way. I have tried to loop through rows and columns of sheet 1 using the code below but I keep getting a runtime error 1004 Select method of Range class. failed


	VB:
	
shiftCol = Sheets(1).Range("B3").Value * -1 
 'shift column = number of columns * -1
 'used to get selection to point to the next D6 and so on
 
Sheets(1).Range("C6").Select 
 
For i = 0 To transRow Step 1 
    For j = 0 To transCol Step 1 
        transMatrix(i, j) = Selection.Value 
        Selection.Offset(0, 1).Select 
    Next j 
     
    Selection.Offset(1, shiftCol).Select 
     
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Due to the high number of rows and columns to be used later, I would really like to use something that doesn't use Selection. Thank you.

Hello

First time post so please bare with me. I have two numbers one is at Dec.11, the other is an average for the 2012 year. I need to linear interpolate the 2012 number from the Dec.11 number across the 2012 year so that the 12 months average the 2012 number and none of the numbers are greater than it.

Dec.11 1071.69
Jan.12
Feb.12
Mar.12
Apr.12
May.12
Jun.12
Jul.12
Aug.12
Sep.12
Oct.12
Nov.12
Dec.12
Average for 2012 is 1,087.46

Any ideas on the excel formula?

Thanks

I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table. Appreciate any help anyone can give.

Hi,

I would imagine this has been covered elsewhere before and I just can't find it so please feel free to refer me to a previous thread if that's the case.

I have a table of rainfall intensity data which I want to draw data from (attached). I am familiar with VLOOKUP however I need to interpolate between two rows of my data, e.g. I have rainfall intensity data for a 10 year flood event and a 25 year flood event, but I want to know intensity for a 20 year event. I need to interpolate between both rows and columns at the same time. I have tried plotting the data and fitting a trendline then using the equation from that, but the trendlines don't fit my data very well - and I figure there must be something more elegant.

Thanks for the help.

Regards,
Shane


No luck finding an answer? You could always try Google.