LINEST
 

This is a function I have never used before but I am trting to help a
colleague.
Cell A1 is headed "projection". Cells B1:K1 are headed April - March.
cells B2:K2 contain the figures
87.48% 102.49% 95.27% 96.67% 96.57% 96.58% 96.92% 97.14% 96.98% 97.32%
cell A2 contains the formula "=K2+LINEST(B2:K2)". these figures give a value
of 97.68%
the problem occurs with blank cells & having to revise the formula each
month. In May it would read "=C2+LINEST(B2:C2)". June would be
"=D2+LINEST(B2:D2)" and so on.
can anyone help with a formula that would ignore blank cells in B2:M2 & give
the formula"=last filled cell+LINEST(B2:last filled cell).
Thanking somebody in anticipation.

LINEST
 

Try something like
=OFFSET(A2,0,COUNT(B2:M2))+LINEST(OFFSET(B2,0,0,1, COUNT(B2:M2)))
You may need to use a countif(B2:M2,"<0") rather than the count if the data
is calculated rather than entered
"pjk@boro" wrote:

This is a function I have never used before but I am trting to help a
colleague.
Cell A1 is headed "projection". Cells B1:K1 are headed April - March.
cells B2:K2 contain the figures
87.48% 102.49% 95.27% 96.67% 96.57% 96.58% 96.92% 97.14% 96.98% 97.32%
cell A2 contains the formula "=K2+LINEST(B2:K2)". these figures give a value
of 97.68%
the problem occurs with blank cells & having to revise the formula each
month. In May it would read "=C2+LINEST(B2:C2)". June would be
"=D2+LINEST(B2:D2)" and so on.
can anyone help with a formula that would ignore blank cells in B2:M2 & give
the formula"=last filled cell+LINEST(B2:last filled cell).
Thanking somebody in anticipation.

LINEST
 

bj you're a star. Thanks very much. pjk@boro.

"bj" wrote:

Try something like
=OFFSET(A2,0,COUNT(B2:M2))+LINEST(OFFSET(B2,0,0,1, COUNT(B2:M2)))
You may need to use a countif(B2:M2,"<0") rather than the count if the data
is calculated rather than entered
"pjk@boro" wrote:

This is a function I have never used before but I am trting to help a
colleague.
Cell A1 is headed "projection". Cells B1:K1 are headed April - March.
cells B2:K2 contain the figures
87.48% 102.49% 95.27% 96.67% 96.57% 96.58% 96.92% 97.14% 96.98% 97.32%
cell A2 contains the formula "=K2+LINEST(B2:K2)". these figures give a value
of 97.68%
the problem occurs with blank cells & having to revise the formula each
month. In May it would read "=C2+LINEST(B2:C2)". June would be
"=D2+LINEST(B2:D2)" and so on.
can anyone help with a formula that would ignore blank cells in B2:M2 & give
the formula"=last filled cell+LINEST(B2:last filled cell).
Thanking somebody in anticipation.