Interpolation in an Excel table or range by means of Sub procedures (Macros) and functions.
They make a long formula using basic Excel functions. Download Spanish
It is not compatible with "Interpolation.xla" Add-in unless the name is changed to: "Personal.xls". Then, they will work fine.
These Macros do not Extrapolate and except with MacroB they give error if the argument equals the higher value of the first column.
Using macros:
Having a table like the one showed in the next picture, it is necessary to interpolate in any column of the table entering a value in the first column:
Kw for 3,47 as argument is needed.
Initially, you have to enable Developer tab because it is normally hidden by default in Excel. Go to File | Options | "Customize Ribbon" and check the Main Tab Developer.
Clicking on Macros a frame appears:
You have to select one of the two macros (Interp.xls has been renamed to Personal.xls). These two macros are different but they give the same result.
Using RefEdit to get range it gives absolute reference which is good for "Rango de tabla" (table range) but not so good for "Valor argumento" (Argument value). For "table range" would be better using a name. Do not include the first row of the table in the table range. The looked value is in the third column, that is why in "Número columna" 3 has been typed.
Resulting formula:
Using the second macro:
Using the third macro:
It would be crazy to try typing the formula directly in formula bar. The formula generated by this Macro will work fine with no necessity of Add-in and the resulting value is going to be refreshed automatically in case of changing the argument.
The only problem is the use of this macro in a non-Spanish version of Excel. Then, if you want to use it, you have to translate Excel basic formulas and change the Excel separator. In the Spanish version Excel uses ; but in the English version I think that Excel uses ,
In Excel, pressing Alt+F11, VBA Excel is opened. Code of forms can be showed and code can be translated.
Excel function translation from Spanish:
SI => IF
DESREF => OFFSET
BUSCARV => VLOOKUP
INDICE => INDEX
COINCIDIR => MATCH
FILAS => ROWS
TENDENCIA => TREND
PRONOSTICO => FORECAST
Another issue could be the use of the following sentence into the code:
ActiveCell.FormulaLocal = tf4
In case of error (in the English version probably) change it to:
ActiveCell.Formula = tf4
It could be necessary to save
Interp.xls with another extension like xlsm (enabled for Macros)
Set up
If you do not have a previous Personal.xls file, move Interpe.xls to this folder: C:\Program Files[(x86)]\Microsoft Office\OFFICE1n\XLSTARTUP and rename it to Personal.xls. If you have a previous Personal.xls you can export Interp.xls (forms and code) and import this stuff in your Personal.xls.