I want to be able to estimate when stock will expire based on current stock holding and the average sales across equivalent months in previous years.
So is there a formula that can look at today's date, work out this is month 10 (October), and then work out when the stock (2,241) is likely to expire, based on an average of the sales starting from October 2021 until the month when the average sales matches the current stock number? In this case, the average sales Oct 21-Mar 22 were 2,560, which means the current 2,241 would expire between Feb-Mar 23 if last year's monthly sales pattern repeats.
I've tried a few different rolling average suggestions online but just can't get there myself. Thank you.
NB. I have formulae that calculate both today's date, and which update each month name in the second row based on that.
| 05/10/2022 | | | |
| Title Full | Stock | Oct | Sep | Aug | Jul | Jun | May | Apr | Mar | Feb | Jan | Dec | Nov | Oct |
| | 2,241 | 0 | 344 | 1,179 | 1,852 | 1,289 | 1,529 | 962 | 652 | 952 | 551 | 128 | 119 | 158 |
