howto formula w/ blank cell

[lgt_shadow]

I’ve got a worksheet that has over 1000 rows that I need to run the
following formula on.
=INDEX(LINEST(B13:L13,LN(B3:L3)),1)

Trouble is that not all rows have values in all 11 cells (first section
in this case B13:L13).
If I fill the empty cells with 0’s then that will change the result of
the formula as compared to running it on only the subset of data.

Example
57 196 280 450 180 328 220 315 305 237 94
5 10 12 8 15 13 0 0 0 0 0
=INDEX(LINEST(A2:K2,LN(A1:K1)),1) == 1.46
=INDEX(LINEST(A2:F2,LN(A1:F1)),1) == 2.37

Also have the problem that it’s not necessarily the first or last
columns that will be missing values it could be just one or two in the
middle.

So does anyone know of something that I can put into the empty cells to
make the formula still work but will not change the value of the
formula?

Ideally I’d like to use the first formula but have something to put in
place of the 0’s that would give me the answer of the second formula.

Is there a ‘null value’ or a ‘skip cell’ kind of thing that could be
used where the 0’s are above?

Any help would be greatly appreciated and an unbelievable time saver.


thanks
tim

 

[Harlan Grove]

I’ve got a worksheet that has over 1000 rows that I need to run the
following formula on.
=INDEX(LINEST(B13:L13,LN(B3:L3)),1)

Trouble is that not all rows have values in all 11 cells (first section
in this case B13:L13).
If I fill the empty cells with 0’s then that will change the result of
the formula as compared to running it on only the subset of data. ....
Also have the problem that it’s not necessarily the first or last
columns that will be missing values it could be just one or two in the
middle. ....
Is there a ‘null value’ or a ‘skip cell’ kind of thing that could be
used where the 0’s are above?

....

To answer the last question first, NO, there is no null/missing/skip value
in Excel that'd be recognized as such by the general regression functions
(LINEST, LOGEST, TREND, GROWTH). It's a serious drawback to Excel as a tool
for regression analysis, but Excel is exceptionally deficient among
spreadsheets.

However, since you have a single X variable (ignoring the constant), you
don't need to use LINEST. If you're running Excel 2002 or earlier, you could
use

=SLOPE(B13:L13,LN(B3:L3))

If you're using Excel 2003, see

http://google.com/groups?selm=B%[email protected]

 

[Harlan Grove]

. . . but Excel is exceptionally deficient among spreadsheets.

....

Make that Excel *ISN'T* exceptionally deficient among spreadsheets.

 

[lgt_shadow]

Great that works just as I had hoped. Now one more thing do you know o
a way to find the intercept? Ie =INDEX(LINEST(B13:L13,LN(B3:L3)),2) <
with the 2 at the end?


Yes I have found excel to be very useful unfortunately I’m just not us
to all the little quirks that it has so it’s been an interestin
learning experience.

ti

 

[lgt_shadow]

Never mind I got it from rereading that website that you gave me that’
great. Thank you you’ve been a big help to me.

Yeay no need to hand play-with 10K equations over the next coupl
weeks.

ti