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,

--
RJ

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" wrote in message
...
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,

--
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" wrote:

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" wrote in message
...
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,

--
RJ

Like this:

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

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

If you use this as a named range:

InsertNameDefine
Name: rng
Refers to: the above formula

Then this array formula:

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

Biff

"RJ" wrote in message
...
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" wrote:

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" wrote in message
...
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,

--
RJ