Ignore blanks or 0's in Linest Formula

[dadsocks]

I have seen a couple of threads on this but haven't been able to get this to work for myself. I am trying to report on particular values over a calendar year; are these values trending upwards or downwards?

The particular project started mid year so January thru May are blank - but over time these values will populate (obviously).

How would I take the following and return a trend-line that ignores the blank values and returns a value based solely on populated months; in this case June 2015-Oct 2015:

X Values	Oct 14	Nov 14	Dec 14	Jan 15	Feb 15	Mar 15	Apr 15	May 15	Jun 15	Jul 15	Aug 15	Sep 15	Oct 15
Y Values	0	0	0	0	0	0	0	0	161	171	180	185	131

<tbody>
</tbody>

 

[rayk8301]

delete the zeros to make them blank. insert the data in a new chart, add trend line in the chart element. in the select data option, hidden and empty cells, select connect data points with line.

 

[dadsocks]

RayK - thanks for the reply - I am not charting this - It will simply be used to determine if this value is trending up or trending down. It's part of a dashboard so I do not want to have to adjust the formula every month for the next six months. This would also be valuable if there is a month that should not be included.

 

[rayk8301]

hmmm then I'm either not quite following or that I don't know of a formula that would help trend it. Unless you want to do conditional formatting with the arrows?

 

[Domenic]

First, define the following name...

1) Ribbon > Formulas > Defined Names > Define Name

2) Name: BigNum

3) Refers to: =9.99999999999999E+307

4) Click Ok

Then, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(INDEX(B3:N3,MATCH(TRUE,B3:N3<>0,0)):INDEX(B3:N3,MATCH(BigNum,B3:N3)),INDEX(B2:N2,MATCH(TRUE,B3:N3<>0,0)):INDEX(B2:N2,MATCH(BigNum,B3:N3)))

Note that here INDEX/MATCH is used to define the relevant range, which starts at the first cell that isn't empty or doesn't contain a zero, and ends at the last cell that contains a numerical value, including any zero's that may exist.

Hope this helps!