Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel - linest function with blanks help

  1. Feb 13, 2015 #1
    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:

  2. jcsd
  3. Feb 13, 2015 #2

    mfb

    User Avatar
    2016 Award

    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.
     
  4. Feb 13, 2015 #3
    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Excel - linest function with blanks help
  1. I need help with Excel (Replies: 2)

Loading...