Excel - linest function with blanks help

1. Feb 13, 2015

lavster

Hi,
Ive been trying to use the linest function with blanks but I cant get it to work... (specifically =INDEX(LINEST(E6:E16,A6:A16^{1,2,3}),1). Here A6:A16 is a number increasing in increments of 1 between 25 to 35, and E6:E16 is only filled if the corresponding measurment is made. Does anyone know how to do this? I attach a screen shot
Thanks

Attached Files:

• screen shot.png
File size:
3.4 KB
Views:
135
2. Feb 13, 2015

Staff: Mentor

LINEST can be expressed in terms of more basic functions, if the built-in function does not work. Alternatively, you can make a second list with only the measured values.

3. Feb 13, 2015

ellipsis

The solution to this is non-trivial. The following should work, but it is an array function. This means, when you place it, you must press Ctrl+Shift+Enter, rather than Enter.

Code (Text):
=INDEX(LINEST(N(OFFSET(A6:A16,SMALL(IF(A6:A16<>0,ROW(A6:A16)-ROW(A6)),
ROW(INDIRECT("1:"&COUNT(A6:A16)))),,1)),N(OFFSET(E6:E16,
SMALL(IF(A6:A16<>0,ROW(A6:A16)-ROW(A6)),ROW(INDIRECT("1:"&
COUNT(A6:A16)))),,1))),1)
I'm not sure what "^{1,2,3}" was supposed to do, but I'm sure you can incorporate that through careful editing of the above expression.