Using trend data set to populate a range with the projected numbers

**livemusic** · 03-01-2012, 10:27 AM

Say I have a natural gas well. All natural gas wells are in perpetual decline, just the nature of the beast. If I take a given data set for monthly production for, say, the past 24 months, I can create a chart and Excel can generate a best-fit or trend line to that data set. Linear, exponential, etc. You can also have it extend the trend line x-months into the future. That's all well and good but then I just have a nice line to eyeball. But I need to extend the data set for, say, another 24 months (into the future) with the projected numbers. How do I do this?

**MrShorty** · 03-01-2012, 12:21 PM

If I understand what you are doing, my preference is to use the LINEST worksheet function to generate my best fit curve, then make a table in the spreadsheet with the desired projections from those coefficients.

Don't let the LINear in LINEST fool you into thinking that it only works for "straight lines." The linear refers to "linear functions" in the linear algebra sense, so it can be used to generate a best fit for all kinds of equations.

**livemusic** · 03-03-2012, 04:05 PM

It seems it would be simpler. For example, say I generate an exponential decline curve based on production number per month. I want to populate a range of cells with the forecast production for the next 36 months. How exactly do I get the forecast numbers into the cells?

**MrShorty** · 03-05-2012, 12:11 AM

The first step to getting the forecast numbers is to generate the best fit curve.

You can generate the best fit curve using a chart trendline (then hand enter the coefficients into the spreadsheet), by using the built in regression tool, or by using the LINEST function (the way I usually do it).

I don't know exactly what you mean by exponential decline curve. I usually think of something of the form ln(y)=ln(A) + Bln(x). To do this using LINEST, setup a table in your spreadsheet where you convert the data to ln(y) and ln(x). Then pick a convenient place in the spreadsheet, select two cells, enter the LINEST function (see Excel's help files). Be sure to confirm with ctrl-shift-enter since LINEST is an array function. Then you can build a forecast table where you input the desired "x" values for the forecast, then use the results of the LINEST function to calculate the forecasted values. Assuming the form I've assumed is correct, this will look like =EXP(A+B*LN(x))