Chart needs #N/A, SLOPE function needs "q"

[ue418]

I have two columns (say M and N) of data (say, from rows 100 to 1000), where some contiguous region is full of numbers, but the region above and below will not have numbers. I never know beforehand exactly where the numbers will be. So the numbers may be between rows 100 and 400 in one case, but between 600 and 900 in another case, etc. I need to both: (a) plot column N vs. M, for the "numbered" region and (b) calculate the slope of the "numbered" region, using the SLOPE function. The problem is that in order for the "blank" cells (those with no numbers) to be ignored for charting, I have to make them say #N/A. However, in order for the blank cells to be ignored for the SLOPE function, they need to be some text (say, "q" or whatever). So I am between a rock and a hard place. If I make the "blank cells" say #N/A, I get a good chart, but NO slope calculation. If I make the blank cells say "qq", I get the slope calculation, but no charting! Does anyone know anything I can put in those blank cells that will allow BOTH charting and the use of the SLOPE function? BTW, I cannot rely on the slope (and equation) that one can get from the trendline on chart, because I need the slope to be assigned to an actual cell.



One solution would be to create two MORE columns (say, O and P), and use logic such as: If(M150=#N/A,"qq",M150). But when I do that, those cells in M that say #N/A still show up as #N/A in the new column (as opposed to "qq")!! If anyone can help me with this, I would at least have a workaround if there is no solution to the main problem. Thanks a lot.