Hi Community,
Looking for some help please, I have the booking for 2021, I will be adding to this so I want my workflow to use last 12 Mths Data I want to use these to create a future FC for next 12 mths, with a growth factor 1.2 rounded (Which I think I've managed so far 😀
Now I'd like to see the average value in the 12 month future forecast, the Formula should exclude any months in the 12 Months future Data for the item with zero "Forecast" so as not to deflate the average), then using this average I'd like to replace any value in the future forecast which exceeds the average with the average.
So in the example below is the rounded average = 7, I'd like to replace the 3 values above 7 with 7.
| Item Number | New_1 | New_2 | New_3 | New_4 | New_5 | New_6 | New_7 | New_8 | New_9 | New_10 | New_11 | New_12 | Average |
| 47703046001 | 23 | 2 | 1 | 6 | 6 | 8 | 5 | 5 | 1 | 13 | 6 | 2 | 7 |
Can some please explain to me how to do this.
TIA,
Karl
