Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
Fitting a curve similar to capacitor charging current. | New Users to Excel | |||
curve fitting a charging capacitor type curve | Excel Discussion (Misc queries) | |||
watch a curve change over time like a movie | Charts and Charting in Excel | |||
Vertical ND Curve on Combination Chart | Charts and Charting in Excel |