Create databased of trend line from each data point against the population

Hello,

I wanted to create a database of trend line from each data point against the population like in the attached.

However, it is time consuming to do formula and wondered if we can do it with macro.

Please let me know if anyone can help.

Originally Posted by MrShorty

If I may ask, what do you find time consuming?

I built something for this in just a few minutes. I notice that you are using all absolute references in your formulas, perhaps if you were more familiar with how to mix relative and absolute referencing. Here's what I did.

1) Add 2 rows at the top for a LINEST() function for each date range. E1:E2 =TRANSPOSE(LINEST(E$4:$KA$4,E$3:$KA$3)) note how I mixed relative and absolute references. Copy across.
2) Add 3 columns at the left to make a copy of the LINEST() results. Enter 1 in A20, Enter A20+1 in A21 and copy down as far as needed. B20:C20=TRANSPOSE(INDEX($E$1:$KA$2,0,$A20))
3) E20 =IF($A20+4<=COLUMN(),$C20+$B20*e$3,NA()) copy down and across as far as needed

No VBA needed, and it goes together fairly quickly.

This is awesome. You're genius.
I also wanted to ask if we create a chart based on this database of trend line like this. I wondered if you can find a intercept point among those lines?

Originally Posted by MrShorty

I am not sure I understand.

We can create a chart if we want fairly easily. The chart can find the y-intercept for each line, if we want it to (through the trendline feature), but we have already found the y-intercept from the LINEST() functions, so it seems like extra effort to have the chart do.

We can find x-intercepts for each line through some algebra. We have y=mx+b for each trendline, set y=0 and solve for x to get the x intercept.

We can find the intersection point for any two lines using algebra again. Set up a system of equations y=m1*x+b1 and y=m2*x+b2 and solve the system of equations for y and x (a good tutorial on solving systems of equations, if needed: http://www.purplemath.com/modules/systlin1.htm ).

Or are you looking for some other intercept?

I added few calculations to the spreadsheet that you returned. The number I highlighted yellow is the final number that I wanted to come up with. Is there a way to get these number without doing additional calculation which I highlighted gray? I guess it might be a very long formula or something. Can you help me?

Originally Posted by MrShorty

The blue calculations look like the average value for each row/line, and then the yellow value is the average of those averages.

If you recall from your first calculus class, we can use the definite integral to find average values of functions (Here's a very quick refresher http://archives.math.utk.edu/visual....s/5/average.1/ ). From this, you should be able to calculate the blue cells directly from the regression coefficients in rows 1 and 2 and the starting and ending dates in row 3. The yellow cell continues to be =AVERAGE(blue cells). The programming for that should be simple, the bulk of the work is doing the calculus.

I expect it is possible to eliminate the blue cells as well, if you are willing to do all of the algebra to do it.

I don't know how to calculate average by using the definite integral in excel. Any tips?