Offset code for dynamic row# & multiple columns in LINEST function

[RJ]

Hi,
I found this offset formula in a different post
offset(A1,1,0,counta(A:A)-1,1),
I think this is kind of what I am looking for, but I'm not positive, but I
would need it for multiple columns.

Essentially i need the following, (x being a dynamic row number being
determined by the last cell to have a value in the range).

=LINEST(B2:Bx,C2:Fx,TRUE,TRUE)

Any help is greatly appreciated, Thanks!

Best,

 

[T. Valko]

I'm assuming there will be no empty cells *within* the ranges.

For B2:Bx (B1 is the column header and *is not* a number)

=B2:INDEX(B:B,COUNT(B:B)+1)

For C2:Fx

=OFFSET(C2,,,COUNT(B:B),4)

Biff

 

[RJ]

Hey Biff,

That worked brilliantly, I really appreciate the help!

Can the same be done to columns, meaning if the row is always 2 but the
array coming accross the columns will vary? Is there away to dynamically
offset the column?

For example:
If I wanted to make the IJ$2 dynamic, where the column reference is always
the furthest to the right available. How would I offset the following.

=SUM(IF(Factors!$E$2:IJ$2>=D9,0,IF(Factors!$E$2:IJ$2>-9999,1,0)))

I've tried a couple of differnt ways with the =Offset function,
unsuccessfuly....

Thanks again for the help. I really appreciate it.

Best

RJ

---

 

[T. Valko]

Like this:

=Factors!$E$2:INDEX(Factors!$E$2:$IV$2,COUNT(Factors!$E$2:$IV$2))

Again, assuming no empty cells *within* the range.

If you use this as a named range:

Insert>Name>Define
Name: rng
Refers to: the above formula

Then this array formula:

=SUM(IF(rng>=D9,0,IF(rng>-9999,1,0)))

Biff