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.
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.
=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.