Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using linest excel function from msaccess with multidimensional ar | Excel Worksheet Functions | |||
linest r-squared bug when calculating forced through zero? | Excel Worksheet Functions | |||
LINEST bug in Excel 2003 | Excel Worksheet Functions | |||
How to use linest with variably sized data arrays? | Excel Worksheet Functions | |||
LINEST bug with cubic polynomials in Excel 2003 | Excel Worksheet Functions |