If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Determining error associated with polynomial trend lines. | BJ Richter | Charts and Charting | 1 | April 21st, 2006 10:16 PM |
LINEST, LOGEST, GROWTH or TREND?? | NlCO | General Discussion | 1 | August 23rd, 2005 12:29 PM |
Trend Lines (or Linest?) | Martinaire | Worksheet Functions | 2 | November 26th, 2004 06:08 PM |
Problems with LINEST in Excel 2003 | hypersonic | Worksheet Functions | 1 | August 17th, 2004 10:36 PM |
Trend line calculations | Jerry W. Lewis | Charts and Charting | 1 | January 8th, 2004 04:09 PM |