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]

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?

Just define Y_vals and X_vals using Insert|Name|Define. After that:

=LINEST(Y_vals,X_vals)

will suffice.

 

[gary2312]

i think ive defined the names, but when using these in the lineast formula it returns #REF!

=LINEST(Duration,x^{1,2})

I assume this is correct?

 

[Aladin Akyurek]

i think ive defined the names, but when using these in the lineast formula it returns #REF!

=LINEST(Duration,x^{1,2})

I assume this is correct?

What is Duration and what is x?

 

[gary2312]

Duration is my y values, so i went to insert/name/define and then put in your formula.

If i click on Duration in the names thing, this is what appears in the "refers to" box.

='Seasonal Decomposition'!$H$21:INDEX('Seasonal Decomposition'!$H:$H,MATCH(9.99999999999999E+307,'Seasonal Decomposition'!$H:$H))


Similarly x is my x values, which is the other formula u mentioned.

='Seasonal Decomposition'!$E$21:INDEX('Seasonal Decomposition'!$E:$E,MATCH(9.99999999999999E+307,'Seasonal Decomposition'!$E:$E))

 

[Aladin Akyurek]

Duration is my y values, so i went to insert/name/define and then put in your formula.

If i click on Duration in the names thing, this is what appears in the "refers to" box.

='Seasonal Decomposition'!$H$21:INDEX('Seasonal Decomposition'!$H:$H,MATCH(9.99999999999999E+307,'Seasonal Decomposition'!$H:$H))


Similarly x is my x values, which is the other formula u mentioned.

='Seasonal Decomposition'!$E$21:INDEX('Seasonal Decomposition'!$E:$E,MATCH(9.99999999999999E+307,'Seasonal Decomposition'!$E:$E))

That appears OK, although x as name is a bit terse...

What did you mean by

x^{1,2}

in the LINEST formula?

 

[gary2312]

well when i have got the linest to work on data before, i wanted a quadratic trendline.

I found this website which explains why i used ^{1,2}

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

=LINEST(y's,x's^{1,2})

then pressing CTRL, SHIFT then Enter gives u 3 numbers which relate to the regression equation.

=LINEST(duration,x) still gives #REF!

 

[Aladin Akyurek]

well when i have got the linest to work on data before, i wanted a quadratic trendline.

I found this website which explains why i used ^{1,2}

http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

=LINEST(y's,x's^{1,2})

then pressing CTRL, SHIFT then Enter gives u 3 numbers which relate to the regression equation.

OK. Thanks.

=LINEST(duration,x) still gives #REF!

#REF! could mean the ranges duration and x are not equally sized. What do you get when you try:

=COUNTA(duration)

=COUNTA(x)

 

[gary2312]

=counta(duration) = 48

=counta(x) = 85

Is that because for duration there are a load of #n/a's at the bottom of the data?

 

[Aladin Akyurek]

=counta(duration) = 48

=counta(x) = 85

Is that because for duration there are a load of #n/a's at the bottom of the data?

We defined duration and x independent of each other. I believe you'd want to use H-range to anchor your analysis: If so, then define

Lrow

as referring to:

=MATCH(9.99999999999999E+307,'Seasonal Decomposition'!$H:$H)

Then change duration's definition to:

='Seasonal Decomposition'!$H$21:INDEX('Seasonal Decomposition'!$H:$H,Lrow)

and x's definition to:

='Seasonal Decomposition'!$E$21:INDEX('Seasonal Decomposition'!$E:$E,Lrow)

 

[gary2312]

yeah it works!!

thank you very much indeed you excel genius!!