Getting value on Y axis by putting X axis value

[kashifjillani]

Hello to all the experts of Excel,

I still can not figure out the way to get the values in a chart/graph by putting an specific X axis value.

e.g.

There is relation between Gauge height and Flow

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>Gauge</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Flow </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.305</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.10022</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.341388</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.27539</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.381347</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.85528</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.401708</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1.32224</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.413752</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1.69248</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.433354</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2.46828</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.452996</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3.43106</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.482379</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5.3022</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.511557</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>7.77429</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.522962</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>8.9451</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.561209</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>13.53592</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.589</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>17.68</TD></TR></TBODY></TABLE>


If I draw the curve with Flow on X-axis and Gage on Y-axis and then want to know the flow at Gage 0.53m.

OR

By putting flow value to know Gage height.

Regards,

 

[Steve=True]

Is this the type of chart you are looking for?

http://www.4shared.com/document/iwjNSNhW/FlowandGuageRateTest.html

Although Excel will graph the line for you, it may not be detailed enough to show you an individual point on the curve.

If that is what you are looking for, then you may want to see how you can build the equation into Excel and solve for X.

Steve=True
www.exceldashboardtemplates.com

 

[Jon Peltier]

I arranged your data like this:

Note the horizontal axis variable is in the first column.

Cell B2 is the value we want to match in the second column.
Cell B3 contains this formula:
=MATCH(B2,B6:B18)
Cell A2 contains this interpolation formula:
=(B2-INDEX(B6:B18,B3))/(INDEX(B6:B18,B3+1)-INDEX(B6:B18,B3))*(INDEX(A6:A18,B3+1)-INDEX(A6:A18,B3))+INDEX(A6:A18,B3)

The following chart shows Flow vs. Gauge as blue diamonds, and the interpolated point as a red square:

When I enter 0.35 into cell B2, A2 calculates 0.400368, and the chart looks like this:

 

[Steve=True]

Great post, thanks Jon.

Steve=True

 

[kashifjillani]

To John Peltier,

Thank you mate you have solved a great problem of mine,

Tremendous approach and solution.

Regards

 

[bsmtool]

[TABLE="width: 1072"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD] x[/TD]
[TD]1.1[/TD]
[TD]1.2[/TD]
[TD]1.3[/TD]
[TD]1.4[/TD]
[TD]1.5[/TD]
[TD]1.6[/TD]
[TD]1.7[/TD]
[/TR]
[TR]
[TD]0.48[/TD]
[TD]1.002[/TD]
[TD]1.00375[/TD]
[TD]1.007[/TD]
[TD]1.01[/TD]
[TD]1.015[/TD]
[TD]1.0205[/TD]
[TD]1.027[/TD]
[/TR]
[TR]
[TD]0.46[/TD]
[TD]1.00225[/TD]
[TD]1.0045[/TD]
[TD]1.0075[/TD]
[TD]1.012[/TD]
[TD]1.0175[/TD]
[TD]1.024[/TD]
[TD]1.032[/TD]
[/TR]
[TR]
[TD]0.44[/TD]
[TD]1.0025[/TD]
[TD]1.005[/TD]
[TD]1.009[/TD]
[TD]1.015[/TD]
[TD]1.02[/TD]
[TD]1.027[/TD]
[TD]1.036[/TD]
[/TR]
[TR]
[TD]0.42[/TD]
[TD]1.003[/TD]
[TD]1.006[/TD]
[TD]1.0105[/TD]
[TD]1.016[/TD]
[TD]1.023[/TD]
[TD]1.0315[/TD]
[TD]1.0415[/TD]
[/TR]
[TR]
[TD]0.4[/TD]
[TD]1.0035[/TD]
[TD]1.0075[/TD]
[TD]1.0125[/TD]
[TD]1.019[/TD]
[TD]1.026[/TD]
[TD]1.036[/TD]
[TD]1.048[/TD]
[/TR]
[TR]
[TD]0.38[/TD]
[TD]1.004[/TD]
[TD]1.0085[/TD]
[TD]1.0145[/TD]
[TD]1.0215[/TD]
[TD]1.0305[/TD]
[TD]1.0415[/TD]
[TD]1.054[/TD]
[/TR]
[TR]
[TD]0.36[/TD]
[TD]1.0045[/TD]
[TD]1.01[/TD]
[TD]1.0155[/TD]
[TD]1.025[/TD]
[TD]1.0355[/TD]
[TD]1.0475[/TD]
[TD]1.062[/TD]
[/TR]
[TR]
[TD]0.34[/TD]
[TD]1.005[/TD]
[TD]1.0116[/TD]
[TD]1.0195[/TD]
[TD]1.029[/TD]
[TD]1.041[/TD]
[TD]1.055[/TD]
[TD]1.071[/TD]
[/TR]
[TR]
[TD]0.32[/TD]
[TD]1.006[/TD]
[TD]1.0135[/TD]
[TD]1.023[/TD]
[TD]1.034[/TD]
[TD]1.047[/TD]
[TD]1.063[/TD]
[TD]1.081[/TD]
[/TR]
[TR]
[TD]0.3[/TD]
[TD]1.007[/TD]
[TD]1.015[/TD]
[TD]1.026[/TD]
[TD]1.04[/TD]
[TD]1.054[/TD]
[TD]1.072[/TD]
[TD]1.093[/TD]
[/TR]
[TR]
[TD]0.28[/TD]
[TD]1.009[/TD]
[TD]1.019[/TD]
[TD]1.032[/TD]
[TD]1.046[/TD]
[TD]1.063[/TD]
[TD]1.084[/TD]
[TD]1.106[/TD]
[/TR]
[TR]
[TD]0.26[/TD]
[TD]1.01[/TD]
[TD]1.022[/TD]
[TD]1.037[/TD]
[TD]1.054[/TD]
[TD]1.074[/TD]
[TD]1.096[/TD]
[TD]1.121[/TD]
[/TR]
[TR]
[TD]0.24[/TD]
[TD]1.012[/TD]
[TD]1.026[/TD]
[TD]1.044[/TD]
[TD]1.063[/TD]
[TD]1.085[/TD]
[TD]1.111[/TD]
[TD]1.14[/TD]
[/TR]
[TR]
[TD]0.22[/TD]
[TD]1.015[/TD]
[TD]1.032[/TD]
[TD]1.052[/TD]
[TD]1.074[/TD]
[TD]1.1[/TD]
[TD]1.127[/TD]
[TD]1.16[/TD]
[/TR]
[TR]
[TD]0.2[/TD]
[TD]1.017[/TD]
[TD]1.037[/TD]
[TD]1.06[/TD]
[TD]1.085[/TD]
[TD]1.114[/TD]
[TD]1.146[/TD]
[TD]1.182[/TD]
[/TR]
[TR]
[TD]0.18[/TD]
[TD]1.02[/TD]
[TD]1.044[/TD]
[TD]1.07[/TD]
[TD]1.1[/TD]
[TD]1.132[/TD]
[TD]1.168[/TD]
[TD]1.206[/TD]
[/TR]
[TR]
[TD]0.16[/TD]
[TD]1.024[/TD]
[TD]1.053[/TD]
[TD]1.082[/TD]
[TD]1.116[/TD]
[TD]1.154[/TD]
[TD]1.195[/TD]
[TD]1.239[/TD]
[/TR]
[TR]
[TD]0.14[/TD]
[TD]1.029[/TD]
[TD]1.061[/TD]
[TD]1.092[/TD]
[TD]1.136[/TD]
[TD]1.18[/TD]
[TD]1.224[/TD]
[TD]1.272[/TD]
[/TR]
[TR]
[TD]0.12[/TD]
[TD]1.034[/TD]
[TD]1.073[/TD]
[TD]1.113[/TD]
[TD]1.16[/TD]
[TD]1.206[/TD]
[TD]1.25[/TD]
[TD]1.31[/TD]
[/TR]
[TR]
[TD]0.1[/TD]
[TD]1.04[/TD]
[TD]1.086[/TD]
[TD]1.135[/TD]
[TD]1.185[/TD]
[TD]1.241[/TD]
[TD]1.299[/TD]
[TD]1.357[/TD]
[/TR]
[TR]
[TD]0.095[/TD]
[TD]1.044[/TD]
[TD]1.09[/TD]
[TD]1.141[/TD]
[TD]1.194[/TD]
[TD]1.25[/TD]
[TD]1.309[/TD]
[TD]1.371[/TD]
[/TR]
[TR]
[TD]0.09[/TD]
[TD]1.05[/TD]
[TD]1.099[/TD]
[TD]1.15[/TD]
[TD]1.205[/TD]
[TD]1.264[/TD]
[TD]1.323[/TD]
[TD]1.387[/TD]
[/TR]
[TR]
[TD]0.085[/TD]
[TD]1.055[/TD]
[TD]1.108[/TD]
[TD]1.161[/TD]
[TD]1.218[/TD]
[TD]1.277[/TD]
[TD]1.34[/TD]
[TD]1.407[/TD]
[/TR]
[TR]
[TD]0.08[/TD]
[TD]1.066[/TD]
[TD]1.12[/TD]
[TD]1.175[/TD]
[TD]1.235[/TD]
[TD]1.29[/TD]
[TD]1.363[/TD]
[TD]1.43[/TD]
[/TR]
[TR]
[TD]0.075[/TD]
[TD]1.076[/TD]
[TD]1.134[/TD]
[TD]1.193[/TD]
[TD]1.255[/TD]
[TD]1.318[/TD]
[TD]1.388[/TD]
[TD]1.458[/TD]
[/TR]
[TR]
[TD]0.07[/TD]
[TD]1.091[/TD]
[TD]1.151[/TD]
[TD]1.213[/TD]
[TD]1.277[/TD]
[TD]1.345[/TD]
[TD]1.415[/TD]
[TD]1.489[/TD]
[/TR]
[TR]
[TD]0.065[/TD]
[TD]1.107[/TD]
[TD]1.169[/TD]
[TD]1.235[/TD]
[TD]1.302[/TD]
[TD]1.373[/TD]
[TD]1.445[/TD]
[TD]1.521[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD]1.126[/TD]
[TD]1.192[/TD]
[TD]1.26[/TD]
[TD]1.333[/TD]
[TD]1.405[/TD]
[TD]1.483[/TD]
[TD]1.56[/TD]
[/TR]
[TR]
[TD]0.055[/TD]
[TD]1.148[/TD]
[TD]1.216[/TD]
[TD]1.288[/TD]
[TD]1.364[/TD]
[TD]1.44[/TD]
[TD]1.52[/TD]
[TD]1.602[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD]1.17[/TD]
[TD]1.243[/TD]
[TD]1.319[/TD]
[TD]1.4[/TD]
[TD]1.48[/TD]
[TD]1.564[/TD]
[TD]1.65[/TD]
[/TR]
[TR]
[TD]0.045[/TD]
[TD]1.195[/TD]
[TD]1.275[/TD]
[TD]1.355[/TD]
[TD]1.438[/TD]
[TD]1.524[/TD]
[TD]1.61[/TD]
[TD]1.7[/TD]
[/TR]
[TR]
[TD]0.04[/TD]
[TD]1.224[/TD]
[TD]1.306[/TD]
[TD]1.391[/TD]
[TD]1.48[/TD]
[TD]1.569[/TD]
[TD]1.66[/TD]
[TD]1.752[/TD]
[/TR]
[TR]
[TD]0.035[/TD]
[TD]1.255[/TD]
[TD]1.341[/TD]
[TD]1.432[/TD]
[TD]1.525[/TD]
[TD]1.62[/TD]
[TD]1.715[/TD]
[TD]1.815[/TD]
[/TR]
[TR]
[TD]0.03[/TD]
[TD]1.288[/TD]
[TD]1.38[/TD]
[TD]1.478[/TD]
[TD]1.575[/TD]
[TD]1.675[/TD]
[TD]1.775[/TD]
[TD]1.88[/TD]
[/TR]
[TR]
[TD]0.025[/TD]
[TD]1.324[/TD]
[TD]1.424[/TD]
[TD]1.525[/TD]
[TD]1.628[/TD]
[TD]1.734[/TD]
[TD]1.838[/TD]
[TD]1.95[/TD]
[/TR]
[TR]
[TD]0.02[/TD]
[TD]1.36[/TD]
[TD]1.469[/TD]
[TD]1.575[/TD]
[TD]1.684[/TD]
[TD]1.798[/TD]
[TD]1.907[/TD]
[TD]2.02[/TD]
[/TR]
</tbody>[/TABLE]

Dear Jon
i have above data in which X value Ranges from 0.48 to 0.02 & Y value Ranges fronm 1.1 to 1.7
actually this is a graph with seven lines.
for example in this if we select value of X=0.48 & Y=1.1 Then results is 1.002

But i want to put values e.g. X=0.196 & Y=1.474
is it possible to do the same.
Thanks

 

[Jon Peltier]

This is much more complicated than a 1D lookup. We need to find not two values our final value is located between, but four values, corners of our lookup grid. We have to interpolate two columns between the rows, then interpolate these two results between columns.

First, sort your X values in ascending order. Here is the top part of my worksheet, with some of the data and with my intermediate calculations. I've shaded the X and Y edges to show where the lookup values lie, and also the 2x2 grid within which our interpolated value will be found.

The X and Y values being looked up are in cells L5 and M5.

Formulas are as follows:

Matches in X and Y arrays:

L7:
=MATCH(L5,$B$3:$B$38)

M7:
=MATCH(M5,$C$2:$I$2)

The 2x2 grid within which our answer lies (aka the corners):

L9:
=INDEX($C$3:$I$38,L7,M7)
L10:
=INDEX($C$3:$I$38,L7+1,M7)
M9:
=INDEX($C$3:$I$38,L7,M7+1)
M10:
=INDEX($C$3:$I$38,L7+1,M7+1)

The fraction of the way our lookup values are from the lower match to upper match (corners):

L12:
=(L5-INDEX($B$3:$B$38,L7))/(INDEX($B$3:$B$38,L7+1)-INDEX($B$3:$B$38,L7))
M12:
=(M5-INDEX($C$2:$I$2,M7))/(INDEX($C$2:$I$2,M7+1)-INDEX($C$2:$I$2,M7))

The interpolated rows:

L14:
=L9+(L10-L9)*L12
M14:
=M9+(M10-M9)*L12

And finally, our answer:
M16:
=L14+(M14-L14)*M12

I've used a lot of cells for intermediate calculations. I recommend you do as well, because it's easier to trace the calculation this way and fix any errors. But if you insist, here is the formula in cell M14 that uses only the lookup grid and the lookup values:

=INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)+1)+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2)+1)-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)+1))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38))))*(M5-INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)))/(INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)+1)-INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)))

 

[bsmtool]

Dear Sir

I don't have words, to say thank you.

You solve my problem by taking personnel interest

Many Many thanks from my Heart



Kulbir Singh

 

[3PlankWalker]

Many Many Thanks for this solution.

 

[Khaiha Norman]

John, how to solve pwf with known other parameters for this formula using excel?