Trend Function to ignore blanks

[bmacias]

In cells K2:K9 I have values but sometimes there are blanks . For example in this particular case only have values on cells K4:K6 (K4=925, K5=755, K6=900).

The formula {=TREND(K4:K6)} works fine and returns 872.5 for the first value on L4...BUT I don't know which cells from K2:K9 will be populated.

I already tried {=TREND(IF(K2:K9<>" ",K2:K9))} but it returns 432.083 on L4 which means that its treating the blank cells as zeros.

Any ideas on how I can rewrite the CSE formula to not take into account the blank cells?

Thanks in advance,

Ben

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=TREND(INDEX(K2:K9,MATCH(TRUE,ISNUMBER(K2:K9),0)):INDEX(K2:K9,MATCH(9.99999999999999E+307,K2:K9)))

 

[bmacias]

Thank you sir!

 

[Aladin Akyurek]

Thank you sir!

You are welcome.

 

[Malina101]

Hello
i have attempted to use the formula to the forecast function but it's not returning the correct information. would you be able to advise. Row labels are months, I would like to use the data under column labels to forecast downward trend in next 6 months ignoring the zero/blanks. Traditional Trend/Forecast works but it takes into account the zeros as previous poster suggested.

much appreciated.

Control+shift+enter, not just enter:

=TREND(INDEX(K2:K9,MATCH(TRUE,ISNUMBER(K2:K9),0)):INDEX(K2:K9,MATCH(9.99999999999999E+307,K2:K9)))