FUNCTION FOR LINEAR INTERPOLATION 5

[nitin36537]

IS THERE ANY FUNCTION FOR LINEAR INTERPOLATON IN EXCEL?

I HAVE A TABLE FOR WIND PRESSURE ON TOWER STUCTURE IN EXCEL LIKE THIS

A B C
1 200 400 <----- SPAN
2 20 10 20
3 30 40 60

RAW 1 INDICATE SPAN
COLUMN A INDICATE HEIGHT OF STRUCTURE
RANGE A2:C3 INDICATE WIND PRESSURE

I HAVE TO FIND OUT WIND PRESSURE ON TOWER WITH SPAN OF 300 METER AND HEIGHT OF 25 M

FUNCTION SHOULD RETURN WIND PRESSURE = 32.5

CAN ANY BODY HELP?

I HAVE SOLVED THIS BY DEVELOPING CUSTOM FUNCTION.
IS THERE ANY BUILT IN EXCEL FUNCTION FOR INTERPOLATION?

IF ANY BODY LIKE TO SEE MY CUSTOME FUNCTION PLEASE ASK.


NITIN PATEL

INDIA

 

[Ari]

Hi,

I haven't found any built-in function for interpolation, but its formula is very simple: think of similar triangles (with the same tangent).


Ari Constancio, Chem. Eng.
ari.constancio@netc.pt

http://www.terravista.pt/ancora/1588

 

[nitin36537]

THANK YOU ARI

BUT MY REAL PROBLEM IS THAT I HAVE A RANGE OF 70 ROWS BY 10 COLUMN FROM WHICH I HAVE TO DO INTERPOLATION

I AM REPRODUCING HERE THE FUNCTION DEVELOPED BY ME.

ITS USE IS VERY SIMPEL

IF WE LIKE TO FIND WIND PR FOR TOWER WITH HEIGHT 25 M & FOR SPAN OF 300M THEN WE HAVE TO WRITE

=INTERPOL(25,300,A1:C3)

THIS WILL RETURN INTERPOLETED VALUE

HOPE THIS WILL BE USEFUL IN MANY APPLICATION

NITIN PATEL



''''==============================
'PLEASE REMOVE OPTION EXPLICIT STATMENT FROM THE TOP
'

'Option Explicit

Private Function RNO1(RVALUE, RR)

If RR.Cells(1, 1) > RVALUE Then
RNO1 = 1
Exit Function
End If


TOTALNOOFROW = RR.Rows.Count

If RR.Cells(TOTALNOOFROW, 1) < RVALUE Then
RNO1 = TOTALNOOFROW
Exit Function
End If

RNO1 = 1
For Each CELL In RR


If CELL.Value = RVALUE Then
RNO1 = RNO1
Exit For
End If

If CELL.Value > RVALUE Then
RNO1 = RNO1 - 1
Exit For
End If

RNO1 = RNO1 + 1

Next

End Function

Private Function RNO2(RVALUE, RR)

If RR.Cells(1, 1) > RVALUE Then
RNO2 = 1
Exit Function
End If


TOTALNOOFROW = RR.Rows.Count

If RR.Cells(TOTALNOOFROW, 1) < RVALUE Then
RNO2 = TOTALNOOFROW
Exit Function
End If

RNO2 = 1
For Each CELL In RR


If CELL.Value = RVALUE Then
RNO2 = RNO2
Exit For
End If

If CELL.Value > RVALUE Then
RNO2 = RNO2
Exit For
End If

RNO2 = RNO2 + 1

Next

End Function


Private Function CNO1(CVALUE, CR)

If CR.Cells(1, 1) > CVALUE Then
CNO1 = 1
Exit Function
End If


TOTALNOOFCOL = CR.Columns.Count

If CR.Cells(1, TOTALNOOFCOL) < CVALUE Then
CNO1 = TOTALNOOFCOL
Exit Function
End If

CNO1 = 1
For Each CELL In CR


If CELL.Value = CVALUE Then
CNO1 = CNO1
Exit For
End If

If CELL.Value > CVALUE Then
CNO1 = CNO1 - 1
Exit For
End If

CNO1 = CNO1 + 1

Next

End Function

Private Function CNO2(CVALUE, CR)

If CR.Cells(1, 1) > CVALUE Then
CNO2 = 1
Exit Function
End If


TOTALNOOFCOL = CR.Columns.Count

If CR.Cells(1, TOTALNOOFCOL) < CVALUE Then
CNO2 = TOTALNOOFCOL
Exit Function
End If

CNO2 = 1
For Each CELL In CR


If CELL.Value = CVALUE Then
CNO2 = CNO2
Exit For
End If

If CELL.Value > CVALUE Then
CNO2 = CNO2
Exit For
End If

CNO2 = CNO2 + 1

Next

End Function


Function INTERPOL(RV, CV, datarange As Range)

noofrow = datarange.Rows.Count
noofcol = datarange.Columns.Count

Set fc = datarange.Cells(2, 1)
Set lc = datarange.Cells(noofrow, 1)
Set RR = Range(fc, lc)

Set fc = datarange.Cells(1, 2)
Set lc = datarange.Cells(1, noofcol)
Set CR = Range(fc, lc)


Set fc = datarange.Cells(2, 2)
Set lc = datarange.Cells(noofrow, noofcol)
Set VR = Range(fc, lc)

r1 = RNO1(RV, RR)
R2 = RNO2(RV, RR)

c1 = CNO1(CV, CR)
C2 = CNO2(CV, CR)

If c1 = C2 Then
MC1 = 0
Else
MC1 = VR.Cells(r1, C2) - VR.Cells(r1, c1)
MC1 = MC1 / (CR.Cells(1, C2) - CR.Cells(1, c1))

MC2 = VR.Cells(R2, C2) - VR.Cells(R2, c1)
MC2 = MC2 / (CR.Cells(1, C2) - CR.Cells(1, c1))

End If


V1 = VR.Cells(r1, c1) + MC1 * (CV - CR.Cells(1, c1))
V2 = VR.Cells(R2, c1) + MC2 * (CV - CR.Cells(1, c1))

If r1 = R2 Then
MR = 0
Else
MR = (V2 - V1) / (RR.Cells(R2, 1) - RR.Cells(r1, 1))
End If

INTERPOL = V1 + MR * (RV - RR.Cells(r1, 1))

End Function

 

[dwilson]

I have come up with possibly a simpler solution to your problem. My problem is the solution uses slopes and intercept information and would be difficult to explain. If you are interested in seeing the excel sheet (no macros, therefore no virus risk) I can send it to you.

A B C
1 200 400
2 20 10 20
3 30 40 60

Slope and Intercept of your data
200 (cell C7) 400 (cell D7)
Slope =SLOPE(C3:C4,B3:B4) =SLOPE(D34,B3:B4)
Intcpt. =INTERCEPT(C3:C4,B3:B4) =INTERCEPT(D34,B3:B4)

INPUTS
Span 400(cell A12)
Height 30 (cell B12)

ADJUST INITIAL SLOPE CALCULATION
Calc. Slope =((A12-C2)/(D2-C2)+C7)
Calc. Int. =C8-((C8-D8)*(A12-C2)/(D2-C2))
Cal. Pres. =B12*C12+D12

Sorry if over complicated here but the sheet is rather simple.

P.S. I have requested the ability to attach files on this site.


Regards,
Dave Wilson

 

[IJR]

I keep convincing myself that Excel does regression, though the function is not in the function list. If you type &quot;best-fit&quot; in Excel help search, you will notice all these things like linear, logarithmic, power etc interpolation. I remember long time ago trying to play with a chart series and a line popped up that connected all my data with the best fit and I could describe the line!

I have never tried them, but it may be worth the search

 

[svanels]

Check out the help on:

LINEST
GROWTH
LOGEST
TREND

GROWTH
Calculates predicted exponential growth by using existing data.
LINEST
Calculates the statistics for a line by using the &quot;least squares&quot; method to calculate a straight line that best fits your data, and returns an array that describes the line.
LOGEST
In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve.
TREND
Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

Check out also the statistical functions in Excell Steven van Els
SAvanEls@cq-link.sr

 

[nitin36537]

Hi Dwilson

yes i would like to see worksheet developed by you.
My e-mail add is nitin_patel@icenet.net

I have developed excel file explaining the function for interpolation. it can handel any no of row & column data & we dont have to calculate intermediate values to find slops & other things

If you like to see the same then let me know.


Thanking you

 

[nitin36537]

Hi Svanels

Thank you for help

My problem is that function like TREND , GROTH does not return value as desire by me.


I have developed excel file explaining the function for interpolation. it can handel any no of row & column data & we dont have to calculate intermediate values to find slops & other things

If you like to see the same then let me know.

 

Hi Nitin

I have had the same problem, but had to fit a complicated exponential curve to test data. I don't like custom VB programming, since it takes a long time to develop. I ended up using the Excell solver to find the best fit, this is how you do it:

Let's say you want to fit a straight line y=mx+c through any amount of data points.

First define the variables m and c somewhere,

Then copy the y=mx+c equation to calculate the y values for each of your measured x values. This gives you two columns of y values, one as you measured, and one for the approximating straight line.

Now add another column defining the error between the y values of what you measured, and that given by the y=mx+c equation. Use (y(actual)-y(linear))^2.

As a last step, simply add all the values in the error column. The logic now is to minimise this total error by varying the values of m and c in the y=mx+c equation.

Simply go to Tools/Solver and set up the minimisation problem. If you don't see the solver, go to add-ins and add the solver. And Bob's your Uncle! it finds the best least squares fit of a straight line throught your data.

I found this method to be extremely powerfull in the sense that you can fit any imaginable equation with several variables to any amount of given data. Very reliably and easy.

It looks complicated, but is very easy to implement.

Hope it helps,

Teo Buhrmann

 

[MikeVV]

I have a spreadsheet that performs interpolation using the formula:

delta_y / delta_x = delta_y0 / delta_x0 and solving for y

It also uses MATCH() and INDEX() functions with data tables to identify the proper rows of data to interpolate.

Reply to MJVanVoorhis@CS.com for a copy - this spreadsheet is invaluable for a variety of analysis tasks because table lookup and interpolation is very common and I like my spreadsheets to be automatic.

MikeVV
MJVanVoorhis@CS.com

 

[Mala]

Dear Nitin
I've developed a worksheet which does exactly what you want.
Ask me for it: mala_rs_singh@rediffmail.com
Mala

 

[Mala]

Hi everybody,
Here's a single formula solution

Suppose our data is set up as below:

A B C D E F
1 200 400 600 800 1000 Span
2 20 10 20 160 210 260
3 30 40 60 190 240 290
4 40 130 180 230 280 330
5 50 180 230 280 330 380
6 60 240 290 340 390 440
7 70 310 360 410 460 510
8 80 390 440 490 540 590
9 90 750 800 850 900 950
Height

Name ranges as under:
RoHd =Sheet1!$A$2:$A$9 - Range containing spans
ColHd =Sheet1!$B$1:$F$1 - Range containing heights
Dat =Sheet1!$B$2:$F$9 - Range containing wind-pressures

Ht =Sheet1!$J$1 - The heightfor which interpolation is reqd.
Sp =Sheet1!$J$2 - The span for which interpolation is reqd.

In the cell where you want the interpolated value stored enter the following jumbo-sized formula:

=((INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd))))+((INDEX(Dat,((MATCH(Ht,RoHd))+1),(MATCH(Sp,ColHd))))-(INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd)))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd)))))+(((INDEX(Dat,(MATCH(Ht,RoHd)),((MATCH(Sp,ColHd))+1)))+((INDEX(Dat,((MATCH(Ht,RoHd))+1),((MATCH(Sp,ColHd))+1)))-(INDEX(Dat,(MATCH(Ht,RoHd)),((MATCH(Sp,ColHd))+1))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd)))))-((INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd))))+((INDEX(Dat,((MATCH(Ht,RoHd))+1),(MATCH(Sp,ColHd))))-(INDEX(Dat,(MATCH(Ht,RoHd)),(MATCH(Sp,ColHd)))))*(Ht-INDEX(RoHd,(MATCH(Ht,RoHd))))/(INDEX(RoHd,((MATCH(Ht,RoHd))+1))-INDEX(RoHd,(MATCH(Ht,RoHd))))))*(Sp-INDEX(ColHd,(MATCH(Sp,ColHd))))/(INDEX(ColHd,((MATCH(Sp,ColHd))+1))-INDEX(ColHd,(MATCH(Sp,ColHd))))

One will almost surely make an error entering this - so simply name the ranges as suggested, copy the formula from this message and paste it in your worksheet.
This formula has been checked with nitin's sample data and yields 32.5 as the result.

For Nitin:
Sorry nitin, the xls I sent you contained a small error which gave an incorrect result - I've sent you the correct xls again (inclusive of the above formula). Sorry once again.

 

[IJR]

Yeah Mala you have done it again. Do you ever sleep?

Respects
IjR

 

[Mala]

IJR, I must say you are very observant - this is becoming an obsession - it's fast sending all the Civil Engg. I learnt into obscurity.
I know of someone who's even worse off - you might check out on the activities of a gentleperson called JVFriederick on the Microsoft:Office forum of

http://www.Tek-tips.com

Regards
Mala

 

[IJR]

Nitin

Please allow me to thank Mala for introducing me to a person. By the way Mala, I am a regular visitor at tek-tips. However I must admit guys there are just too fast for me to even try to follow behind.

Nice week pals

IJr

 

For linear,parabolic and cubic interpolation search for fcernst on any good search engine such as google. He charges nowadays but reasonably.

 

[nitin36537]

Hi Everybody

Mala had done great Job
I had never seen this much long formula.
Mala Thank You.

Nitin Patel
India

 

[IJR]

Yeah nitin

Keep posting questions here: One question posted activates one hundred brains and another one hundred benefits. To some of us we get responses, the rest get challenges, yet there are others who get both.

Thanx all pals
IJR

 

[nitin36537]

Hi IJR

I have posted one more problem (display formula......)
Hope you will help

Thank You

Nitin

 

[acidrain]

Another solution is to import your spreadsheet into Quattro Pro spreadsheet software and use it's built-in LINTERP function. If you don't have access to this software, it may be worthwhile to buy it from Corel. The cost will probably be less than the cost of your time to implement some interpolation method in Excel.