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.

LINEST function - data range
 

I have just changed the cells so instead of displaying " " in empty cells it
displays NA(), but this still results in #VALUE being displayed. I've been
reading through the other posts all afternoon but can seem to find how to do
this. I do not want to run a macro as this sheet will need to be used by
laymen who just need to enter the data and get a pass/fail out.

I can find the last cell, the data in it but need to be able to reference
the cell itself(eg A894). eg =MAX(A1:LAST CELL with data in), so the formula
would be MAX(A1:A694) in this example. Where last cell could be A680-A700.
but if I only had 680 cells of data it returns #VALUE. Please help as I'm
really stuck.

LINEST function - data range
 

Excel 2007
Using Tables with dynamic ranges:
http://www.mediafire.com/file/ymcnbwj4yju/12_16_08.xlsx