Hello, I am trying to build a forecast model that removes spikes (lets say, any value >2x the average of the last 12/24/36 months). I don't want the value to count as zero, I basically just want that month of demand to not exist, or for the FCST function to skip over it.
I know how to do it with a AverageIF function, but I don't want the average, I want a forecast.
Any help is appreciated! Thanks!
Last edited by AliGW; 07-14-2023 at 10:56 AM. Reason: Irrelevant part of thread title removed.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Fast answers need clear examples. Please post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.
See top of this page.
Attachment 836428
Here is an example of a list of parts that have 2 spikes (two data points > 2x the average). I want to build a forecast formula that will forecast every month excluding the values that would be >2x the average. Instead of counting them as 0, I'd like the formula to just ignore them.
Look at the last row, row 22. In this case, I'd like the FCST function to forecast on months 1,2,3,4,5,6,8,9,10. Ignoring the spike of 47425 and 29660.
Example.xlsx
Last edited by Lukas12345; 07-14-2023 at 11:11 AM.
Use
=FILTER(A21:L21,A21:L21<2*N21)
and use that as the Values array in whatever forecast formula you want to use. (You didn't say...).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Wow, I never tried the Filter formula. Lets say those 12 months are 06/23 - 08/22. Could you show me what the Forecast.linear formula would look like, forecasting for month 08/23?
Not sure I follow you... as 06/23 - 08/22 is 11 months. Maybe something like this:
=FORECAST.LINEAR(24,FILTER(A2:L2,A2:L2<2*N2),FILTER(SEQUENCE(,12),A2:L2<2*N2))
to forecast month 24, from data derived from months 1-12....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks