Approximating a curve
 

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Look at FORECAST, TREND, GROWTH, LINEST, and LOGEST and see which function
suits your needs.

Dave
--
Brevity is the soul of wit.


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Use FORECAST(). So in your example, say your x values are in the range
A1:A20 and your y values are in the range B1:B20:

=FORECAST(x_to_predict,B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Thanks Dave none of these functions will produce the results I am looking for.

I really need the formula that produces the curve but I can not get that.


"Dave F" wrote:

Look at FORECAST, TREND, GROWTH, LINEST, and LOGEST and see which function
suits your needs.

Dave
--
Brevity is the soul of wit.


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Thanks Dave this function will produce the results I am looking for.

I really need the formula that produces the curve but I can not get that


"David Billigmeier" wrote:

Use FORECAST(). So in your example, say your x values are in the range
A1:A20 and your y values are in the range B1:B20:

=FORECAST(x_to_predict,B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

So this IS or ISN'T what you were looking for? In your reply to Dave F you
said none of those functions work... and then you told me that does work.

Do you need like a slope/intercept? Like y=m*x+b, in this case:
m (slope) =SLOPE(B1:B20,A1:A20)
b (intercept) =INTERCEPT(B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Thanks Dave this function will produce the results I am looking for.

I really need the formula that produces the curve but I can not get that


"David Billigmeier" wrote:

Use FORECAST(). So in your example, say your x values are in the range
A1:A20 and your y values are in the range B1:B20:

=FORECAST(x_to_predict,B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Sorry David I missed out a 'Not'

You are on the correct track; that is what I am trying to do assume a
straight line between the two points. Not sure why i can not get this to
work. I tried the (Slope & Intercept ) on two set of figures and I can not
get them to work. It is making feel pretty dim at the moment.

Thanks for your help

"David Billigmeier" wrote:

So this IS or ISN'T what you were looking for? In your reply to Dave F you
said none of those functions work... and then you told me that does work.

Do you need like a slope/intercept? Like y=m*x+b, in this case:
m (slope) =SLOPE(B1:B20,A1:A20)
b (intercept) =INTERCEPT(B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Thanks Dave this function will produce the results I am looking for.

I really need the formula that produces the curve but I can not get that


"David Billigmeier" wrote:

Use FORECAST(). So in your example, say your x values are in the range
A1:A20 and your y values are in the range B1:B20:

=FORECAST(x_to_predict,B1:B20,A1:A20)

--
Regards,
Dave


"Steve J. Vaughan" wrote:

Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Steve J. Vaughan wrote:
what I am trying to do assume a
straight line between the two points.

This is different from my (and others') interpretation of your original
question. I suspect that what you want to do is interpolate values of
Y between any two values of X in the list that you provided in your
first posting (see below), thereby providing points along the curve
defined by the table of known X and Y. Right?

That is, given a table of known X and Y, look up an intermediate value
of X (X[k]) to find the largest known X (X[i]) less than or equal to
X[k], then compute the corresponding intermediate value of Y (Y[k])
that lies on the straight line between Y[i] and Y[i+1]. Right?

If that is what you want, perhaps the following will suit your needs.
Let A1:A17 contain known X and B1:B17 contain known Y such that A1:B17
is the table of known values. Let C1 contain X[k], the intermediate
value of X. Then:

C2: =MATCH(C1, A1:B17)

D1: =TREND(OFFSET(B1,C2-1,0):OFFSET(B1,C2,0),
OFFSET(A1,C2-1,0):OFFSET(A1,C2,0), C1)

C2 is the row offset from A1 of the largest X[i] less than or equal to
X[k] in C1. D1 computes the corresponding Y[i]. The expressions
"OFFSET(...):OFFSET(...)" specify the subtables of Y[i+1]:Y[i] and
X[i+1]:X[i].

You could avoid the use of C2 by replacing C2 with VLOOKUP() everywhere
in the formula in D1.

I suspect there is some easier way to do all this. Now that the
problem is properly specified (if I am right), perhaps someone more
adept with Excel can offer a cleaner solution.


----- first posting -----

Steve J. Vaughan wrote:
Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Thanks Joe, That is what I am attempting to do.

I managed it with using slope & intercept and a vlookup table once I got my
head in gear this morning.

Thanks again

" wrote:
[i]
Steve J. Vaughan wrote:
what I am trying to do assume a
straight line between the two points.

This is different from my (and others') interpretation of your original
question. I suspect that what you want to do is interpolate values of
Y between any two values of X in the list that you provided in your
first posting (see below), thereby providing points along the curve
defined by the table of known X and Y. Right?

That is, given a table of known X and Y, look up an intermediate value
of X (X[k]) to find the largest known X (X[i]) less than or equal to
X[k], then compute the corresponding intermediate value of Y (Y[k])
that lies on the straight line between Y[i] and Y[i+1]. Right?

If that is what you want, perhaps the following will suit your needs.
Let A1:A17 contain known X and B1:B17 contain known Y such that A1:B17
is the table of known values. Let C1 contain X[k], the intermediate
value of X. Then:

C2: =MATCH(C1, A1:B17)

D1: =TREND(OFFSET(B1,C2-1,0):OFFSET(B1,C2,0),
OFFSET(A1,C2-1,0):OFFSET(A1,C2,0), C1)

C2 is the row offset from A1 of the largest X[i] less than or equal to
X[k] in C1. D1 computes the corresponding Y[i]. The expressions
"OFFSET(...):OFFSET(...)" specify the subtables of Y[i+1]:Y[i] and
X[i+1]:X.

You could avoid the use of C2 by replacing C2 with VLOOKUP() everywhere
in the formula in D1.

I suspect there is some easier way to do all this. Now that the
problem is properly specified (if I am right), perhaps someone more
adept with Excel can offer a cleaner solution.


----- first posting -----

Steve J. Vaughan wrote:
Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give the same
value of Y 19.51

Approximating a curve
 

Steve, you have the classic condition here for interpolation. First,
you have a curvilinear regression happening, not a straight
correlation. So, the traditional slope formula will not work. Your
data set shows a nice progression except for one "bump."

Anyway, interpolation is fairly simple to explain and you can probably
get the formula set up better than I could. Look at the two points on
your data set from 14000-20000. That is a range of 6000. The
corresponding Y values are 10.68 to 19.51, a spread of 8.83. If you
change X by 6000 you change Y by 8.83. Now, if you change X by 3000,
you change Y by 4.415. If you change X by 1000, you change Y by 1.47.
(This is 8.83 divided by 6.)

The basic idea of interpolation is fairly simple. You assume the
connection between two known points on your scale is a straight line.
You assume the slope of the line changes with each two points known
points (e.g., 1400-2075 or 3450-4150). You need to calculate the
range from one X point to the next X point. Calculate the range from
one Y point to the next Y point. Then, get the relationship between
them for a unit change in the X series. For your "new" X point,
calculate the expected Y value.

Hope this helps. I did not give you a formula--I am not that
proficient in all of Excel's instructions; would use a different
programming language for something like this.




"Steve J. Vaughan" wrote in
message ...
Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give
the same
value of Y 19.51

Approximating a curve
 

This is the equation for interpolation that I use. The formula may be off
one or two cells as I ripped it from a larger spread sheet.


A B
1 X Y Unknown Y value
2 1 5 Y= 10
3 2
4 3 15 (ABS((((A2-A3)/(A2-A4))*(B2-B4)))+B2)
Good luck!

Ron

"Michael Plog" wrote:

Steve, you have the classic condition here for interpolation. First,
you have a curvilinear regression happening, not a straight
correlation. So, the traditional slope formula will not work. Your
data set shows a nice progression except for one "bump."

Anyway, interpolation is fairly simple to explain and you can probably
get the formula set up better than I could. Look at the two points on
your data set from 14000-20000. That is a range of 6000. The
corresponding Y values are 10.68 to 19.51, a spread of 8.83. If you
change X by 6000 you change Y by 8.83. Now, if you change X by 3000,
you change Y by 4.415. If you change X by 1000, you change Y by 1.47.
(This is 8.83 divided by 6.)

The basic idea of interpolation is fairly simple. You assume the
connection between two known points on your scale is a straight line.
You assume the slope of the line changes with each two points known
points (e.g., 1400-2075 or 3450-4150). You need to calculate the
range from one X point to the next X point. Calculate the range from
one Y point to the next Y point. Then, get the relationship between
them for a unit change in the X series. For your "new" X point,
calculate the expected Y value.

Hope this helps. I did not give you a formula--I am not that
proficient in all of Excel's instructions; would use a different
programming language for something like this.




"Steve J. Vaughan" wrote in
message ...
Approximating a curve

I have a set of X and Y values for example

X Y
690 0.82
1400 1.72
2075 2.75
2760 3.93
3450 5.31
4150 6.68
4800 8.27
5500 8.34
7600 12.48
11750 8.27
14000 10.68
20000 19.51
24000 25.86
26500 28.89
28500 31.37
32500 33.3
34500 30.3


I need to calculate a value of Y for any value of X.

Is there a F(x) that will allow me to do this.

VLOOKUP is no good as a value of X from 20,001 to 23,999 will give
the same
value of Y 19.51