#1   Report Post  
Posted to microsoft.public.excel.misc
pjk@boro
 
Posts: n/a
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
bj
 
Posts: n/a
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
pjk@boro
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using linest excel function from msaccess with multidimensional ar jobxyz Excel Worksheet Functions 1 December 26th 05 03:11 PM
linest r-squared bug when calculating forced through zero? Filtration Guy Excel Worksheet Functions 7 November 14th 05 02:46 AM
LINEST bug in Excel 2003 mathman Excel Worksheet Functions 11 June 21st 05 02:05 PM
How to use linest with variably sized data arrays? [email protected] Excel Worksheet Functions 0 April 13th 05 04:56 PM
LINEST bug with cubic polynomials in Excel 2003 byundt Excel Worksheet Functions 3 March 21st 05 03:15 PM


All times are GMT +1. The time now is 11:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"