number progresion relationship

[Ralphf]

This isn't a school related problem and I am not really even sure what section this would belong in. If this is the wrong spot for this please let me know and either move it to the appropriate area or I will move as appropriate.

I am trying to figure out how a program calculates value Y based on value X below is a table of the values that I am able to get. What I want to be able to do is have a calculation that I can put in excel and calculate what Value Y should be based on any given value X. I thought at first it would be a simple ratio but that doesn't seem to work out for me. Thanks for any help you can give.

X Y
7 33.65
11 32.98333333
17 32.01666667
28 30.3
29 30.15
30 29.98333333
35 29.25
36 29.1
39 28.66666667
101 21
102 20.9
103 20.8
104 20.7
105 20.18333333
107 20.38333333
108 20.28333333
109 20.18333333
111 19.98333333
112 19.88333333
113 19.78333333
115 19.58333333
119 19.2
120 19.1
121 19
132 17.98333333
134 17.8
135 17.71666667
141 17.18333333

 

[galactus]

Plug these into Excel and do a regression. Are you familiar with that?.

Try a linear regression, or maybe another polynomial regression.

Let me know if you don't and I can try to show you the steps. A lot of calculators do these as well.

 

[Ralphf]

Thanks galactus

Here is what I got using the regression formula in excell

X Value entered ----Logest Value-------------Linest Value------------------actual value
7 -----------------------41.82---------------------32.94----------------------------33.65
141---------------------175.15--------------------16.43----------------------------17.18

Sample Logest equation =SUM(LOGEST(G19:G46,F19:F46)*{7,1})
Sample Linest equation =SUM(LINEST(G19:G46,F19:F46)*{7,1})


Linest is fairly close and if that is the only way to do it I will have to use that, but if there is some other way to get it more exact I would appreciate the help.

Thanks

 

[galactus]

Yes, you can try other regressions.

Put your data in columns A and B.

Then, in the toolbar, click on INSERT, CHART, XY-SCATTER, Enter in your data range and click OK and all that until your graph appears.

Then, in the toolbar, click on CHART, ADD TRENDLINE, under Options make sure the R^2 and 'show equation on graph' boxes are checked.

The R^2 tells you your accuracy. That should be about it. You have various options here as to what regression you wish. There is a polynomial up to 6th power, there is an exponential regression, Power regression, Log regression, linear, and poly.

Let me know how it works out.

 

[Ralphf]

That worked out great... Used the xy-scatter with polynomial regression 2nd power. average deviation for the y values calcualated is .02 which is acceptable.

Thanks for all the help.