this time show the implementation of a function to interpolate linearly within a data set, as in a previous post but Through a role in this link can review the post reference. You'll also see how to use FUNCUSTOMIZE.ZIP to be more elegant the function created.
1. The interlining
The function created is named INTERLINE , see the Excel VBA code that allows the interpolation. Then explain what each line of code.The input of this function are: x, RGX, rgy. X is selected in the cell where the value to be interpolated, RGX is the range of values \u200b\u200bof X and Y values \u200b\u200bRGY
used three variables i, iX, iY, the first serves as a counter to the Do loop. The second and third are the ranges (2 pairs of data) between those who make the interpolation.
The function is designed as follows: if the value to search is less than the first value or greater than the latter (assuming that the data is sorted from lowest to highest) is interpolated or extrapolated using rather using the two first or last two pairs respectively. Otherwise look for pairs among those interpolated with the Do While loop in line 14. Using the Select setencia determines which pairs with interpolation. After
pairs obtained among those interpolated to calculate the estimated value for x in the FORECAST function (Forecast in English, line 20), is accessed using Application.WorksheetFunction before the show. Finally frees memory. Download file here.
2.User FUNCUSTOMIZE.DLL
In the post Working with a dll I mentioned about the characteristics of FUNCUSTOMIZE, this dll library allows us to emulate the help you have the Excel functions, ie to write function appear in the list of functions and also in helping it to press fx in the formula bar. View image.
Here I leave a file to learn how to implement a custom function to download it FUNCUSTOMIZE.DLL.
Luck.
0 comments:
Post a Comment