Excel 2007 : Trying to create a dynamic LINEST() function for K-Ratio

[sherlucky]

21 Jan-12 0.237526814
22 Feb-12 0.228978729
23 Mar-12 0.230876595
24 Apr-12 0.244510957
25 May-12 0.268560542
26 Jun-12 0.292142458
27 Jul-12 0.292142458
28 Aug-12 0.292142458
29 Sep-12 0.292142458
30 Oct-12 0.292142458
31 Nov-12 0.292142458
32 Dec-12 0.292142458

Column A is the X, Column C is the Y... Column C are calculated numbers from another sheet in excel file I'm working on

Hello All. I'm trying to create a dynamic LINEST() function. Currently, if I take the Linest from January 2012 - June 2012, I'll get the stats. However, I need to create a formula which will incorporate number for July when that cell gets update, and so on and so forth.

I think its going to need an ADDRESS LOOKUP nested in the LINEST function...The address lookup should give a cell reference of $C$6....something like this?

=LINEST(C1:ADDRESS(LOOKUP),IF(C1:ADDRESS(LOOKUP),A1:A12),TRUE,TRUE)

[Richard Buttrey]

Hi and welcome to the forum.

The easiest way is to create two dynamic range names.

kRatiosX
=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$C:$C),1)

kRatiosY
=OFFSET(Sheet3!$C$1,0,0,COUNTA(Sheet3!$C:$C),1)

Then the LINEST function is
=LINEST(kRatiosY,kRatiosX)

Note. This assumes there are no other values in columns A & C underneath the last stat.

You'll also have a problem with November 31st & December 32nd, but I guess you've probably worked that out

[sherlucky]

This named data range takes into account cells C7:C12 and A7:A12 which are months July to December 2012. I confirmed with analysis toolpack stats. I need to find a formula where currently it will only take into account, month January to June. Once, July's data comes in, it should take into account Jan-July.

In my data set, July - Dec numbers are automatically calculated through a formula. THOSE ARE NOT THE ACTUAL NUMBERS. THOSE ARE JUST PRESET FORMULAS.

I've been trying to figure this out for 2 days now. LOL

[sherlucky]

Actually in the COUNTA part I was putting in C1:C12...I'm not supposed to do that but I fixed that part. So I cannot even confirm with data analysis toolpak.

HOWEVER, not I'm getting 0's for most of my stats and I'm only able to to get the constant... I'm not familiar with the defined named ranges and offset formula.

[Richard Buttrey]

Hi,

I didn't mention 'defined range name'. I said 'dynamic range name'.

Can I suggest that you use the help to read about the OFFSET() function? And visit Debra Dalgleish's site http://www.contextures.com/xlNames01.html#Dynamic where you'll be able to read everything you ever wanted to know - and more, about dynamic range names, and indeed the OFFSET() function.

[sherlucky]

Now I'm getting error of #VALUE! for all the stats. I'm evaluating the error and the formula is doing this ...

LINEST($C$1:$C$13,$A$1:$A$13,1,1).... I don't know why its grabbing cells 13 in both A and C column and I don't know why its going to C13, it should go to C6 and A6 if this is supposed to be dynamic...

[Richard Buttrey]

Are you saying that's the actual LINEST formula? If so why doesn't it contain the dynamic range names that you've created?
What's the syntax of your two range names?
Are there any cells in C1:C13 that contain space characters and appear otherwise empty?

Upload a workbook if you still can't sort it.

[sherlucky]

I figured out how to use it...But I find that it is taking in to account July 2012-Dec 2012 numbers. I confirmed the data analysis took pack with regression analysis. How can I fix it, so that it can only take into account months January 2012 to June 2012?

Column C is calculated numbers of a different sheet in my workbook. July to Dec numbers are coming with a number of .292142458 because of a preset mathematical formula I grabbed from an other sheet.