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

Leave a Reply

Your email address will not be published. Required fields are marked *