Formula to create a regression line chart.

[sungen99]

I have NO idea of how to do this so I hope you guys might be able to offer some assistance.

I have attached an example showing the “Days” (days eating wheat) from 120 to 180 days.

As far as column B goes- this is the Average Daily Gain. I can tell you that at 120 days the gain is 2.80 and at 180 its 2.25. Just so you understand what I am actually looking at this is Cattle. So as you can imagine as the Cattle get larger they daily gain will shrink but its not lateral (a straight line). The slope should start out slight then drop off almost exponentially as you approach the 180th day to 2.25.

I assume there needs to be a couple of additional numbers to make this happen but again I don’t even know where to start.

Thank you for your help.

[MrShorty]

I am not sure where to start, either, because I am not sure exactly what you need to do. If I had to guess, here's how I would expect to approach this:

1) Extract the "known" data points from the table and put them somewhere convenient. Maybe put 120 into E2, 180 into E3, 2.8 into F2, and 2.25 into F3.
2) Using whatever regression tool you like (LINEST(), LOGEST(), chart trendline, or Data anaylysis->Regression) to perform the regression using those two known points.
2a) In order to do this, you will need to know something about the regression equation you want to use. It sounds like you don't want a simple straight line, but that will mean thinking through the kind of equation you want. Recognize that, with only two data points, your regression equation can have no more than two adjustable parameters.
2b) If you have no idea what kind of equation to use, the best I can recommend is to look at how others in your field model cattle weight gain. If that information is not available to you, review the basic curves you learned in algebra/calculus and choose one: http://www.dummies.com/education/mat...ebraic-curves/
http://tutorial.math.lamar.edu/Class...monGraphs.aspx
3) With parameters for your chosen equation obtained from the regression, enter appropriate formula into column B and copy down (pay attention to relative and absolute references to make copying easy).

What specific step do you need help with?

[sungen99]

Over time I will be able to enter actual numbers based on real word data but until then all i can say is that from 120 to 180 the down curve increases exponentially. I think what i am asking for is some formula (perhaps relying on a regression slope or whatnot) to simulate the curve. Again as I gain real data the curve will contain more an more real info.

Does that make any sense?

[shg]

There is surely an ocean of data available from feed lots, or any agricultural college (Texas A&M, VA Tech, ...), or any number of books on animal husbandry, that would make guessing unnecessary.

[shg]

For example: http://feedlotmagazine.com/archive/a...n6pg36-37.html

[MrShorty]

It kind of makes sense. How would you express this

all i can say is that from 120 to 180 the down curve increases exponentially.

as a mathematical equation? I hesitate to make a suggestion because I do not know what someone in your field would normally use for this kind of regression. I could see trying something like ADG(0)-ADG=bm^x (where you will need to pick a good value for ADG(0)), if you really have no idea what kind of equation to use. As that is just a guess, I would probably also suggest (again, assuming you have no idea what to use), that you research what kinds of equations are used for these models. I put "modeling cattle average daily gain" into my favorite internet search engine and found several resources (many of them professional journal articles) that may be useful for understanding how the cattle industry models ADG.

Perhaps to see how much of this is an Excel programming question and how much is the math question above, could you do this if you assumed a straight line model (ADG=mx+b)? Could you do it using an exponential decay model (ADG=b*m^x or ADG=b*exp(mx))? These are probably the easiest to program into Excel (thanks to the built in LINEST() and LOGEST() functions). If you can do these two, then the problem is mostly about choosing a suitable regression equation and performing the regression. If you cannot do it with these simplest models, then it might be easiest to focus on making those work, before tackling the more difficult regression equation that you want.

[sungen99]

Thank you all for your assistance.