I am trying to figure out a formula for a set of data that I was given (For work). It is a list of how long a cutting job should take given a certain length of wood. The data is as follows:

Length |  20 |  40 |  60 |  80 | 100 | 150 | 600 |
Hours  | 0.2 | 0.3 | 0.4 | 0.5 | 0.6 | 0.9 | 3.8 |  

Now, given this data, I need to figure out a linear equation that will give me the appropriate number of hours for a given length of lumber: Hours = Length(x) + y. I would really like a way of figuring out x and y myself because there a few other data sets like this that need to have an equation built from them. Also, I would like a way to figure out exponential, quadratic, etc formulas if a linear one isnt the best fit. I hope you can give me some tips, thanks. [edited by - ISOPimp on March 19, 2003 4:47:58 PM]

Just used generic arithmatic (rise/run) but I got Y = 0.006X + 0.8


EDIT:Added 0

-~-The Cow of Darkness-~-

[edited by - cowsarenotevil on March 19, 2003 5:06:15 PM]

[edited by - cowsarenotevil on March 19, 2003 5:07:58 PM]

[edited by - cowsarenotevil on March 19, 2003 5:08:45 PM]

Just to play devil''s advocate. Did you ask your user about this? If you use f(x)=(x-20)*((3.8-0.2)/(600-20))+0.2 correctly gives f(20)=0.2 and f(600)=3.8, but f(100)=0.6966. If you are lucky when you turn it over they just ask you who told you it was ok for you to do that. If you are unlucky no one notices until a year later when a customer starts demanding a refund, ethics demands you give everyone a refund now that you are aware of it, it is an unexpected expense that hammers your cash flow and it is significant so you have to restate your earnings. The moral? Don''t count on your buddy or even your boss saying yeah that sounds like a good idea. Rather make sure the person responsible for providing this table thinks it is a good idea.

This is my finding:
x = length
f(x) = hours

f(x) = .0158473106(x) + .0062753036

To pose the devils advocate against LilBudyWizer''s devil''s advocate, just tell your customers that the chance for error in the expected time will increase as the length of the wood gets larger. More simply: The time it takes to do the job isn''t set in stone, don''t be surprised if it takes longer. It''s like going to a mechanics and getting an estimate.

This is simple statistics and that you want a linear trend line.

First, you won''t get a linear equation that that returns perfect numbers unless your data is 100% linear. However, you can get a best fit linear equation:
I came up with:
Hours = 0.0063*Length + 0.0158
With an error r^2 = 0.9989

You can calculate a best fit line by using the principle of least squares. So,

Slope = Sum((x-Xavg)*(y-Yavg)) / Sum( (x-Xavg)^2 )
Intercept = YAvg-Slope*XAvg
Where the Sum is from 1 to the number of elements, in your case 7.

r^2(error) = Sum( (y-h(x))^2 ) / n-2
Where h(x) is the formula generated above and n is the number of elements. In your case 7.



While this is how to do it. If you have access to execl just use that. Put in your data and created a scatter plot chart. Then add a trend line, it will do linear, logarithmic, polynomial, power, exponential, and moving average. Pick the one that best fits your data and turn on Display equation on char. It will create the equation and give it to you on the chart. If you want the error turn on r^2 too.

Here is the linear regression model produced by my calculator.

Y=0.0062753036X+0.015847316



-~-The Cow of Darkness-~-

Just to play devil''s advocate against devil''s advocate against devil''s advocate, oh wait, I''m already ih there once Depending on the business you may charge actual or standard time. My point is more that such decisions are not for the I/S department to make. I/S knows about writing programs. The business users know about what the customers expect, how to meet those expectations and how to do so while making a profit. Turn the business over to I/S and you have a bankrupt company. The dot com revolution is a good example of what happens when I/S runs a business the way they think it should be ran. Profits, that''s old economy. Nope, old economy are all the companies that went bankrupt before you and are no longer part of the current economy.

Thanks to everyone that helped.

Thank''s a lot to the below AP for a nice description of how the slope and intercept were obtained. I tried this and it works quite well. I also found the SLOPE() function in Excel XP which will speed up the process considerably.

quote:

---

Original post by Anonymous Poster
This is simple statistics and that you want a linear trend line.

First, you won''t get a linear equation that that returns perfect numbers unless your data is 100% linear. However, you can get a best fit linear equation:
I came up with:
Hours = 0.0063*Length + 0.0158
With an error r^2 = 0.9989

You can calculate a best fit line by using the principle of least squares. So,

Slope = Sum((x-Xavg)*(y-Yavg)) / Sum( (x-Xavg)^2 )
Intercept = YAvg-Slope*XAvg
Where the Sum is from 1 to the number of elements, in your case 7.

r^2(error) = Sum( (y-h(x))^2 ) / n-2
Where h(x) is the formula generated above and n is the number of elements. In your case 7.



While this is how to do it. If you have access to execl just use that. Put in your data and created a scatter plot chart. Then add a trend line, it will do linear, logarithmic, polynomial, power, exponential, and moving average. Pick the one that best fits your data and turn on Display equation on char. It will create the equation and give it to you on the chart. If you want the error turn on r^2 too.

---