interpolating a number

[Soccer boy]

i have a question. i am trying to find a function or some way to help me
interpolate a range of numbers to find a given value. say i have the range
of numbers below.

X Y
1 100%
1.5 90%
2 80%
2.5 70%
3 60%
3.5 50%
4 40%
4.5 30%
5 20%

Now i want to find the value of X that corresponds to a Y value of 65% now
obivioulsy the value is going to be 2.75, but say i have a harder range of
numbers is there a way i can do this.

 

[Luke M]

For linear interpolation, you can use the TREND function. Just note that the
way you are descirbing it, swap your x's and y's. (function requires you to
input new x's)

=TREND(B2:B10,A2:A10,65)

 

[Soccer boy]

Thanks Luke, your post helped me kinda, but what if my x and y values dont
form a straight line. and is more of an S curve. the trend fuction only
really works with a linear line. Is there something that can work for that.

 

[Lori Miller]

=percentile(A:A,1-percentrank(B:B,D2,30))

with data in columns A and B eg D2=65% gives 2.75
To find a y-value given an x-value, switch A:A and B:B

**Note that this formula assumes there is a decreasing relationship
and will return the same answer regardless of how data is ordered.
If there is an increasing relationship, remove the "1-".

 

[Ron Rosenfeld]

i have a question. i am trying to find a function or some way to help me
interpolate a range of numbers to find a given value. say i have the range
of numbers below.

X Y
1 100%
1.5 90%
2 80%
2.5 70%
3 60%
3.5 50%
4 40%
4.5 30%
5 20%

Now i want to find the value of X that corresponds to a Y value of 65% now
obivioulsy the value is going to be 2.75, but say i have a harder range of
numbers is there a way i can do this.

Your specification is incomplete.

If you want to do a straight-line interpolation *between* any two of your
points, you could use a formula like:

=TREND(OFFSET(Y,MATCH(D2,Y,-1),-1,-2),OFFSET(Y,MATCH(D2,Y,-1),0,-2),D2)

Where Y is the range containing your "Y" values; X is the range containing your
"X" values, and D2 contains your "given value".

This formula will return an error for values at or below the lower limit, so
you might need to test for that. It also assumes that the ascending order of
X's and the descending order of Y's, as you posted, will always be the case.
--ron

 

[Herbert Seidenberg]

Excel 2007
For compound S curves:
http://www.mediafire.com/file/nzmonlonvwj/04_14_09.xlsx