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:

[TABLE="width: 800"]
<tbody>[TR]
[TD]X Values[/TD]
[TD]Oct 14[/TD]
[TD]Nov 14[/TD]
[TD]Dec 14[/TD]
[TD]Jan 15[/TD]
[TD]Feb 15[/TD]
[TD]Mar 15[/TD]
[TD]Apr 15[/TD]
[TD]May 15[/TD]
[TD]Jun 15[/TD]
[TD]Jul 15[/TD]
[TD]Aug 15[/TD]
[TD]Sep 15[/TD]
[TD]Oct 15[/TD]
[/TR]
[TR]
[TD]Y Values[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]161[/TD]
[TD]171[/TD]
[TD]180[/TD]
[TD]185[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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!