Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LINEST function - data range
Excel 2007
Using Tables with dynamic ranges: http://www.mediafire.com/file/ymcnbwj4yju/12_16_08.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linest - different columns for x range | Excel Worksheet Functions | |||
Linest - different columns for x range | Excel Worksheet Functions | |||
Linest function | Excel Worksheet Functions | |||
Linest function: data not being selected in formula | Excel Worksheet Functions | |||
Linest function: data selection problems | Excel Worksheet Functions |