Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linear Interpolation in excel
Hello again,
I have written the following function as I cannot find a function in excel that works properly. This linearly interpolates a value in the 2nd range (y1), by finding the the position of the two values in the first range (y0) that surround (above and below) the "x" value which can be a double. The problem is is that this function really slows down the spreadsheet (it does work), but I was wonder if anyone could point out places where I could possibly speed it up. Function LinTerp(y0 As Range, y1 As Range, x As Double) 'this function will return the linear interpulated value corresponding to the x value found in the y0 range (y0 range has to be in ascending order) 'the range y0 has cannot be sinusoidal or parabolic as it will find the first instance in the range 'that surrounds the value being searched for. Dim VL As Double Dim y0Num, y1Num, vlpos As Integer Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double y0Num = Application.Count(y0) y1Num = Application.Count(y1) If y0Num < y1Num Then 'if the two ranges are not of the same length, this function should stop as it will calculate the wrong number LinTerp = "Ranges Inconsistant" Else VL = Application.VLookup(x, y0, 1, True) 'binary search to find the position of VL (Vlpos) in y0 range Dim foundFlag As Boolean Dim first, middle, last As Integer foundFlag = False first = 1 last = y0Num Do While (first <= last) And (Not foundFlag) vlpos = Int((first + last) / 2) Select Case y0(vlpos, 1) Case VL foundFlag = True Case Is VL last = vlpos - 1 Case Is < VL first = vlpos + 1 End Select Loop 'end of binary search y0Below = y0(vlpos, 1) y0Above = y0(vlpos + 1, 1) y1Below = y1(vlpos, 1) y1Above = y1(vlpos + 1, 1) A = y0Above - y0Below B = x - y0Below C = B / A LinTerp = y1Below + (y1Above - y1Below) * C End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linear Interpolation in excel
Assuming your data is linear,
Use the Forecast function. More powerful is the LINEST also slope, intercept and trend functions. If you actually want to interpolate just the values before and after, then you can contruct a more complex formula Table in A1:B10, Value to interpolate in C1. Data sorted as your function requires. =TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1), OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1) -- Regards, Tom Ogilvy "JohnJack" wrote: Hello again, I have written the following function as I cannot find a function in excel that works properly. This linearly interpolates a value in the 2nd range (y1), by finding the the position of the two values in the first range (y0) that surround (above and below) the "x" value which can be a double. The problem is is that this function really slows down the spreadsheet (it does work), but I was wonder if anyone could point out places where I could possibly speed it up. Function LinTerp(y0 As Range, y1 As Range, x As Double) 'this function will return the linear interpulated value corresponding to the x value found in the y0 range (y0 range has to be in ascending order) 'the range y0 has cannot be sinusoidal or parabolic as it will find the first instance in the range 'that surrounds the value being searched for. Dim VL As Double Dim y0Num, y1Num, vlpos As Integer Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double y0Num = Application.Count(y0) y1Num = Application.Count(y1) If y0Num < y1Num Then 'if the two ranges are not of the same length, this function should stop as it will calculate the wrong number LinTerp = "Ranges Inconsistant" Else VL = Application.VLookup(x, y0, 1, True) 'binary search to find the position of VL (Vlpos) in y0 range Dim foundFlag As Boolean Dim first, middle, last As Integer foundFlag = False first = 1 last = y0Num Do While (first <= last) And (Not foundFlag) vlpos = Int((first + last) / 2) Select Case y0(vlpos, 1) Case VL foundFlag = True Case Is VL last = vlpos - 1 Case Is < VL first = vlpos + 1 End Select Loop 'end of binary search y0Below = y0(vlpos, 1) y0Above = y0(vlpos + 1, 1) y1Below = y1(vlpos, 1) y1Above = y1(vlpos + 1, 1) A = y0Above - y0Below B = x - y0Below C = B / A LinTerp = y1Below + (y1Above - y1Below) * C End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linear Interpolation in excel
Again Tom, you're a god. Thanks so much.
Jack On Feb 14, 3:49 pm, Tom Ogilvy wrote: Assuming your data is linear, Use the Forecast function. More powerful is the LINEST also slope, intercept and trend functions. If you actually want to interpolate just the values before and after, then you can contruct a more complex formula Table in A1:B10, Value to interpolate in C1. Data sorted as your function requires. =TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1), OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1) -- Regards, Tom Ogilvy "JohnJack" wrote: Hello again, I have written the following function as I cannot find a function in excel that works properly. This linearly interpolates a value in the 2nd range (y1), by finding the the position of the two values in the first range (y0) that surround (above and below) the "x" value which can be a double. The problem is is that this function really slows down the spreadsheet (it does work), but I was wonder if anyone could point out places where I could possibly speed it up. Function LinTerp(y0 As Range, y1 As Range, x As Double) 'this function will return the linear interpulated value corresponding to the x value found in the y0 range (y0 range has to be in ascending order) 'the range y0 has cannot be sinusoidal or parabolic as it will find the first instance in the range 'that surrounds the value being searched for. Dim VL As Double Dim y0Num, y1Num, vlpos As Integer Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double y0Num = Application.Count(y0) y1Num = Application.Count(y1) If y0Num < y1Num Then 'if the two ranges are not of the same length, this function should stop as it will calculate the wrong number LinTerp = "Ranges Inconsistant" Else VL = Application.VLookup(x, y0, 1, True) 'binary search to find the position of VL (Vlpos) in y0 range Dim foundFlag As Boolean Dim first, middle, last As Integer foundFlag = False first = 1 last = y0Num Do While (first <= last) And (Not foundFlag) vlpos = Int((first + last) / 2) Select Case y0(vlpos, 1) Case VL foundFlag = True Case Is VL last = vlpos - 1 Case Is < VL first = vlpos + 1 End Select Loop 'end of binary search y0Below = y0(vlpos, 1) y0Above = y0(vlpos + 1, 1) y1Below = y1(vlpos, 1) y1Above = y1(vlpos + 1, 1) A = y0Above - y0Below B = x - y0Below C = B / A LinTerp = y1Below + (y1Above - y1Below) * C End If End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linear Interpolation in excel
On Feb 14, 3:49 pm, Tom Ogilvy
wrote: Assuming your data is linear, Use the Forecast function. More powerful is the LINEST also slope, intercept and trend functions. If you actually want to interpolate just the values before and after, then you can contruct a more complex formula Table in A1:B10, Value to interpolate in C1. Data sorted as your function requires. =TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1), OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1) -- Regards, Tom Ogilvy "JohnJack" wrote: Hello again, I have written the following function as I cannot find a function in excel that works properly. This linearly interpolates a value in the 2nd range (y1), by finding the the position of the two values in the first range (y0) that surround (above and below) the "x" value which can be a double. The problem is is that this function really slows down the spreadsheet (it does work), but I was wonder if anyone could point out places where I could possibly speed it up. Function LinTerp(y0 As Range, y1 As Range, x As Double) 'this function will return the linear interpulated value corresponding to the x value found in the y0 range (y0 range has to be in ascending order) 'the range y0 has cannot be sinusoidal or parabolic as it will find the first instance in the range 'that surrounds the value being searched for. Dim VL As Double Dim y0Num, y1Num, vlpos As Integer Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double y0Num = Application.Count(y0) y1Num = Application.Count(y1) If y0Num < y1Num Then 'if the two ranges are not of the same length, this function should stop as it will calculate the wrong number LinTerp = "Ranges Inconsistant" Else VL = Application.VLookup(x, y0, 1, True) 'binary search to find the position of VL (Vlpos) in y0 range Dim foundFlag As Boolean Dim first, middle, last As Integer foundFlag = False first = 1 last = y0Num Do While (first <= last) And (Not foundFlag) vlpos = Int((first + last) / 2) Select Case y0(vlpos, 1) Case VL foundFlag = True Case Is VL last = vlpos - 1 Case Is < VL first = vlpos + 1 End Select Loop 'end of binary search y0Below = y0(vlpos, 1) y0Above = y0(vlpos + 1, 1) y1Below = y1(vlpos, 1) y1Above = y1(vlpos + 1, 1) A = y0Above - y0Below B = x - y0Below C = B / A LinTerp = y1Below + (y1Above - y1Below) * C End If End Function Very cool interpolation function Tom! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linear Interpolation in excel
Credit goes to Harlan Grove.
(although I am sure I have written something similar in the past <g) -- Regards, Tom Ogilvy wrote in message oups.com... On Feb 14, 3:49 pm, Tom Ogilvy wrote: Assuming your data is linear, Use the Forecast function. More powerful is the LINEST also slope, intercept and trend functions. If you actually want to interpolate just the values before and after, then you can contruct a more complex formula Table in A1:B10, Value to interpolate in C1. Data sorted as your function requires. =TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1), OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1) -- Regards, Tom Ogilvy "JohnJack" wrote: Hello again, I have written the following function as I cannot find a function in excel that works properly. This linearly interpolates a value in the 2nd range (y1), by finding the the position of the two values in the first range (y0) that surround (above and below) the "x" value which can be a double. The problem is is that this function really slows down the spreadsheet (it does work), but I was wonder if anyone could point out places where I could possibly speed it up. Function LinTerp(y0 As Range, y1 As Range, x As Double) 'this function will return the linear interpulated value corresponding to the x value found in the y0 range (y0 range has to be in ascending order) 'the range y0 has cannot be sinusoidal or parabolic as it will find the first instance in the range 'that surrounds the value being searched for. Dim VL As Double Dim y0Num, y1Num, vlpos As Integer Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double y0Num = Application.Count(y0) y1Num = Application.Count(y1) If y0Num < y1Num Then 'if the two ranges are not of the same length, this function should stop as it will calculate the wrong number LinTerp = "Ranges Inconsistant" Else VL = Application.VLookup(x, y0, 1, True) 'binary search to find the position of VL (Vlpos) in y0 range Dim foundFlag As Boolean Dim first, middle, last As Integer foundFlag = False first = 1 last = y0Num Do While (first <= last) And (Not foundFlag) vlpos = Int((first + last) / 2) Select Case y0(vlpos, 1) Case VL foundFlag = True Case Is VL last = vlpos - 1 Case Is < VL first = vlpos + 1 End Select Loop 'end of binary search y0Below = y0(vlpos, 1) y0Above = y0(vlpos + 1, 1) y1Below = y1(vlpos, 1) y1Above = y1(vlpos + 1, 1) A = y0Above - y0Below B = x - y0Below C = B / A LinTerp = y1Below + (y1Above - y1Below) * C End If End Function Very cool interpolation function Tom! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to do a Linear interpolation in excel? | Excel Worksheet Functions | |||
Does Excel have a linear interpolation function? | Excel Discussion (Misc queries) | |||
linear interpolation function in excel | Excel Worksheet Functions | |||
Linear Interpolation | Excel Programming | |||
linear interpolation | Excel Discussion (Misc queries) |