LINEST function - data range
I'm using the LINEST function to obtain a best fit line to some data. The
problem is that the ammount of data can change from 685 readings to 700
readings. For the best fit i need it to automatically use all the data
available to obtain the best fit, and I do not want to be changing the array
for each series of data I enter (there is a lot of data to go though and
sheet needs to be data entered-pass fail out). I currently have the LINEST to
use cells 1-690 as most of the time there are at leat 690 results, but
occasionally ther are not which results in #VALUE being displayed. Is there a
way of getting the LINEST function to take into account all the data and
icnoring empty cells? (I think i should also add I process the data to remove
noise so I suspect the blank cells are text even though they are blank). As I
only use the first 690 cells I could also be ignoring imortant data as the
bigest variation is at the end of the results.
|