Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want to use trend() and linest() but some cells are empty, #N/A, o
I want to use trend() and linest() on a range of data but it returns
"#Value!" if there are empty cells or cells with #N/A, or "" in the "known x" or "known y" ranges. I have a template which I paste data into but it is never the same number of observed points, usually about 30. X is in B84:Bxxx , Y is in C84:Cxxx. I am trying to lookup the y on the curve fit at x observed. In F84 I have the following formula =TREND(C$84:C$133,B$84:B$133^{1,2,3},B84^{1,2,3}). I don't want to have to manual change the formula in F84:F133 because the data doesn't go all the way to row133. I have written a complicated macro to write the correct formula into each cell but I want a simpler way. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Want to use trend() and linest() but some cells are empty, #N/A, o
ACcompressor wrote...
I want to use trend() and linest() on a range of data but it returns "#Value!" if there are empty cells or cells with #N/A, or "" in the "known x" or "known y" ranges. .... It's large & ugly, but try the array formula =TREND(N(OFFSET(Y,SMALL(IF(ISNUMBER(X)*ISNUMBER(Y) ,ROW(X)-MIN(ROW(X))), ROW(INDIRECT("1:"&COUNT(1/ISNUMBER(X)/ISNUMBER(Y))))),0,1,1)), N(OFFSET(X,SMALL(IF(ISNUMBER(X)*ISNUMBER(Y),ROW(X)-MIN(ROW(X))), ROW(INDIRECT("1:"&COUNT(1/ISNUMBER(X)/ISNUMBER(Y))))),0,1,1))...,...) replacing X and Y with your known X and known Y range addresses, respectively. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining error associated with polynomial trend lines. | Charts and Charting in Excel | |||
LINEST, LOGEST, GROWTH or TREND?? | Excel Discussion (Misc queries) | |||
LINEST bug in Excel 2003 | Excel Worksheet Functions | |||
Trend Lines (or Linest?) | Excel Worksheet Functions |