Difference between two numbers in decimal

[withirfan]

Dear Friends
Please help to solve this.
If the Value
1=5.498
2=15.527
3=28.481
4=43.783 & So on
then how to get the value of 1.1, 1.2, 1.3 till 1.9 & 2.1,2.2,2.3 and so on using v lookup ?
I want to enter the criteria in one cell and want the result in another cell

[MrShorty]

It looks like a linear interpolation problem, is that right? If so, here's one previous thread that discusses simple linear interpolation: https://www.excelforum.com/excel-for...me-series.html
Another: https://www.excelforum.com/excel-gen...wo-points.html

I would also note that other spreadsheets have built in linear interpolation functions. I know QuattroPro had one back in the day (@LINTERP()). Gnumeric also has one (INTERPOLATE()). If you are not constrained to using Excel, those might be options.

[joeu2004]

It would be better if you provided all of the known data. We don't know what "and so on" means.

-----

In Excel, the built-in functon to do linear interpolation is FORECAST.

For example, with 1 through 4 in A1:A4 and 5.498 through 43.783 in B1:B4, =FORECAST(1.1,$B$1:$B$4,$A$1:$A$4) yields the linear estimate associated with 1.1.

However, if we backtest that formula with formulas of the form =FORECAST(A1,$B$1:$B$4,$A$1:$A$4), substituting A2, A3 and A4, we find that the results are off by 2.9% to 24.5% compared to the actual data in B1:B4.

The reason is: your data is not related linearly exactly, and FORECAST uses a "best-fit" straight-line to approximate the data.

-----

I would start by putting your data into an XY Scatter chart (not a Line chart), and see if one of the standard trendlines fits the data better.

In fact, a so-called "power" trendline fits almost exactly. (RSQ is 0.999999737213761.)

The formula for the "power" trendline is y = b*x^m.

One way to calculate "m" and "b" is to select a pair of horizontal cells (e.g. E1:F1), and array-enter (press ctrl+shift+Enter instead of just Enter) the formula =LINEST(LOG(B1:B4),LOG(A1:A4)).

Then, the formula to estimate the value equivalent to 1.1 is =10^$F$1 * 1.1^$E$1, and the backtest formulas are of the form =10^$F$1 * A1^$E$1.

Since 10^$F$1 is constant, we might put =10^$F$1 into G1, and the backtest formula is =$G$1 * A1^$E$1.

With your data, the backtest results are within 0.02% to 0.04% of the actual data in B1:B4.

However, we do not know how well a "power" trendline fits the unknown data ("and so on").

-----

Alternatively, you might be interested in linerally interpolating between pairs of data.

To that end, we need to know the largest "criteria" value. That is, what is the last "and so on" after 1, 2, 3 and 4?

Suppose it is 4. Enter 1 through 4 into A1:A4 and the corresponding values 5.498 through 43.783 into B1:B4

Then, given any "criteria" value in H1 between 1 and 4 (more precisely, >= 1, but < 4), the linear interpolation can be calculated using:

=FORECAST(H1, INDEX($B$1:$B$4,MATCH(H1,$A$1:$A$4)):INDEX($B$1:$B$4,1+MATCH(H1,$A$1:$A$4)),
INDEX($A$1:$A$4,MATCH(H1,$A$1:$A$4)):INDEX($A$1:$A$4,1+MATCH(H1,$A$1:$A$4)))

Obviously, it would be prudent to enter =MATCH(H1,$A$1,$A$4) I1, and write INDEX($B$1:$B$4,I1):INDEX($B$1:$B$4,1+I1), for example.

If we backtest A1:A3, the formula returns exactly what is in B1:B3. But we cannot backtest A4 (4), if 4 is the largest "criteria" value.

Implementation note: It might be easier to write the ranges using OFFSET instead of INDEX:INDEX. I prefer the latter because OFFSET is a "volatile" function; that is, it cause the formula and any directly or indirectly dependent cells to be recalculated when Excel performs a recalculation cycle (e.g. editing any cell in the workbook).