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 the trend as it distorts the figures. How do I incorporate the >0 so that the trend is calculated correctly. I don't want to change the range either.
In both the TREND & the FORECAST.ETS I don't want to include figures in the range that are 0 or less.
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.
4 Replies
- 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.
