Using LINEST and INDEX with #N/A and constantly updating cells

I have a data set comprised of weekly sales numbers. Column A is weeks of the year (1-52) and column B is cumulative total sales YTD. If there is sales data in any given week, the sales are expressed as a number (e.g. 100.45). If there is no sales data (i.e., if it is not yet week xx in the year), the cell returns #N/A. This is set up for graphing purposes, so the chart only shows weeks with numbers and does not graph weeks with #N/A.

On the chart I’ve created (y axis = cumulative YTD sales by x-axis = week of the year), I have a 2nd order polynomial trendline to estimate total sales, which updates automatically as more data are fed into the data set.

I’ve also set up a series of cells that solve the various components of that polynomial equation (y = Lx^2+ Mx + N) so I can estimate y at x = 52 — i.e., to answer the question “what is the trendline estimate of sales at the end of the year based on current sales YTD?”

This is the formula I’m using for L in my polynomial equation:

=INDEX(LINEST(B1:B52,A1:A52^{1,2}),1)

The problem is that I have to make sure that the range stops when the #N/A begins in order to avoid an error in my calculation. How can I improve my formula to “look” down my column of sales data and calculate L using only the cells without #N/A?

Here are my data (assuming it is the end of week 20):

 1 40 2 68 3 161 4 186 5 239 6 266 7 299 8 356 9 372 10 375 11 411 12 487 13 586 14 596 15 610 16 642 17 710 18 763 19 793 20 836 21 #N/A 22 #N/A 23 #N/A 24 #N/A 25 #N/A 26 #N/A 27 #N/A 28 #N/A 29 #N/A 30 #N/A 31 #N/A 32 #N/A 33 #N/A 34 #N/A 35 #N/A 36 #N/A 37 #N/A 38 #N/A 39 #N/A 40 #N/A 41 #N/A 42 #N/A 43 #N/A 44 #N/A 45 #N/A 46 #N/A 47 #N/A 48 #N/A 49 #N/A 50 #N/A 51 #N/A 52 #N/A

By: Joshua Miner