msquared99
Member
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?
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?