Free Microsoft Excel 2013 Quick Reference

2D-Interpolation

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.

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.

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


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

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

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

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

Hi,
I want to calculate discount rates for different cash flows using a yield curve. This is how I try to do it:
I have a table with government bonds, their interest rates and time to maturity in days. The table is sorted by maturity times (increasing)I have another table with 1. Date of cash flow, 2. Time to maturity of the bond that has the closest time of maturity before the cash flow date, 3. This bond’s interest rate, 4. Same as (2) except the bond with time of maturity closest following cash flow date, 5. Interest rate of bond in (4) 6. An interpolation formula between the two bonds.My problem is with columns 2 and 4 in this last table. I want to automatically find those values from the first table. So I try to use the MATCH-function, but it has two issues:
The time to maturity in table one has to be sorted increasingly in (2) and decreasingly in (4) which obviously can’t be done at the same time;It gives an index value that I don’t know how to use. If I try the CHOOSE-function, I can’t provide the “value”-arguments as a vector since it seems to treat the vector as a single argument.I am using Excel for Mac 2011. Any help is appriciated.

Hello experts,

I have large data of around 10,000 drainage pipe segments and 10,000 junctions in ArcGIS layer

The problem is: due to some reason certain junctions could not be accessed for survey, thus 1,000+ entries are missing for pipes (up stream and d/s invert elevation) and junctions' surface elevation

Logically, interpolation should work here. (based on slope, u/s tag invert level or two junction u/s tag invert level or likewise)

Tried to do it manually in excel, but quite tiring and with less confidence (unprofessional too)

ASSETID LENGTH US_INVERT DS_INVERT US_TAG DS_TAG
*196939 76.51 0 40.42 7772 7771

*197048 34.77 42.98 0 7773 7772

196938 10.45 40.61 40.46 7783 7771

196939 76.51 0 40.42 7772 7771

196940 8.26 39.88 39.84 7735 7734

Drainage pipes data

Above, I have put * to just two rows which are involved in calculations out of the whole data from 10,062 rows (Row 3 to Row 10,064)

1. To calculate US_INVER (US_TAG) elevation we need to find an asset where 7772 is under column heading DS_TAG
2. That is ASSETID 197048
3. After finding it (through some function/formula), we have got on US_INV and one DS_INV
4. Now we want to know elevation of a point in between two known elevations: which are +42.98 and +40.42 and total distance b/w these two points is length of Asset ID 196939(76.51m) and 197048(34.77m)=111.28m
5. The point is located 34.77m downstream of +42.98 and 76.51m upstream from of +40.42
6. We have just created a right angle triangle where Perpendicular is 42.98-40.42=2.56m and Base is 34.77+76.51=111.28
7. To calculate the angle of drainage pipe tan Ѳ = 2.56/111.28, Ѳ=1.317
8. After knowing the angle of pipe we can easily know the elevation of any point along the hypotenuse
9. So, US_INVERT (US_TAG) = 42.98 - tan (1.317) * 34.77 = +42.18

Alternatively
1. After step 5. we have another choice; by dividing value of perpendicular with base and multiply with distance from upstream and subtracting from upstream elevation i.e. 42.98 - (2.56/111.28)*34.77 = +42.18 again

This is the value we require at US_INVER (US_TAG) of Asset ID 196939

And

DS_INVERT (DS_TAG) of ASSETID 197048

Also, something similar would be require to fill in the missing data under column DS_INVERT

There are around 1000 missing values, if we know the formula we Ctrl+H (find and replace “0”) to save time and effort

Also, sometime one asset upstream we wont get any value (“0”again), in that case we can go two steps up (but, if it is not possible we can leave it for manual calculation)

The main challenge here, in my understanding is finding a function which could find US_TAG value of row (in question) in a DS_TAG column and start calculating by taking values from that row and row in question.

Note: the DS_TAG value could be found in multiple rows, in that case priority should be given to a row with least value in LENGTH column. (again if it is difficult, any of row could be taken)

It seems we are pushing MSEXCEL to its limits from where C++ programming starts,

The complete file in excel format is uploaded at

http://www.mediafire.com/?uniy9cic1gvxytp

http://www.mediafire.com/?uniy9cic1gvxytp

Thanks for your time reading and efforts to solve it

Regards,

Kashif

Hello everyone, I want to use a formula to do some very simple linear interpolations (which I will later enter into a macro to auto search for and interpolate blank cells). Thank you in advance!

The formula should be able to take these numbers in a column:
1
2
(empty cell)
(empty cell)
(empty cell)
6
7And fill in the blanks to create this series in the same column:
1
2
3
4
5
6
7The formula should be able to do this if there is one empty row, or more.

Is there a excel spreadsheet function that can be used to interpolate between two columns of data? If not, can you recommend a quick equation to perform interpolation?
I tried with "IF(AND" conditions, but we can't use it for more than 7 set of values...
so please recommend a quick equation to perform interpolation?

I have moderate experience with excel, and am trying to find a quick way to create a surface plot. Imagine a rectangular duct with ports at 1', 2', 3', 4'. I have known data at 1' and 4'. I do have depth dimensions, but these are not used in the interpolation. I am trying to use TREND(), and I am able to get good interpolated data only by moving column 4' right next to column 1. Excel seems to not like it if I try to grab known x and y values from cells that are not right next to each other. Is there a better way to do this, so I don't have to bother copying/pasting values and moving around cells so I can chart a surface plot? Any assistance would be greatly appreciated.

I have several worksheets of data that are updated/added to daily. Sometimes I have missing values and I end up filling these in with linear interpolation.

I would like to have a VBA macro that would take the upper and lower bounds of say a columnar block that is selected and fill in the blank cells with interpolated values.

Seems like it would be simple. I've been looking for code to modify, but nothing seems close. My experience with Excel/VBA is limited and this will be a learning experience.

Any help would appreciated. BTW, I already searched this forum for "interpolation" help. Seems like someone should have done this.

Regards,
Dave Strang

Hi buddies,

Here I come with new querys. I would like to linear interpolate an yo value from an (x,y) columns data, when knowing a specific xo value. I see it is possible to do this with the IF function to choose the interval where xo is included but it is tired and not very elegant when the (x,y) columns are very long(see file attached).

Thanks and regards,

Ivanucho