Expert: Jack Cheng - 2/17/2007

Question
Jack, I've just had a go at the trendline, i know now how to make one, however, how do i make up the values to fir the trend line? i looked at your formula for the ones u predicted, and adapted the equation of my trendline to the formula but it didn't come out right.  the graph completely changed and looked odd.  once i know how to make up the data for these tables, i then need to make a graph to show the relationship between each variable on one chart?
-------------------------------------------
The text above is a follow-up to ...

-----Question-----
hi jack,

heres a link for you to download the data i am using.  what i need to do is extrpolate data so to fill in the missing years, but at the same time it needs to fit the model consistently.

http://www.yousendit.com/download/ZGJlNU1BNDRrWTk1VEE9PQ
-------------------------------------------
The text above is a follow-up to ...

-----Question-----
Hi, i am looking at a sample from 1970 to 2004.  i have some data such as gdp and unemployment levels that fit this sample, however, i have only managed to find data that starts from 1992 for other vairables.  I there any possibility to simulate data from 1970? if so how?  i am not very bright in stats or maths so my level of understanding will be low.
-----Answer-----
Hi Shah,

As I don't know what data you have, I cannot give you specific details.  You can try look up linear regression or quadratic regression to see if they would help.

~ Jack
-----Answer-----
Using regressions would probably be your best bet. Using your data, you can create a scatterplot. With that scatterplot, you can have your stats software (Excel, I presume) to create a trendline. The trendline would then give you approximate values for the years with missing values.  

I'll try to walk you through an example. I might not have your version of Office, so some of my directions might not be correct; you should still be able to find those functions though. If not, ask again and I'll try to do some research for you.

1) Select cells A1 to I36 (basically everything).

2) Click Insert -> Chart. Select XY (Scatter) on the left side. Click next.

3) Select the Series tab on top. On the bottom left, you should see a complete list of all the title headers (e.g. NETIM, UKUN%TOTQ). Get rid of all but one of those headers (select one, then press Remove). Press Finish.

4) You should now have a graph. Depending on the series you selected, your graph will look different.

5) Select the chart (if it's not selected already), click Chart -> Add Trendline...

6) There are several types of trendlines. If you've taken Stats, you should be able to recognize them and choose the right time for your graph. If not, just use trial and error (that's how I learned it at first :)  

[Hint: if the graph looks straight, linear is your best choice. If it curves up, try either logarithmic, exponential, or power (the pictures should give you a hint). If there are many curves, use Polynomial and just play around with the Order setting. You can try the moving average, but I think the other options should more than suffice.]

7) Select your trendline.

8) Click the Option tab on top, and make sure Display Equation on Chart is checked. Click OK.

9) The trendline, along with the equation should be displayed on the graph. If your choice of trendline isn't the best, then just undo and start again from step 5)

10) The equation is what is important, because it can tell you what the value is in terms of the year. You can use the equation as a formula to determine the unknown values.

Here are some examples that I did with your data:

http://www.mediafire.com/?d4mied55mgm

I hope this helps,
Jack

Answer
I guess I was really lucky when I did the examples, picking the easiest two to do.  ;)

The other graphs are much harder to deal with. Polynomial degree 2 seems to work with UKYCBH well. UKYCBE is somewhat linear, if you ignore the first value. I am really not sure what to do with UKYCBQ, and NETIM, especially with its sinusoidal natures. This is the best that I can do on Excel, and I've never used EView, so I can't help you on that.

Sorry, but good luck somewhere else.

I am also posting this on the Question Pool in case another expert can help you.

~ Jack