Forum Discussion
Solved
TREND & FORECAST.ETS formula only having >0
Hi, Trying to use the TREND function & the FORECAST ETS formula separately so that I have 2 different forecasts. In the range data I have zeros but I do not want to include these figures in t...
- For the forecast.ets formula you can use this variation:
=FORECAST.ETS(F10,IF($B$7:$B$30=0,"",$B$7:$B$30),IF($A$7:$A$30=0,"",$A$7:$A$30),,,1)
However, it looks like the trend function does not like this trick, so to use TREND you'll have to exclude the empty cells form the formula.
Do you have a sample workbook we might look at and work with? Please make sure no sensitive data is included with your sample.
JKPieterse thanks for the quick response.
Hope the workbook makes it easier to understand how I want it to work.
Thanks,
- For the forecast.ets formula you can use this variation:
=FORECAST.ETS(F10,IF($B$7:$B$30=0,"",$B$7:$B$30),IF($A$7:$A$30=0,"",$A$7:$A$30),,,1)
However, it looks like the trend function does not like this trick, so to use TREND you'll have to exclude the empty cells form the formula.thanks ever so much for this.
Appreciate your help
shame that it won't work for the trend formula though.
