Linest

[gary2312]

Hi there,

I've got a LINEST function working on some data, however each month new data is added.

Is there anyway to get this function to look at the new data automatically? I'ev tried blank cells, #n/a#s and putting zeros in cells but the function doesn't like these. I was thinking a pivot table that updates somehow might work but I may be completely off target with that idea.

Thanks

 

[Aladin Akyurek]

What is the current range of your data and in which sheet?

 

[gary2312]

My y values go from H15:H105, however H15:H20 are blank cells and H69 onwards contains #n/a's as these are referenced in a graph but i dont want them plotted. The x yalues are in E15:E105.

Then next month, H69 will have a numeric value then H70 and so on.

The data is in a sheet called "seasonal decomposition"

I've tried zeros but this then gives a completely wrong answer as these are taken into account by the function as proper data values.

Cheers

 

[Aladin Akyurek]

My y values go from H15:H105, however H15:H20 are blank cells and H69 onwards contains #n/a's as these are referenced in a graph but i dont want them plotted. The x yalues are in E15:E105.

Then next month, H69 will have a numeric value then H70 and so on.

The data is in a sheet called "seasonal decomposition"

I've tried zeros but this then gives a completely wrong answer as these are taken into account by the function as proper data values.

Cheers

I need the actual data range of data. Are these E20:E105 and H20:H105?

 

[gary2312]

well E21 is month 7 in my data, and H21 is where my first actual piece of data begins. I'm using a moving average model, so the first 6 months have no corresponding value.

 

[Aladin Akyurek]

well E21 is month 7 in my data, and H21 is where my first actual piece of data begins. I'm using a moving average model, so the first 6 months have no corresponding value.

Define Y_vals as referring to:

=x!$H$21:INDEX(x!$H:$H,MATCH(9.99999999999999E+307,x!$H:$H))

Define X_vals as referring to:

=x!$E$21:INDEX(x!$E:$E,MATCH(9.99999999999999E+307,x!$E:$E))

Substitute your sheet name for x.

 

[gary2312]

I've tried putting that formula in, but it only returns #value!

 

[gary2312]

If it helps, when I enter =MATCH(9.99999999999999E+307,$H:$H) it returns 68, which is the position of the last numeric value.

=INDEX($H:$H,MATCH(9.99999999999999E+307,$H:$H)) returns the value in position 68.

It just doesn't like the $H$21: at the beginning.

 

[Aladin Akyurek]

If it helps, when I enter =MATCH(9.99999999999999E+307,$H:$H) it returns 68, which is the position of the last numeric value.

=INDEX($H:$H,MATCH(9.99999999999999E+307,$H:$H)) returns the value in position 68.

It just doesn't like the $H$21: at the beginning.

You have to define Y_vals and X_vals thru Insert|Name|Define. If you don't how, just post back.

 

[gary2312]

sorry to post yet again, when using the following formula:

=LINEST(INDEX($H:$H,MATCH(9.99999999999999E+307,$H:$H)),INDEX($E:$E,MATCH(9.99999999999999E+307,$E:$E))^{1,2})

I get #num! returned, i dunno if this is any help?