FUNCTION FOR LINEAR INTERPOLATION
FUNCTION FOR LINEAR INTERPOLATION
(OP)
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
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





RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
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(D3:D4,B3:B4)
Intcpt. =INTERCEPT(C3:C4,B3:B4) =INTERCEPT(D3:D4,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
RE: FUNCTION FOR LINEAR INTERPOLATION
I have never tried them, but it may be worth the search
RE: FUNCTION FOR LINEAR INTERPOLATION
LINEST
GROWTH
LOGEST
TREND
GROWTH
Calculates predicted exponential growth by using existing data.
LINEST
Calculates the statistics for a line by using the "least squares" 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
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
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.
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
I've developed a worksheet which does exactly what you want.
Ask me for it: mala_rs_singh@rediffmail.com
Mala
RE: FUNCTION FOR LINEAR INTERPOLATION
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.
RE: FUNCTION FOR LINEAR INTERPOLATION
Respects
IjR
RE: FUNCTION FOR LINEAR INTERPOLATION
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 www.Tek-tips.com
Regards
Mala
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
RE: FUNCTION FOR LINEAR INTERPOLATION
Mala had done great Job
I had never seen this much long formula.
Mala Thank You.
Nitin Patel
India
RE: FUNCTION FOR LINEAR INTERPOLATION
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
RE: FUNCTION FOR LINEAR INTERPOLATION
I have posted one more problem (display formula......)
Hope you will help
Thank You
Nitin
RE: FUNCTION FOR LINEAR INTERPOLATION
RE: FUNCTION FOR LINEAR INTERPOLATION
If you want to export, use some real computing power from MathCad or MathLab
Regards
Steven van Els
SAvanEls@cq-link.sr
RE: FUNCTION FOR LINEAR INTERPOLATION
great work, your formula just saved me. thanks to nitin also for posting good question.
great work guys.
regrads,
imtiyaz