I always find "manual interpolation" much easier than putting it in an excel formula. but it is doable. vlookup gets me stuck to easy, using index and match helps me to solve.
just to help understand the values a bit better I have put X and Y values in col x and y cols so you can read in the formula if x or y value is used in which part
so that resulted in
MATCH(AA3;$X$2:$X$6;1) finds the values closest but always less then the desired value.
MATCH(AA3;$X$2:$X$6;1)+1 simply finds the next row in the value that will be over the wanted x
the index parts in the formula helps to eighter use the X value or the Y value in the formula
if you translate each index to its value it will show..
X to find Y value for in my example is 2,25 (in cell AA3 for this example) this will result in the following outcome for each index formual above
y= 20 + ((2,25-2)*(30-20)/(3-2))
y = 20 + 0,25*10
y = 22,5
The formula could be simplified somewhat if the interval of x values is always 1 but because changes are this is not always the case this formula will work on any interval. To help understand the formula you can also use the evaluate formula option under formulas menu in the Formula Auditing section (3rd or 4th section on the ribbon)
Bookmarks