Trend Function to Ignore Zeros

[msquared99]

I am trying to look at sales data for many stores. Some stores were not open at some points in time. For instance, maybe 3 stores did not open until 11/16 while others were open from 07/16 to present. I want to use the TREND function but want it to ignore the zeros.

I did find a formula:=TREND(INDEX(G12:AM12,MATCH(TRUE,ISNUMBER(G12:AM12),0)):INDEX(G12:AM12,MATCH(9.99999999999999E+307,G12:AM12)))
However it gives an incorrect number of 214

Just using TREND on the cells containing values I get 1,126 but if I use TREND on all the values I get ZERO.

The AVERAGE ignoring zeros is 856. I used this formula for that: =AVERAGEIF(G12:AM12,"<>0")

Anyone have any ideas on how to get and accurate TREND?

 

[Peter Bartholomew]

Sorry but I am at something of a loss to understand what it is you are trying to achieve with your formula.
You appear to be using the same series for dependent and independent variables, e.g.
= TREND( storeA.value, storeA.value)
where 'storeA.value' is an absolute reference I provided for your Store A data. By definition, that will simply return the values as they are; without CSE you will get one value, namely the first which is zero.
I replaced your formula with a dynamic array formula
= TREND( storeA.value, date )
using the date to get the grey line below.

This is unduly influenced by the zeros at the beginning of the series when the store had yet to open.



Because of the version of Excel that I use, I was able to filter out the zero amounts from the value row along with the corresponding dates.
= TREND( FILTER(storeA.value, storeA.value), FILTER(date, storeA.value) )
This gave the orange line which is a better fit over the actual data range.

I appreciate you are unlikely to be using Office 365 insider, so you will need to select the active ranges manually, row by row, or use INDEX:INDEX / MATCH or OFFSET/MATCH to do something similar by formula. For example, the active values for Store A would be

= OFFSET( storeA.value, 0, MATCH(0, storeA.value), 1, COLUMNS(date) - MATCH(0, storeA.value) )
with dates
= OFFSET( date, 0, MATCH(0, storeA.value), 1, COLUMNS(date) - MATCH(0, storeA.value) )

I have appended a file to show the OFFSET ranges.